前言
下面这个报错，相信大多数童鞋都遇见过；那么碰到这个问题，我们应该怎么办呢？在mysql 5.7及之前版本，出现“too many connection”报错，超级用户root也无法登录上去，除了重启实例，没有其他更好的解决办法；不过在mysql 8.0版本中，是对连接管理做了一些优化，下面我们就来看一下。
|
1
|
error 1040 (hy000): too many connections |
连接管理
在mysql 8.0版本中，对连接管理这一块，是先后做了两个比较大的改变：一个是允许额外连接，另一个是专用的管理端口。
额外连接
在mysql 8.0版本中，在当前连接数达到最大连接数时，服务端允许1个额外连接，可以让具有connection_admin权限的用户连接进来，下面简单测试一下。
（1）为了方便测试，先调整最大连接数
|
1
2
|
mysql> set global max_connections=3;query ok, 0 rows affected (0.00 sec) |
（2）多开几个会话，以达到最大连接数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show processlist;+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| id | user | host | db | command | time | state | info |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| 15 | event_scheduler | localhost | null | daemon | 154190 | waiting on empty queue | null || 54 | root | localhost | null | query | 0 | starting | show processlist || 55 | test | 127.0.0.1:59120 | null | sleep | 19 | | null || 56 | test | 127.0.0.1:59136 | null | sleep | 9 | | null |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+4 rows in set (0.00 sec)mysql> show global status like 'threads_connected';+-------------------+-------+| variable_name | value |+-------------------+-------+| threads_connected | 3 |+-------------------+-------+4 rows in set (0.01 sec) |
（3）普通用户test尝试连接，报错too many connections
|
1
2
3
|
$ mysql -utest -p -h127.0.0.1 -p10080enter password: error 1040 (08004): too many connections |
（4）超级用户root尝试连接成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ mysql -uroot -penter password: welcome to the mysql monitor. commands end with ; or \g.your mysql connection id is 60server version: 8.0.20 mysql community server - gplcopyright (c) 2000, 2020, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> |
（5）再次查看当前连接数，为max_connections+1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| id | user | host | db | command | time | state | info |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| 15 | event_scheduler | localhost | null | daemon | 155064 | waiting on empty queue | null || 54 | root | localhost | null | query | 0 | starting | show processlist || 55 | test | 127.0.0.1:59120 | null | sleep | 893 | | null || 56 | test | 127.0.0.1:59136 | null | sleep | 883 | | null || 60 | root | localhost | null | sleep | 141 | | null |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+5 rows in set (0.00 sec)mysql> show global status like 'threads_connected';+-------------------+-------+| variable_name | value |+-------------------+-------+| threads_connected | 4 |+-------------------+-------+4 rows in set (0.00 sec) |
（6）超级用户root再次尝试连接，也报错too many connections
|
1
2
3
|
$ mysql -uroot -penter password: error 1040 (hy000): too many connections |
通过上面测试可知，在mysql 8.0中，允许的连接数为max_connections+1，其中这1个额外连接，只允许具有connection_admin权限的用户使用。通过这1个额外连接，dba可以使用超级用户root连接，进行kill会话等管理操作，以避免直接重启实例，降低成本，提高效率。
管理端口
额外连接，在一定程度上，提供了出现too many connection问题时的临时解决手段，但额外数量只有1个，难免会有一些意外，出现类似"连接被抢用"、“终端异常掉线”等情况。因此，在mysql 8.0.14版本中，又推出了一个非常重要的新特性——管理端口；它允许具有service_connection_admin权限的用户，通过特定的ip和port连接上来，且没有连接数限制。
（1）先介绍下相关参数
|
1
2
3
|
admin_address：监听ip地址admin_port：监听端口create_admin_listener_thread：是否创建一个单独的线程来监听管理连接 |
（2）通过配置上述参数，即可启用管理端口
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show global variables like 'admin%';+---------------+-----------+| variable_name | value |+---------------+-----------+| admin_address | 127.0.0.1 || admin_port | 33062 |+---------------+-----------+2 rows in set (0.00 sec)# netstat -lntp | grep 33062tcp 0 0 127.0.0.1:33062 0.0.0.0:* listen 20042/mysqld |
（3）接下来进行测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show processlist;+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| id | user | host | db | command | time | state | info |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| 15 | event_scheduler | localhost | null | daemon | 168750 | waiting on empty queue | null || 54 | root | localhost | null | query | 0 | starting | show processlist || 55 | test | 127.0.0.1:59120 | null | sleep | 14579 | | null || 56 | test | 127.0.0.1:59136 | null | sleep | 14569 | | null |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+4 rows in set (0.00 sec)mysql> show global status like 'threads_connected';+-------------------+-------+| variable_name | value |+-------------------+-------+| threads_connected | 3 |+-------------------+-------+1 row in set (0.00 sec) |
（4）普通用户test尝试连接，报错too many connections
|
1
2
3
|
$ mysql -utest -p -h127.0.0.1 -p10080enter password: error 1040 (08004): too many connections |
（5）超级用户root尝试通过管理端口连接成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ mysql -uroot -p -h127.0.0.1 -p33062enter password: welcome to the mysql monitor. commands end with ; or \g.your mysql connection id is 62server version: 8.0.20 mysql community server - gplcopyright (c) 2000, 2020, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '\h' for help. type '\c' to clear the current input statement.mysql> |
（6）继续多开几个会话，使用超级用户root，通过管理端口连接成功，不受最大连接数max_connections限制
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
mysql> show processlist;+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| id | user | host | db | command | time | state | info |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+| 15 | event_scheduler | localhost | null | daemon | 169035 | waiting on empty queue | null || 54 | root | localhost | null | query | 0 | starting | show processlist || 55 | test | 127.0.0.1:59120 | null | sleep | 14864 | | null || 56 | test | 127.0.0.1:59136 | null | sleep | 14854 | | null || 62 | root | 127.0.0.1:47660 | null | sleep | 151 | | null || 63 | root | 127.0.0.1:47760 | null | sleep | 52 | | null || 64 | root | 127.0.0.1:47768 | null | sleep | 43 | | null || 65 | root | 127.0.0.1:47780 | null | sleep | 35 | | null || 66 | root | 127.0.0.1:47790 | null | sleep | 24 | | null || 67 | root | 127.0.0.1:47800 | null | sleep | 16 | | null || 68 | root | 127.0.0.1:47808 | null | sleep | 8 | | null |+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+11 rows in set (0.00 sec)mysql> show global status like 'threads_connected';+-------------------+-------+| variable_name | value |+-------------------+-------+| threads_connected | 10 |+-------------------+-------+1 row in set (0.00 sec) |
可以说，有了管理端口这个新功能，dba再也不用担心too many connections的问题。
总结
在mysql 8.0版本中，为了应对too many connections的场景，先后推出了额外连接和管理端口两个新功能，可以让dba方便、快速地解决问题；不过，这始终是一个临时应急手段，最根本的原因还是要排查应用端的配置(并发限流、sql性能、连接池配置等等)，以彻底规避此类问题。
以上就是mysql 8.0新特性 — 管理端口的使用简介的详细内容，更多关于mysql 8.0新特性 — 管理端口的资料请关注服务器之家其它相关文章！
原文链接：https://cloud.tencent.com/developer/inventory/2098/article/1708462
