关于mysql8:故障分析-MySQL-80-解决连接满问题

52次阅读

共计 3407 个字符，预计需要花费 9 分钟才能阅读完成。

作者：杨涛涛

资深数据库专家，专研 MySQL 十余年。善于 MySQL、PostgreSQL、MongoDB 等开源数据库相干的备份复原、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相干技术支持、MySQL 相干课程培训等工作。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

恰好前些日子和一客户探讨 MySQL 连接数满的问题：ERROR 1040 (HY000): Too many connections

从实际意义来讲，连接数满属于老问题，此问题产生的起因可能有以下几种：

1. 利用端不习惯受限制的用户权限，沉迷于应用 ALL 权限用户。

2. DBA 为了省事儿，建设多个 ALL 权限用户，调配给开发、运维等。

3. MySQL 数据库服务端没有应用连接池（相似 MySQL 企业版连接池插件），越来越多的数据库申请沉积导致连贯满。

4. 利用端和 MySQL 端之间也没有部署连接池，用直连 MySQL 的形式解决日常业务，进而数据库申请过多导致连贯满。

5. MySQL 参数 max_connections 设置不合理，与以后数据库申请存在较大偏差，导致连贯不够用报错。

如果依照业务性能细分为不同权限的用户，只保留一个管理员用户，在这个问题裸露时管理员就能够应用预留连贯进入数据库查看具体问题。MySQL 默认给管理员预留一个额定连贯，用于解决连贯满的场景；然而事实场景并非如此，大部分 MySQL 数据库都是所有业务模块共享一个管理员用户或者应用多个命名不同的具备管理员权限的用户。出问题后只能由 DBA 来调大 max_connections 值（在数据库服务器负载可控前提下）。

MySQL 8.0 自带的连贯治理接口（administrative connection interface）能够帮 DBA 辅助解决这类问题。

连贯治理接口限度放开很多。老版本的连贯预留只有一个，而连贯治理则不限度连接数（硬件级别限度）。

具体在 MySQL 8.0 里怎么用呢？全局设置以 admin 结尾的变量：

admin_address：连贯治理接口监听 IP 地址或者域名，只能够设置单个值。

admin_port：连贯治理接口监听端口，默认为 33062，能够本人指定，不要超过 65535 即可。

admin_ssl 结尾、admin_tsl 结尾等都是设置平安连贯相干，默认为空，可选配置。

create_admin_listener_thread：是否为连贯治理接口创立一个独自监听线程，默认不创立。

应用连贯治理接口的前提条件是用户必须有 super 动态权限或者是 service_connection_admin 动静权限。

来看看连贯治理接口具体该如何应用：

确认参数都开启：

localhost:(none)>select @@admin_address,@@admin_port,@@create_admin_listener_thread;
+-----------------+--------------+--------------------------------+
| @@admin_address | @@admin_port | @@create_admin_listener_thread |
+-----------------+--------------+--------------------------------+
| debian-ytt1     |        18027 |                              1 |
+-----------------+--------------+--------------------------------+
1 row in set (0.00 sec)

创立蕴含 service_connection_admin 权限的用户：

localhost:(none)>create user ytt_admin;
Query OK, 0 rows affected (0.02 sec)

localhost:(none)>grant select, insert,update,delete,service_connection_admin on *.* to ytt_admin;
Query OK, 0 rows affected (0.02 sec)

为了突显成果，把 max_connections 改为最小值 1，此时，MySQL 管理员能够申请的最大连接数为 2，普通用户能够申请的最大连接数为 1.

localhost:(none)>select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

新建设的用户 ytt_admin 尽管为普通用户，然而持有 service_connection_admin 权限，也能够享受当“副”管理员待遇，最多能够申请两个连贯，当申请第三次连贯时报错退出。

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[1] 7474
root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[2] 7475
root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)" &
[3] 7477
root@debian-ytt1:~/sandboxes/msb_8_0_25# ERROR 1040 (HY000): Too many connections

[3]+  退出 1                mysql -uytt_admin -P 8025 -h debian-ytt1 -e "select sleep(3600)"

应用连贯治理接口来连贯：

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -h debian-ytt1 -uytt_admin -P 18027 -e "select sleep(3600)" &
[3] 8516

查看下以后曾经连贯到 MySQL 服务上的连贯，仍然应用连贯治理接口：两个失常连贯，两个额定连贯

root@debian-ytt1:~/sandboxes/msb_8_0_25# mysql -h debian-ytt1 -uytt_admin -P 18027 -e "show processlist;" -ss
8    ytt_admin    debian-ytt1:57946    NULL    Query    154    User sleep    select sleep(1000)
9    ytt_admin    debian-ytt1:57948    NULL    Query    148    User sleep    select sleep(3600)
13    ytt_admin    debian-ytt1:60718    NULL    Query    138    User sleep    select sleep(3600)
17    ytt_admin    debian-ytt1:60726    NULL    Query    0    init    show processlist

应用连贯治理接口、以及老版本预留给管理员的额定连接功能有一个独特前提就是：这个连贯必须可能建设胜利。也就是得有建设这个连贯所需的硬件资源，如果资源有余，MySQL 服务器会回绝连贯，比方可能会有这个谬误：ERROR 2003 (HY000): Can’t connect to MySQL server on ‘…’ (110)

尽管 MySQL 8.0 自带连贯治理接口对连贯满问题有一个很好的备选解决方案，但毕竟是在数据库端的一个长期解决方案。要想从根本上解决这个问题，得从申请进入数据库前就先做优化才好，毕竟数据库不是万能的。比方能够应用如下可能的办法来躲避这个问题：