max_connect_errors

现象：

[root@localhost ~]# mysql -h192.168.118.129 -uroot -proot123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '192.168.118.131' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因：

与参数max_connect_errors有关，5.6.5及以前默认值为 10 ，5.6.6及以后默认值为 100.
参数含义：在没有成功连接的情况下中断来自主机的连续连接请求后，服务器会阻止该主机进一步连接。如果在上一次连接中断后，在少于尝试的次数(如100)内成功建立了来自主机的连接，则主机的错误计数(字段performance_schema.host_cache.SUM_CONNECT_ERRORS)将清零。
但是，一旦主机被阻止，刷新主机缓存是解除阻塞的唯一方法。
要刷新主机缓存，可尝试以下任意一种方法：

1. flush hosts; 用户需要reload权限
2. mysqladmin flush-hosts 用户需要reload权限
3. truncate table performance_schema.host_cache; 用户需要drop 权限
4. set global host_cache_size=200 动态设置此参数也可以清除表host_cache的记录，非官方文档方法
5. 或者等待该记录从host cache中被挤掉

注意：performance_schema.host_cache 只记录非本地的TCP连接，不记录本地回环地址（如：127.0.0.1 or ::1）和使用Unix套接字文件，命名管道或共享内存建立的连接。

模拟连接请求失败场景：

需要启用 host_cache 功能，即需要设置以下参数：
host_cache_size=200 动态参数。设置一个非0值，否则不能启用host cache。 设置为0，即不启用host cache，效果等同 --skip-host-cache (<= 5.6.7 默认值 128; >= 5.6.8 默认值 -1， 根据参数 max_connections 动态变化)
#skip_name_resolve 注释此参数，关闭解析主机名，只能将此参数从my.cnf去掉才能关闭，skip_name_resolve=OFF不生效，有点奇怪。需要重启数据库方可生效

重启数据库后查看参数值：

(test)root@localhost [(none)]> show variables like '%skip_name%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+
(test)root@localhost [(none)]> show variables like '%host_cache%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| host_cache_size | 200   |
+-----------------+-------+
(test)root@localhost [(none)]> set global max_connect_errors=5 ;       ## 将此参数设置较小，方便后续测试
(test)root@localhost [(none)]> show VARIABLES like '%max_connect_errors%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 5     |
+--------------------+-------+

测试：

1. 输入错误密码； 不会在表performance_schema.host_cache中记录错误连接。故此测试不满足错误连接请求场景。
2. 利用 telnet 连接后再终止连接，测试生效。于是通过telnet测试下面两种场景。

场景一：正确连接一次立即清零之前的错误连接次数，不会触发 max_connect_errors，主机不被blocked。
先在主机192.168.118.131上执行4次telnet：
[root@localhost ~]# telnet 192.168.118.129 3306 
Trying 192.168.118.129...
Connected to 192.168.118.129.
Escape character is '^]'.
N
5.7.18-log;oPuK*)U-f
                    rwLG|mysql_native_password

!#08S01Got packets out of orderConnection closed by foreign host.

在数据库192.168.118.129 上查看：
(test)root@localhost [(none)]> select ip,host,HOST_VALIDATED,SUM_CONNECT_ERRORS from performance_schema.host_cache ;
+-----------------+------+----------------+--------------------+
| ip              | host | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-----------------+------+----------------+--------------------+
| 192.168.118.131 | NULL | YES            |                  4 |
| 192.168.118.1   | NULL | YES            |                  0 |
+-----------------+------+----------------+--------------------+

在主机192.168.118.131上正确连接一次192.168.118.129数据库:
[root@localhost ~]# mysql -h192.168.118.129 -uroot -proot123
18:07:15 (none)> select ip,host,HOST_VALIDATED,SUM_CONNECT_ERRORS from performance_schema.host_cache ;
+-----------------+------+----------------+--------------------+
| ip              | host | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-----------------+------+----------------+--------------------+
| 192.168.118.131 | NULL | YES            |                  0 |
| 192.168.118.1   | NULL | YES            |                  0 |
+-----------------+------+----------------+--------------------+
2 rows in set (0.00 sec)

只要一次正确连接，则之前的错误次数立即被清零。

场景二：达到错误连接次数，触发 max_connect_errors，导致host is blocked.
再在主机192.168.118.131上执行5次telnet，然后在数据库192.168.118.129 上查看：
(test)root@localhost [(none)]> select ip,host,HOST_VALIDATED,SUM_CONNECT_ERRORS from performance_schema.host_cache ;
+-----------------+------+----------------+--------------------+
| ip              | host | HOST_VALIDATED | SUM_CONNECT_ERRORS |
+-----------------+------+----------------+--------------------+
| 192.168.118.131 | NULL | YES            |                  5 |
| 192.168.118.1   | NULL | YES            |                  0 |
+-----------------+------+----------------+--------------------+

再到主机192.168.118.131上尝试正确连接一次192.168.118.129数据库:
[root@localhost ~]# mysql -h192.168.118.129 -uroot -proot123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1129 (HY000): Host '192.168.118.131' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

主机被block，执行清除host cache解除block后即可正常连接数据库。