经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
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

 友情链接: NPS