故障分析 | MySQL Router：服务器后端那么闲，为什么不让访问？

站长

2022年09月15日 22:15 · 阅读数 16

作者：杨际宁
爱可生 DBA 团队成员，主要负责 MySQL 日常维护、故障处理和性能优化。对技术执着，为客户负责。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

一、背景说明

开发反馈连 router 报错：'ERROR 1040 (HY000): Too many connections to MySQL Router'，

而后端 mysqlserver 上的连接却只有几个。经排查发现，当连接时间超过 wait_timeout 设定的值后，8.0.29 版本 router 的 TCP 连接并不会释放，且 8.0.30 版本的 router 依旧存在这个问题，因此采用降级的方式进行处理。

二、问题复现

mysqlserver 版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.01 sec)

router 版本

shell> ./mysqlrouter -V
MySQL Router  Ver 8.0.30 for Linux on x86_64 (MySQL Community - GPL)

router 端口设置为 8030 和设置最大连接数 max_connections = 3 方便复现

shell> egrep 'max|port' mysqlrouter.conf 
max_connections = 3
bind_port = 8030

开启 3 个会话

mysql> show processlist;
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
| Id  | User            | Host                | db   | Command | Time   | State                  | Info             |
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
|   5 | event_scheduler | localhost           | NULL | Daemon  | 427243 | Waiting on empty queue | NULL             |
| 146 | haha            | 10.186.65.132:33324 | NULL | Sleep   |    184 |                        | NULL             |
| 147 | haha            | 10.186.65.132:33326 | NULL | Sleep   |     14 |                        | NULL             |
| 148 | haha            | 10.186.65.132:33328 | NULL | Sleep   |      9 |                        | NULL             |
| 149 | root            | localhost           | NULL | Query   |      0 | init                   | show processlist |
+-----+-----------------+---------------------+------+---------+--------+------------------------+------------------+
5 rows in set (0.00 sec)

所有会话设置 wait_timeout = 10，超时断开连接

MySQL [(none)]> show databases; set wait_timeout=10;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]>

查看会话（客户端已经断开连接，但不做任何操作将占有 router 的连接）

mysql> show processlist;
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id  | User            | Host      | db   | Command | Time   | State                  | Info             |
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
|   5 | event_scheduler | localhost | NULL | Daemon  | 427509 | Waiting on empty queue | NULL             |
| 157 | root            | localhost | NULL | Query   |      0 | init                   | show processlist |
+-----+-----------------+-----------+------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

新建连接报错

shell> mysql -uhaha -p1 -h10.186.65.132 -P8030
ERROR 1040 (HY000): Too many connections to MySQL Router

查看 router 和 mysqlclient 的连接，并未释放

[root@router ~]# netstat  -lntpa |grep 8030
tcp        0      0 0.0.0.0:8030            0.0.0.0:*               LISTEN      15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56308     ESTABLISHED 15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56312     ESTABLISHED 15045/./mysqlrouter 
tcp        0      0 10.186.65.132:8030      10.186.65.137:56310     ESTABLISHED 15045/./mysqlrouter 


[root@mysql ~]# netstat  -lntpa |grep 8030
tcp        0      0 10.186.65.137:56308     10.186.65.132:8030      ESTABLISHED 3992/mysql          
tcp        0      0 10.186.65.137:56310     10.186.65.132:8030      ESTABLISHED 3990/mysql          
tcp        0      0 10.186.65.137:56312     10.186.65.132:8030      ESTABLISHED 3935/mysql