MySQL连接参数max_connect_errors

来源：cnblogs　　作者：小有志气　　时间：2021/2/18 15:26:50　　对本文有异议

相关参数：

mysql> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

如果一台主机连续请求MySQL，而这些请求由于网络延迟都没有成功建立连接就被中断了，当这些连续的请求累计值大于该参数时，MySQL服务器就会阻止这台主机后续的所有请求。

mysql> show variables like '%connect_timeout%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+
1 row in set (0.00 sec)

客户端与MySQL建立连接需要三次握手，正常情况下时间很短，但是网络波动会导致延迟，就会导致这个握手协议无法完成，如果超过该参数（默认10秒）还没有完成3次握手，则客户端会收到Lost connection to MySQL server at 'XXX', system error: errno错误，并且累计错误连接数。

mysql> desc performance_schema.host_cache;
+--------------------------------------------+------------------+------+-
| Field                                      | Type             | Null |
+--------------------------------------------+------------------+------+-
| IP                                         | varchar(64)      | NO   |
| HOST                                       | varchar(255)     | YES  |
| HOST_VALIDATED                             | enum('YES','NO') | NO   |
| SUM_CONNECT_ERRORS                         | bigint(20)       | NO   |
| COUNT_HOST_BLOCKED_ERRORS                  | bigint(20)       | NO   |
| COUNT_NAMEINFO_TRANSIENT_ERRORS            | bigint(20)       | NO   |
| COUNT_NAMEINFO_PERMANENT_ERRORS            | bigint(20)       | NO   |
| COUNT_FORMAT_ERRORS                        | bigint(20)       | NO   |
| COUNT_ADDRINFO_TRANSIENT_ERRORS            | bigint(20)       | NO   |
| COUNT_ADDRINFO_PERMANENT_ERRORS            | bigint(20)       | NO   |
| COUNT_FCRDNS_ERRORS                        | bigint(20)       | NO   |
| COUNT_HOST_ACL_ERRORS                      | bigint(20)       | NO   |
| COUNT_NO_AUTH_PLUGIN_ERRORS                | bigint(20)       | NO   |
| COUNT_AUTH_PLUGIN_ERRORS                   | bigint(20)       | NO   |
| COUNT_HANDSHAKE_ERRORS                     | bigint(20)       | NO   |
| COUNT_PROXY_USER_ERRORS                    | bigint(20)       | NO   |
| COUNT_PROXY_USER_ACL_ERRORS                | bigint(20)       | NO   |
| COUNT_AUTHENTICATION_ERRORS                | bigint(20)       | NO   |
| COUNT_SSL_ERRORS                           | bigint(20)       | NO   |
| COUNT_MAX_USER_CONNECTIONS_ERRORS          | bigint(20)       | NO   |
| COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS | bigint(20)       | NO   |
| COUNT_DEFAULT_DATABASE_ERRORS              | bigint(20)       | NO   |
| COUNT_INIT_CONNECT_ERRORS                  | bigint(20)       | NO   |
| COUNT_LOCAL_ERRORS                         | bigint(20)       | NO   |
| COUNT_UNKNOWN_ERRORS                       | bigint(20)       | NO   |
| FIRST_SEEN                                 | timestamp        | NO   |
| LAST_SEEN                                  | timestamp        | NO   |
| FIRST_ERROR_SEEN                           | timestamp        | YES  |
| LAST_ERROR_SEEN                            | timestamp        | YES  |
+--------------------------------------------+------------------+------+

每次连接超时失败会在SUM_CONNECT_ERRORS和COUNT_HANDSHAKE_ERRORS字段累计，当累计到max_connect_errors时就会收到is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts错误。

解决：

根本的解决方法还是需要解决网络波动问题；临时解决可以通过调整max_connect_errors参数来解决，但是如果一个高负载的系统再业务高峰期是可能error很快回累计到很大的值。

原文链接：http://www.cnblogs.com/xiaoyouzhiqi/p/14345563.html

友情链接：直通硅谷　点职佳　北美留学生论坛