MySQL 連線太多次導致連線失敗

MySQL 有個機制是如果有 IP 連線失敗多次，就會將該 IP block

檢查

檢查方式：

mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.09 sec)

mysql>

以上表示一個 IP 最多可以失敗 100 次

至於有哪些 IP 發生這問題，要去 performance_schema.host_cache （需要特別開權限才能進該 schema）看

mysql> select IP, HOST, SUM_CONNECT_ERRORS, COUNT_HOST_BLOCKED_ERRORS, FIRST_ERROR_SEEN, LAST_ERROR_SEEN from performance_schema.host_cache;
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
| IP             | HOST | SUM_CONNECT_ERRORS | COUNT_HOST_BLOCKED_ERRORS | FIRST_ERROR_SEEN    | LAST_ERROR_SEEN     |
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
| 10.8.8.237     | NULL |                101 |                    577863 | 2023-07-27 15:12:11 | 2023-07-28 03:28:00 |
| 10.200.21.54   | NULL |                100 |                      3276 | 2023-07-28 00:44:19 | 2023-07-28 03:28:00 |
| 10.240.2.19    | NULL |                  0 |                         0 | 2023-07-28 03:26:01 | 2023-07-28 03:26:01 |
| 10.200.26.30   | NULL |                101 |                      5967 | 2023-07-28 00:56:40 | 2023-07-28 03:25:16 |
| 10.200.23.34   | NULL |                  0 |                         0 | 2023-07-28 01:57:29 | 2023-07-28 01:57:29 |
| 10.200.21.62   | NULL |                  0 |                         0 | 2023-07-28 03:08:13 | 2023-07-28 03:08:13 |
| 10.200.206.216 | NULL |                  0 |                         0 | 2023-07-28 02:53:28 | 2023-07-28 02:53:28 |
| 10.200.21.63   | NULL |                  0 |                         0 | 2023-07-28 00:37:29 | 2023-07-28 00:37:29 |
+----------------+------+--------------------+---------------------------+---------------------+---------------------+
8 rows in set (0.08 sec)

mysql>

這樣可以看到 10.8.8.237、10.200.21.54、10.200.26.30這 3 個 IP 都出現這問題。

解決辦法

解決辦法是執行以下 SQL

mysql> flush hosts;

但該指令有限制要最高權限才能執行，沒有權限者會出現以下 error

ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

參考資料

KuBee, (2020, September 10). MySQL-客户端连接被拒绝问题. 博客园. https://www.cnblogs.com/binliubiao/p/13648294.html

yinxuep, (2019, December 11). Mysql连接错误过多的问题处理_mysql sum_connect_errors. CSDN博客. https://blog.csdn.net/yinxuep/article/details/103501044