作者：俊达

引言

当我们在使用 MySQL 时，可能遇到过如下类似的错误：

Host 'IP' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

该错误意味着 mysqld 已从给定主机收到许多中断的连接请求。并且数量超过了max_connect_errors系统变量的值。

1 参数max_connect_errors

MySQL有参数max_connect_errors，当一个主机尝试登录MySQL，失败的次数超过了max_connect_errors，则这个主机将无法登录到mysql。

下面是一个简单的测试：

### max_connect_errors 设置成3mysql> show variables like '%max_connect_errors%';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 100   |+--------------------+-------+1 row in set (0.00 sec)mysql> set global max_connect_errors=3;Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%max_connect_errors%';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 3     |+--------------------+-------+### 模拟登陆失败（访问3306端口）[root@box1 ~]# telnet 172.16.20.51 3306Trying 172.16.20.51...Connected to 172.16.20.51.Escape character is '^]'.J5.7.32Qm>IHUJd>++[103(Nmysql_native_password!#08S01Got packets out of orderConnection closed by foreign host.### 重试3次后，无法登陆数据库[root@box1 ~]# telnet 172.16.20.51 3306Trying 172.16.20.51...Connected to 172.16.20.51.Escape character is '^]'.jHost '172.16.20.51' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.#### 提供正确的密码也无法登录MySQL[root@box1 ~]# mysql -uauser -h172.16.20.51 -pausermysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1129 (HY000): Host '172.16.20.51' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'### 执行mysqladmin flush-hosts后，可以登陆[root@box1 ecdsa]# mysqladmin -uroot -h127.0.0.1 -phello flush-hosts

客户端连接错误信息记录在information_schema.host_cache表，mysql服务端会从这个表读取数据，获取主机登陆信息。mysqladmin flush-hosts的作用是清空这个表。

mysql> select * from performance_schema.host_cache\G*************************** 1. row ***************************                                        IP: 172.16.20.51                                      HOST: box1                            HOST_VALIDATED: YES                        SUM_CONNECT_ERRORS: 3                 COUNT_HOST_BLOCKED_ERRORS: 2           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0           COUNT_NAMEINFO_PERMANENT_ERRORS: 0                       COUNT_FORMAT_ERRORS: 0           COUNT_ADDRINFO_TRANSIENT_ERRORS: 0           COUNT_ADDRINFO_PERMANENT_ERRORS: 0                       COUNT_FCRDNS_ERRORS: 0                     COUNT_HOST_ACL_ERRORS: 0               COUNT_NO_AUTH_PLUGIN_ERRORS: 0                  COUNT_AUTH_PLUGIN_ERRORS: 0                    COUNT_HANDSHAKE_ERRORS: 5                   COUNT_PROXY_USER_ERRORS: 0               COUNT_PROXY_USER_ACL_ERRORS: 0               COUNT_AUTHENTICATION_ERRORS: 0                          COUNT_SSL_ERRORS: 0         COUNT_MAX_USER_CONNECTIONS_ERRORS: 0COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0             COUNT_DEFAULT_DATABASE_ERRORS: 0                 COUNT_INIT_CONNECT_ERRORS: 0                        COUNT_LOCAL_ERRORS: 0                      COUNT_UNKNOWN_ERRORS: 0                                FIRST_SEEN: 2021-04-06 09:17:05                                 LAST_SEEN: 2021-04-06 09:18:40                          FIRST_ERROR_SEEN: 2021-04-06 09:17:16                           LAST_ERROR_SEEN: 2021-04-06 09:18:401 row in set (0.00 sec)

1、验证链接

检查网络连接以确保您的主机不存在 TCP/IP 连接问题。

2、刷新缓存

mysqladmin flush-hosts命令清除缓存

[root@box1 ecdsa] mysqladmin -uroot -h127.0.0.1 -phello flush-hosts

3、调整max_connect_error参数

可以运行以下查询

mysql> set global max_connect_errors=10000;Query OK, 0 rows affected (0.00 sec)

当一个主机连续登录mysql失败的次数超过max_connect_errors后，将无法再次登录数据库。这些错误次数会按照主机的维度记录在performance_schema.host_cache表中。然而，如果在一个主机达到max_connect_errors之前，出现了一次成功的登录，系统会将该主机的错误计数重置为0。为了重置错误次数，我们可以使用mysqladmin flush-hosts命令。

针对运维的需求，我们可以考虑适当调大max_connect_errors的值，以提高系统的灵活性和容错性。通过调整这个参数，我们可以更好地管理数据库的连接请求，确保主机在尝试连接失败后仍有机会成功登录。这样，我们可以更有效地进行运维管理，确保数据库的稳定性和可靠性。

更多技术信息请查看云掣官网云掣YunChe - 可观测运维专家 | 大数据运维托管 | 云MSP服务