[[toc]]

适用范围

MYSQL 8.0.14+

问题概述

应用连接DB时，出现 “ERROR 1040 (HY000): Too many connections” 错误，此时在低版要么是手动停掉应用释放连接 ，要么是重启DB以释放连接。

问题原因

出现这个报错的原因有两种情况：

• 一种是单个用户的连接数超过“max_user_connections”参数定义值。
• 另一种情况是，所有应用的连接数超过“max_connections”参数定义值。

解决方案

在MySQL8以前的版本，由于应用用户和管理用户共同使用同一个端口服务，没有进行隔离，如果使用不规范时，很容易造成DBA无法用root用户连接数据库，进行故障定位。

到MySQL8的版本，MySQL官方考虑到这个问题，于是就给数据库管理人员独立起了一个管理端口服务，这样应用用户和管理用户访问的端口进行隔离，互不影响。

由于管理端口默认是33062 ，如果在多实例环境则需要手动去更改该参数。在my.cnf配置文件中添加3个参数

admin_address=127.0.0.1
admin_port=33306
create_admin_listener_thread=1 
复制

重启DB即可

测试效果

下面就来测试一下，当报"ERROR 1040 (HY000): Too many connections"错误之后，DBA是否还能使用管理端口连接数据库
1.模拟会话总数已经达到max_connections参数定义阀值

mysql> show variables like '%connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 3     |
| max_user_connections   | 0     |
| mysqlx_max_connections | 100   |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> show global status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 3     |
+-------------------+-------+
1 row in set (0.01 sec)

#模拟连接数过多
[root@s2ahuoracle02 ~]# mysql -u root -proot   -h 127.0.0.1  -P3333 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
复制

2.使用MySQL数据库额外提供的端口，root连接数据库，不占用连接数

[root@s2ahuoracle02 ~]# mysql -u root -proot -h 127.0.0.1  -P33306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 223
Server version: 8.0.30-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  show global status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 4    |
+-------------------+-------+
1 row in set (0.01 sec)

复制

可以看到root用户还可以通过管理端口33306进行登录了，当前的连接数总数也到了4个，超过了max_connections定义的3个。

3.通过SQL统计当前会话连接，以分析出应用连接问题

#通过用户分组统计连接数
mysql>  select USER, count(*) CONNECT_COUNT FROM information_schema.processlist GROUP BY USER  ORDER BY count(*) DESC ;
+-----------------+---------------+
| USER            | CONNECT_COUNT |
+-----------------+---------------+
| root            |             4 |
| event_scheduler |             1 |
+-----------------+---------------+
2 rows in set (0.07 sec)

#查看每个host的当前连接数和总连接数
mysql> SELECT *  FROM performance_schema.hosts;
+-----------+---------------------+-------------------+
| HOST      | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+-----------+---------------------+-------------------+
| NULL      |                  75 |                93 |
| localhost |                   2 |                 8 |
| 127.0.0.1 |                   3 |               400 |
+-----------+---------------------+-------------------+
3 rows in set (0.24 sec)

 
# 按照登录用户+登录服务器查看登录信息
mysql> SELECT 
     USER as login_user,
     LEFT(HOST,POSITION(':' IN HOST)-1) AS login_ip,
     count(1) as login_count
     FROM information_schema.PROCESSLIST P 
     -- WHERE P.USER IN('root') 
     GROUP BY USER,LEFT(HOST,POSITION(':' IN HOST)-1);
+-----------------+-----------+-------------+
| login_user      | login_ip  | login_count |
+-----------------+-----------+-------------+
| root            | 127.0.0.1 |           2 |
| event_scheduler |           |           1 |
| root            |           |           1 |
+-----------------+-----------+-------------+
3 rows in set (0.01 sec)

# 按照登录用户+数据库+登录服务器查看登录信息
SELECT 
DB as database_name,
USER as login_user,
LEFT(HOST,POSITION(':' IN HOST)-1) AS login_ip,
count(1) as login_count
FROM information_schema.PROCESSLIST P 
-- WHERE P.USER IN('root') 
GROUP BY DB,USER,LEFT(HOST,POSITION(':' IN HOST)-1); 

+---------------+-----------------+-----------+-------------+
| database_name | login_user      | login_ip  | login_count |
+---------------+-----------------+-----------+-------------+
| NULL          | root            | 127.0.0.1 |           2 |
| mysql         | root            | 127.0.0.1 |           1 |
| NULL          | event_scheduler |           |           1 |
| NULL          | root            |           |           1 |
+---------------+-----------------+-----------+-------------+
4 rows in set (0.00 sec)
复制

参考文档

https://dev.mysql.com/doc/refman/8.0/en/administrative-connection-interface.html