最近一MySQL服务器，由于一些特殊原因遇到“ERROR 1129 (00000): Host xxx is blocked because of many connection errors. Unblock with mysqladmin flush-hosts”，在问题解决后，在详细了解参数max_connect_errors的过程中，有些不同网络资料的矛盾描述确实让我有点迷惑和混淆（关于这个错误，本质原因是因为同一个IP在短时间内产生太多中断的数据库连接（超过max_connect_errors的最大值）而导致的），下面介绍我的探索问题、分析问题、释疑的一个过程。
首先，我在网上搜索了一些资料，不少资料信誓旦旦的介绍，密码输入错误的尝试次数超过max_connect_errors变量，MySQL就会阻塞这个客户端登录，然后我找到了官方资料关于max_connect_errors的介绍，如下所示，MySQL 5.6/5.7的介绍一致
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it. The default is 100.
如上所示，翻译出来的话，大致如下：如果MySQL服务器连续接收到了来自于同一个主机的请求，而且这些连续的请求全部都没有成功的建立连接就被中断了，当这些连续的请求的累计值大于max_connect_errors的设定值时，MySQL服务器就会阻止这台主机后续的所有请求。相信一开始你看到这些资料，也会被“many successive connection requests from a host are interrupted without a successful connection”给弄懵，其实这个就是因为由于网络异常/错误而导致数据库连接中断。网上搜索到这么一个资料：
There seems to be confusion around that variable. It does not really block hosts for repeated invalid passwords but for aborted connections due to network errors.
好吧，那么我们自己动手实验验证一下，就能弄明白到底哪个是正确的。在MySQL数据库里面创建一个test账号，然后我们将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_error%;+--------------------+-------+| Variable_name | Value |+--------------------+-------+| max_connect_errors | 3 |+--------------------+-------+1 row in set (0.00 sec)然后我们在另外一台测试机器，以错误的密码去连接这个MySQL数据库，如下所示，即使前面输入了三次错误密码，第四次输入是也没有碰到上面错误。那么可以排除这个变量与密码错误输入有关系。
[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 1045 (28000): Access denied for user test@mytestlnx02 (using password: YES)[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 1045 (28000): Access denied for user test@mytestlnx02 (using password: YES)[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 1045 (28000): Access denied for user test@mytestlnx02 (using password: YES)[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 1045 (28000): Access denied for user test@mytestlnx02 (using password: YES)[root@mytestlnx02 tmp]#其实，关于某个IP输入了错误密码，MySQL会在performance_schema数据库下的host_cache表中记录。它会累计记录在COUNT_AUTHENTICATION_ERRORS字段，如下所示：
mysql> use performance_schema;Database changedmysql> select * from host_cache\G;*************************** 1. row *************************** IP: 192.168.27.180 HOST: gettestlnx02 HOST_VALIDATED: YES SUM_CONNECT_ERRORS: 0 COUNT_HOST_BLOCKED_ERRORS: 0 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: 0 COUNT_PROXY_USER_ERRORS: 0 COUNT_PROXY_USER_ACL_ERRORS: 0 COUNT_AUTHENTICATION_ERRORS: 4 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: 2018-01-31 16:28:19 LAST_SEEN: 2018-01-31 16:28:26 FIRST_ERROR_SEEN: 2018-01-31 16:28:19 LAST_ERROR_SEEN: 2018-01-31 16:28:261 row in set (0.00 sec)ERROR: No query specified官方资料介绍，host_cache的字段是统计被视为“阻塞”的连接错误的数量（根据max_connect_errors系统变量进行评估）。 只计算协议握手错误，并且仅用于通过验证的主机（HOST_VALIDATED = YES）。
SUM_CONNECT_ERRORSThe number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES).MySQL客户端与数据库建立连接需要发起三次握手协议，正常情况下，这个时间非常短，但是一旦网络异常，防火墙reset连接、网络超时等因素出现，就会导致这个握手协议无法完成，MySQL有个参数connect_timeout，它是MySQL服务端进程mysqld等待连接建立完成的时间，单位为秒。如果超过connect_timeout时间范围内，仍然无法完成协议握手话，MySQL客户端会收到异常，异常消息类似于: Lost connection to MySQL server at XXX, system error: errno，该变量默认是10秒：
mysql> show variables like connect_timeout;+-----------------+-------+| Variable_name | Value |+-----------------+-------+| connect_timeout | 10 |+-----------------+-------+1 row in set (0.00 sec)mysql>那么我们就构造一个网络超时引起的数据库连接被中断案例吧，我们用Linux下的netem与tc命令模拟构造出复杂环境下的网络传输延时案例，如下设置后，此时从测试服务器去访问MySQL服务器，都会出现延时11秒：
[root@gettestlnx02 ~]# ping 10.20.57.24PING 10.20.57.24 (10.20.57.24) 56(84) bytes of data.64 bytes from 10.20.57.24: icmp_seq=1 ttl=62 time=0.251 ms64 bytes from 10.20.57.24: icmp_seq=2 ttl=62 time=0.330 ms64 bytes from 10.20.57.24: icmp_seq=3 ttl=62 time=0.362 ms64 bytes from 10.20.57.24: icmp_seq=4 ttl=62 time=0.316 ms64 bytes from 10.20.57.24: icmp_seq=5 ttl=62 time=0.281 ms64 bytes from 10.20.57.24: icmp_seq=6 ttl=62 time=0.377 ms^C--- 10.20.57.24 ping statistics ---6 packets transmitted, 6 received, 0% packet loss, time 5716msrtt min/avg/max/mdev = 0.251/0.319/0.377/0.047 ms[root@gettestlnx02 ~]# tc qdisc add dev eth0 root netem delay 11000ms[root@gettestlnx02 ~]# ping 10.20.57.24PING 10.20.57.24 (10.20.57.24) 56(84) bytes of data.64 bytes from 10.20.57.24: icmp_seq=1 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=2 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=3 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=4 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=5 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=6 ttl=62 time=11000 ms64 bytes from 10.20.57.24: icmp_seq=7 ttl=62 time=11000 ms我们在测试服务器gettestlnx02连接MySQL数据库，如下所示（注意，如果你是在通过ssh连接这台服务器的话，此时在gettestlnx02上操作会相当慢。当然你也可以在MySQL服务器模拟网络延时，或者你将connect_timeout和网络延时都设小一点）
[root@gettestlnx02 ~]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 2013 (HY000): Lost connection to MySQL server at reading authorization packet, system error: 0[root@gettestlnx02 ~]#如上所示，由于网络延时超过10秒，导致连接MySQL失败，此时，你在MySQL服务器上查询host_cache表时,那么你就会看到SUM_CONNECT_ERRORS变成1了，COUNT_HANDSHAKE_ERRORS也变成了1.
那么我们反复这样折腾三次，那么你会看到SUM_CONNECT_ERRORS变成3了，COUNT_HANDSHAKE_ERRORS也变成了3了.
mysql> select * from host_cache\G;*************************** 1. row *************************** IP: 192.168.27.180 HOST: gettestlnx02 HOST_VALIDATED: YES SUM_CONNECT_ERRORS: 3 COUNT_HOST_BLOCKED_ERRORS: 1 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: 3 COUNT_PROXY_USER_ERRORS: 0 COUNT_PROXY_USER_ACL_ERRORS: 0 COUNT_AUTHENTICATION_ERRORS: 4 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: 2018-01-31 16:28:19 LAST_SEEN: 2018-01-31 17:02:10 FIRST_ERROR_SEEN: 2018-01-31 16:28:19 LAST_ERROR_SEEN: 2018-01-31 17:02:101 row in set (0.00 sec)ERROR: No query specified然后我们用netem与tc 命令在测试服务器上取消网络延迟模拟，然后去测试连接MySQL数据库，如下测试所示：
[root@gettestlnx02 ~]# tc qdisc del dev eth0 root netem delay 11000ms[root@gettestlnx02 ~]# mysql -h10.20.57.24 -utest -pEnter password: ERROR 1129 (HY000): Host 192.168.27.180 is blocked because of many connection errors; unblock with mysqladmin flush-hosts[root@gettestlnx02 ~]#此时就能构造出“ERROR 1129 (HY000): Host 192.168.27.180 is blocked because of many connection errors; unblock with mysqladmin flush-hosts”错误了。
目录
解决方案
解决ERROR 1129 (00000): Host xxx is blocked because of many connection errors. Unblock with mysqladmin flush-hosts这个错误的方法比较多，不过有些方案都是临时方案。临时方案是指标不治本。关键还是需要解决网络错误（这个往往需要求助网络管理人员或系统管理员）
解决方法：
1、将变量max_connection_errors的值设置为一个更大的值mysql> show variables like max_connect_errors;+--------------------+-------+| Variable_name | Value |+--------------------+-------+| max_connect_errors | 3 |+--------------------+-------+1 row in set (0.01 sec)mysql> set global max_connect_errors=150;Query OK, 0 rows affected (0.00 sec)mysql>这个临时方案只是延迟触发IP被禁止访问的条件而已，而且在复杂情况或高并发的情况下，需要设置一个很大的值，否则很容易就会再次被触发。另外，变量只对当前环境生效，如果重启就会失效，如果需要永久有效，可以在my.cnf配置文件里面配置。
2：使用flush hostsmysql> flush hosts;Query OK, 0 rows affected (0.00 sec)mysql> select * from performance_schema.host_cache;Empty set (0.00 sec)mysql>当然你也可以mysqladmin flush-hosts 命令清理一下hosts cache信息
[root@DB-Server ~]# mysqladmin --port=3306 -uroot -p flush-hostEnter password:那么host cache是什么呢？官方介绍如下：
The MySQL server maintains a host cache in memory that contains information about clients: IP address, host name, and error information. The server uses this cache for nonlocal TCP connections. It does not use the cache for TCP connections established using a loopback interface address (127.0.0.1 or ::1), or for connections established using a Unix socket file, named pipe, or shared memory.
简单来说，就是MySQL服务器在内存中维护一个包含客户端信息的缓存：IP地址，主机名和错误信息等。服务器会将非本地TCP连接信息缓存起来。它不会缓存使用环回接口地址（127.0.0.1或者:: 1）建立的TCP连接，或者使用Unix套接字文件，命名管道或共享内存建立的连接。host cache信息可以通过performance_schema数据库下的host_cache表查询。
3：将变量host_cache_size设置为0其实我想说这是一个最不靠谱的解决方法，只是让MySQL服务器不记录host cache信息。
mysql> show variables like %host_cache_size%;+-----------------+-------+| Variable_name | Value |+-----------------+-------+| host_cache_size | 279 |+-----------------+-------+1 row in set (0.00 sec)mysql> set global host_cache_size=0;Query OK, 0 rows affected (0.00 sec)mysql> select * from performance_schema.host_cache;Empty set (0.00 sec)mysql>知识点延伸
关于参数max_connect_errors，不要误解其功能，这个不能作为防止穷举密码的攻击手段。如果您担心SYN泛滥攻击，max_connect_errors可能会在特定情况下帮助您。MySQL 5.6中的PERFORMANCE_SCHEMA改进提供了有关潜在强力攻击的有意义的信息，但是只有在涉及主机缓存的情况下。关于这个可以参考博文Understanding max_connect_errors[1]
\
参考资料
[1]: