MySQL 关于连接信息的使用;Mysql命令:查看连接数量
查看当前连接数
Threads_connected显示的数值就是当前的连接数
MySQL [(none)]> show status like 'Threads%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Threads_cached | 3 |
| Threads_connected | 415 |
| Threads_created | 857 |
| Threads_running | 208 |
+-----------------------+-------+
4 rows in set (0.003 sec)
查看最大连接数
MySQL [(none)]> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 5000 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.007 sec)
查看显示连接状态
mysql> SHOW STATUS LIKE '%connect%';
+-------------------------------------------------------+---------------------+
| Variable_name | Value |
+-------------------------------------------------------+---------------------+
| Aborted_connects | 1191 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 16540 |
| Global_connection_memory | 0 |
| Locked_connects | 0 |
| Max_used_connections | 70 |
| Max_used_connections_time | 2023-05-08 10:10:56 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 139 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 43 |
+-------------------------------------------------------+---------------------+
23 rows in set (0.00 sec)
查看当前所有连接
show full processlist;
查看各用户连接数
查看当前各用户连接数据库的数量
MySQL [(none)]> select USER , count(*) from information_schema.processlist group by USER;
+-------------------+----------+
| USER | count(*) |
+-------------------+----------+
| root | 415 |
| event_scheduler | 1 |
+-------------------+----------+
2 rows in set (0.010 sec)
查看各客户端连接
查看连接到数据库的客户端ip及各连接数
MySQL [(none)]> SELECT substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;
+------------+-----------------------------------------------------------------+----------+
| host_name | state | count(*) |
+------------+-----------------------------------------------------------------+----------+
| 172.17.0.1 | Source has sent all binlog to replica; waiting for more updates | 206 |
| 172.17.0.1 | | 207 |
| 10.17.0.9 | | 1 |
| 10.17.0.28 | executing | 1 |
| localhost | Waiting on empty queue | 1 |
+------------+-----------------------------------------------------------------+----------+
5 rows in set (0.010 sec)
查看各连接帐号
4、查看连接到数据库的账号、客户端ip及各连接数
MySQL [(none)]> SELECT user,substring_index(host, ':',1) AS host_name,state,count(*) FROM information_schema.processlist GROUP BY state,host_name;
+-----------------+------------+-----------------------------------------------------------------+----------+
| user | host_name | state | count(*) |
+-----------------+------------+-----------------------------------------------------------------+----------+
| root | 172.17.0.1 | Source has sent all binlog to replica; waiting for more updates | 206 |
| root | 172.17.0.1 | | 207 |
| root | 10.17.0.9 | | 1 |
| root | 10.17.0.28 | executing | 1 |
| event_scheduler | localhost | Waiting on empty queue | 1 |
+-----------------+------------+-----------------------------------------------------------------+----------+
5 rows in set (0.005 sec)
修改最大连接数
set global max_connections=5000；
# 或者修改MySQL的配置文件my.cnf，新增以下配置：
max_connections=5000 #建议修改配置文件，完成后重启服务后生效。
评论区