MySQL Router是一个轻量级的连接转发代理，用来将应用程序的连接转发到MySQL数据库。Oracle官方建议MySQL Router与应用部署在相同服务器上，当底层MySQL高可用集群（通常是指MGR）的角色或服务器发生变化时，MySQL Router能自动识别变化情况，并进行连接控制，无需应用修改配置，实现应用对数据库的透明访问。

当前使用MySQL Router的用户越来越多，但是关于MySQL Router的问题分析较少。本文总结了常见的MySQL Router连接失败案例，供大家参考。

原因一：连接失败次数（连续的）达到上限

1、问题现象

MySQL客户端或应用代码报错出现关键字：Too many connection errors

[kingly@rhel7]$ mysql -uapp -pxxx -h127.0.0.1 -P23306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Too many connection errors from 127.0.0.1:25084
复制

2、问题原因

应用到MySQL Router的连接失败次数（连续的）达到上限（默认100）。

这里的错误连接不是指密码输入错误，而是指三次握手失败。应用到数据库的连接会先连接MySQL Router，而MySQL Router是一个http服务，所以在连接时，需先进行三次握手。三次握手失败的原因，可能是网络原因，也可能是应用本身连接异常。

这里的连接失败次数是指连续失败次数，如果失败在未达到上限时连接成功了，则错误次数自动清零。

对于连接失败的报错，MySQL Router日志中会提示“closed connection before finishing handshake”，表示三次握手未正常完成；同时，会记录连接失败的统计情况“1 connection errors for 127.0.0.1 (max 100)”，其中 1 是当前已连续错误的次数，max 100 表示最大的错误次数为100。当达到上限时，会提示连接已被阻止（blocking client host 127.0.0.1）。以下是日志示例：

2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:47520 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 1 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:50818 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 2 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:52844 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 3 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:54380 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 4 connection errors for 127.0.0.1 (max 100)
......

2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 95 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:29112 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 96 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:34896 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 97 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:44970 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 98 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:63526 closed connection before finishing handshake
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 99 connection errors for 127.0.0.1 (max 100)
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:24348 closed connection before finishing handshake
2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.1
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:23440 closed connection before finishing handshake
2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.1
2022-02-26 14:50:15 routing INFO [3gd6e9270700] [routing:mycluster_rw] 127.0.0.1:30882 closed connection before finishing handshake
2022-02-26 14:50:15 routing WARNING [3gd6e9270700] [routing:mycluster_rw] blocking client host 127.0.0.1
复制

3、解决方法

• 方法一：重启MySQL Router

此方法会清零MySQL Router当前的错误连接计数情况，能快速解决当前服务器上应用连不上数据库的问题。

• 方法二：调大最大错误连接次数

查看mysqlrouter.conf配置文件中，是否有设置最大连接数max_connect_errors，如果没有配置，默认是100。

修改配置文件mysqlrouter.conf，在[routing:mycluster_rw]模块下添加max_connect_errors=XXXX，XXXX为需要调整的最大错误连接数。

[kingly@rhel7]$vi /home/mw/mysqlrouter/mycluster_23306/mysqlrouter.conf
[routing:mycluster_rw]
max_connect_errors=10000
bind_address=0.0.0.0
bind_port=23306
destinations=metadata-cache://mycluster/?role=PRIMARY
routing_strategy=first-available
protocol=classic
复制

重启MySQL Router。

此种方法适合网络稳定性差、或忽略这种连接异常多的场景，避免短时连接失败影响新连接到数据库。

4、验证结果

测试连接情况，通过MySQL客户端尝试连接数据库，看能否正常连接数据库。

[kingly@rhel7]$ mysql -uapp -pxxx -h127.0.0.1 -P23306
复制