mysql根据用户名，ip，连接时间查看连接数，进行数据分析

1.查看数据库所有连接概况

使用show full processlist;

mysql> show full processlist;
+------+-------------+----------------------+------------------+-------------+--------+-----------------------------------------------------------------------+-----------------------+
| Id   | User        | Host                 | db               | Command     | Time   | State                                                                 | Info                  |
+------+-------------+----------------------+------------------+-------------+--------+-----------------------------------------------------------------------+-----------------------+
| 5540 | root        | localhost            | NULL             | Query       |      0 | init                                                                  | show full processlist |
+------+-------------+----------------------+------------------+-------------+--------+-----------------------------------------------------------------------+-----------------------+
1 rows in set (0.00 sec)

2.查看总最大连接数

查看max_connections

mysql> show variables like '%max_connections%'; 
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.00 sec)

3.查看单个用户最大连接数

查看max_user_connections

mysql> show variables like '%max_user_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)
 

4.根据用户查看当前连接数

mysql> select USER , count(*) from information_schema.processlist group by USER;
+-------------+----------+
| USER        | count(*) |
+-------------+----------+
| repl        |        2 |
| root        |        1 |
| testcarchat |        1 |

+-------------+----------+
3 rows in set (0.01 sec)

5.根据ip查看当前连接数

mysql> select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
+----------------+----------+
| ip             | count(*) |
+----------------+----------+
| localhost      |        1 |
+----------------+----------+
1 rows in set (0.00 sec)

6.查看连接时间最长的连接信息

mysql> select host,user,time,state,info from information_schema.processlist order by time desc limit 2;
+---------------------+-----------+--------+------------------------------------------------------------------+------+
| host                | user      | time   | state                                                            | info |
+---------------------+-----------+--------+------------------------------------------------------------------+------+
| 10.176.140.18:57122 | repl      | 527410 | Master has sent all binlog to slave; waiting for binlog to be up | NULL |
| 10.176.140.17:58380 | repl      | 527320 | Master has sent all binlog to slave; waiting for binlog to be up | NULL |
+---------------------+-----------+--------+------------------------------------------------------------------+------+
2 rows in set (0.00 sec)
 

注意，其中time的单位为秒