最近一MySQL伺服器，由於一些特殊因素遇到“ERROR 1129 (00000): Host 'xxx' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'”，在問題解決後，在詳細了... ...
最近一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 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES)
[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES)
[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'mytestlnx02' (using password: YES)
[root@mytestlnx02 tmp]# mysql -h10.20.57.24 -utest -p
Enter 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: gettestlnx02HOST_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: 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: 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:26
1 row in set (0.00 sec)
ERROR:
No query specified
官方資料介紹，host_cache的欄位是統計被視為“阻塞”的連接錯誤的數量（根據max_connect_errors系統變數進行評估）。 只計算協議握手錯誤，並且僅用於通過驗證的主機（HOST_VALIDATED = YES）。
SUM_CONNECT_ERRORS
