MySQL：查看客户端连接信息

#MySQL 笔记

查看客户端连接详细信息

select * from information_schema.processlist \G

结果示例：

ID      | 1
USER    | root
HOST    | 127.0.0.1:60626
DB      | <null>
COMMAND | Query
TIME    | 0
STATE   | executing
INFO    | select * from information_schema.processlist

查看当前连接数量

select count(*) from information_schema.processlist

查看ip来源和对应的连接数量

SELECT * FROM (
    select 
        substring_index(host,':',1) as ip , 
        count(*) as connection_count 
    from information_schema.processlist 
    group by ip
) t order by t.connection_count desc

结果示例：

+------------+------------------+
| ip         | connection_count |
+------------+------------------+
| 127.0.0.1  | 1                |
+------------+------------------+

查看各个 database 下的ip来源和对应的连接数量

SELECT * FROM (
    select 
        substring_index(host,':',1) as ip, 
        db, 
        count(*) as connection_count 
    from information_schema.processlist 
    group by ip, db
) t order by t.connection_count desc

结果示例:

+------------+--------+------------------+
| ip         | db     | connection_count |
+------------+--------+------------------+
| 127.0.0.1  | <null> | 1                |
+------------+--------+------------------+