应用通过MySQL Router连接MGR数据库失败原因-1

[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。

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

vlambda博客
学习文章列表