欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL根据用户名,ip,连接时间查看连接数

程序员文章站 2024-02-24 11:37:34
...

MySQL根据用户名,ip,连接时间查看连接数

mysql提供了processlist表来记录数据库当前连接情况,可以使用聚合函数来分类查看数据库连接:

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的单位为秒

相关标签: mysql 连接数