MySQL--查看数据库连接信息

=====================================

查看当前连接到数据库的用户和Host

## 查看当前连接到数据库的用户和Host ##

SELECT DISTINCT 
USER,HOST 
FROM `information_schema`.`PROCESSLIST` P 
WHERE P.USER NOT IN('root','repl','system user')   G

=====================================

查看每个host的当前连接数和总连接数

SELECT * 
FROM performance_schema.hosts;

PS1: 系统表performance_schema.hosts在MySQL 5.6.3版本中引入，用来保存MySQL服务器启动后的连接情况。

=====================================

按照登录用户+登录服务器查看登录信息

SELECT 
USER as login_user,
LEFT(HOST,POSITION(':' IN HOST)-1) AS login_ip,
count(1) as login_count
FROM `information_schema`.`PROCESSLIST` P 
WHERE P.USER NOT IN('root','repl','system user') 
GROUP BY USER,LEFT(HOST,POSITION(':' IN HOST)-1)
ORDER BY COUNT(1) DESC;

=====================================

按照登录用户+数据库+登录服务器查看登录信息

SELECT 
DB as database_name,
USER as login_user,
LEFT(HOST,POSITION(':' IN HOST)-1) AS login_ip,
count(1) as login_count
FROM `information_schema`.`PROCESSLIST` P 
WHERE P.USER NOT IN('root','repl','system user') 
GROUP BY DB,USER,LEFT(HOST,POSITION(':' IN HOST)-1)
ORDER BY COUNT(1) DESC;

mysql查看数据库链接

SHOW STATUS LIKE 'Threads%';

1、查看当前数据库的连接情况

    show full processlist;

    show processlist;

2、查看所有用户的总连接数

  show variables like '%max_connections%';

3、查看每一个用户的最大连接数

show variables like '%max_user_connections%';

4、查看当前连接中各个用户的连接数

select USER , count(*) from information_schema.processlist group by USER;

5、查看当前连接中各个IP的连接数

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

6、查看当前连接中连接时间最长的的连接

 select host,user,time,state,info from information_schema.processlist order by time desc limit 10;

7、查询线上Mysql数据库的连接数配置

show variables like '%conn%';