MySQL运维实战（2.1） 登录失败次数太多导致主机被锁住的问题处理
作者：俊达
引言
当我们在使用 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 设置成3
mysql> 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 3306
Trying 172.16.20.51...
Connected to 172.16.20.51.
Escape character is '^]'.
J
5.7.32Qm>IHUJd>++[103(Nmysql_native_password
!#08S01Got packets out of orderConnection closed by foreign host.
### 重试3次后，无法登陆数据库
[root@box1 ~]# telnet 172.16.20.51 3306
Trying 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 -pauser
mysql: [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: 0
COUNT_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:40
1 row in set (0.00 sec)
2 问题处理
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)
3 总结
当一个主机连续登录mysql失败的次数超过max_connect_errors后，将无法再次登录数据库。这些错误次数会按照主机的维度记录在performance_schema.host_cache表中。然而，如果在一个主机达到max_connect_errors之前，出现了一次成功的登录，系统会将该主机的错误计数重置为0。为了重置错误次数，我们可以使用mysqladmin flush-hosts命令。
针对运维的需求，我们可以考虑适当调大max_connect_errors的值，以提高系统的灵活性和容错性。通过调整这个参数，我们可以更好地管理数据库的连接请求，确保主机在尝试连接失败后仍有机会成功登录。这样，我们可以更有效地进行运维管理，确保数据库的稳定性和可靠性。
