侧边栏壁纸
博主头像
★街角晚灯★博主等级

博观而约取 厚积而薄发

  • 累计撰写 450 篇文章
  • 累计创建 183 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

Mysql命令:查看连接数量

WinJay
2023-05-27 / 0 评论 / 0 点赞 / 177 阅读 / 7754 字 / 正在检测是否收录...
温馨提示:
文章发布较早,内容可能过时,阅读注意甄别。。。。

MySQL 关于连接信息的使用;Mysql命令:查看连接数量

image

查看当前连接数

  • 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)

image-20230527165143501

image-20230508161909862

image-20220530224849885

查看最大连接数

MySQL [(none)]> SHOW VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 5000  |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.007 sec)

image-20230527170009131

image-20220530224912870

查看显示连接状态

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)

image-20230508162330462

image-20220530224942646

查看当前所有连接

show full processlist;

image-20220530225022697

查看各用户连接数

  • 查看当前各用户连接数据库的数量
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)

image-20230527165350529

查看各客户端连接

  • 查看连接到数据库的客户端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)

image-20230527165720603

查看各连接帐号

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)

image-20230527165833982

修改最大连接数

set global max_connections=5000;

# 或者修改MySQL的配置文件my.cnf,新增以下配置:

max_connections=5000			#建议修改配置文件,完成后重启服务后生效。

image-20230527170538818

0
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin

评论区