Mysql 錯誤日誌出現大量[Warning] Aborted connection to db user host的解決思路

官方文件
https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html
https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-host-cache-table.html


error日誌出現錯誤資訊

2021-02-08 21:26:58 140482217572096 [Warning] Aborted connection 367111 to db: 'db1' user: 'u1' host: '192.168.13.16' 
(Got an error reading communication packets)
2021-02-08 21:28:57 140482253641472 [Warning] Aborted connection 367774 to db: 'db1' user: 'u1' host: '192.168.13.16' 
(Got an error reading communication packets)

總結：此類問題一般和資料庫本身沒有太大關係，資料庫端要查的地方主要是wait_timeout、interactive_timeout、max_allowed_packet引數



解決問題的思路
1、檢視[Warning] Aborted connection概念，它是指客戶端成功連線，但後來不正確地斷開連線或被終止，此時這個斷開會記錄到錯誤日誌中

If a client is unable even to connect, the server increments the Aborted_connects status variable.
If these kinds of things happen, it might indicate that someone is trying to break into your server! If the general query log is enabled, messages for these types of problems are logged to it.
如果客戶端甚至無法連線，伺服器將增加aborted_connections狀態變數。
如果發生這種情況，可能表明有人試圖入侵您的伺服器!如果啟用了通用查詢日誌，這些型別的問題的訊息將被記錄到該日誌中。

If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log. The cause can be any of the following:
The client program did not call mysql_close() before exiting.
The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
The client program ended abruptly in the middle of a data transfer.
如果客戶端成功連線，但後來不正確地斷開連線或被終止，伺服器將增加Aborted_clients狀態變數，並將一箇中止的連線訊息記錄到錯誤日誌中。原因可能是以下任何一種:
客戶端程式退出之前沒有呼叫mysql_close()。
客戶端休眠的時間超過了wait_timeout或interactive_timeout秒，沒有向伺服器發出任何請求。
客戶端程式在資料傳輸過程中突然終止。



2、檢視Aborted_clients和Aborted_connects數值

MariaDB [(none)]> SHOW GLOBAL STATUS;
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| Aborted_clients               | 22604     |
| Aborted_connects              | 2604      |
| Access_denied_errors          | 13        |

Aborted_clients
The number of connections that were aborted because the client died without closing the connection properly.
由於客戶端未正確關閉連線而死亡的連線數。

Aborted_connects
The number of failed attempts to connect to the MySQL server.
連線MySQL伺服器失敗的次數。

Connection_errors_internal
The number of connections refused due to internal errors in the server, such as failure to start a new thread or an out-of-memory condition.
由於伺服器內部錯誤（例如無法啟動新執行緒或記憶體不足情況）而被拒絕的連線數。

Connection_errors_max_connections
The number of connections refused because the server max_connections limit was reached.
由於達到伺服器max_connections限制而拒絕的連線數。



3、檢視報錯的型別

MariaDB [performance_schema]> select * from performance_schema.host_cache\G;
*************************** 1. row ***************************
                                        IP: 192.168.143.6
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 3
                       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: 2
                    COUNT_HANDSHAKE_ERRORS: 2582
                   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-02-08 07:22:53
                                 LAST_SEEN: 2021-02-08 21:33:47
                          FIRST_ERROR_SEEN: 2021-02-08 07:22:53
                           LAST_ERROR_SEEN: 2021-02-08 21:33:37

4、檢視max_connect_errors,log_warnings引數

MariaDB [performance_schema]> show variables like '%errors%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 40    |
| slave_skip_errors  | 1062  |
+--------------------+-------+

max_connect_errors
After max_connect_errors successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. If a connection from a host 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. To unblock blocked hosts, flush the host cache;
在max_connect_errors連續之後，來自主機的連續連線請求將中斷從而無法成功連線，伺服器將阻止該主機進行進一步的連線。如果在先前的連線被中斷後，在少於max_connect_errors次嘗試的時間內成功建立了來自主機的連線，則該主機的錯誤計數將清零。要取消阻止主機，請清除主機快取；

log_warnings
是否在錯誤日誌中產生額外的警告訊息，5.7.2之後的版本由log_error_verbosity引數替代了log_warnings引數，設定log_warnings=0等價於log_error_verbosity=1(僅有錯誤會記錄到error log中)，設定log_warnings=1等價於log_error_verbosity=2(錯誤，警告會記錄到error log中)，設定log_warnings=2(或更高)等同於log_error_verbosity=3 (錯誤，警告，notes都會記錄到error log中)，如果指定的值更大，伺服器將log_warnings設定為2。

    Whether to produce additional warning messages to the error log. As of MySQL 5.7.2, information items previously governed by log_warnings are governed by log_error_verbosity, which is preferred over, and should be used instead of, the older log_warnings system variable. (The log_warnings system variable and --log-warnings command-line option are deprecated; expect them to be removed in a future release of MySQL.)
    log_warnings is enabled by default (the default is 1 before MySQL 5.7.2, 2 as of 5.7.2). To disable it, set it to 0. If the value is greater than 0, the server logs messages about statements that are unsafe for statement-based logging. If the value is greater than 1, the server logs aborted connections and access-denied errors for new connection attempts.
    Assigning a value to log_warnings assigns a value to log_error_verbosity and vice versa.
        In MySQL 5.7.2 and higher, use of log_warnings is still permitted but maps onto use of log_error_verbosity as follows:
        Setting log_warnings=0 is equivalent to log_error_verbosity=1 (errors only).
        Setting log_warnings=1 is equivalent to log_error_verbosity=2 (errors, warnings).
        Setting log_warnings=2 (or higher) is equivalent to log_error_verbosity=3 (errors, warnings, notes), and the server sets log_warnings to 2 if a larger value is specified.
    是否在錯誤日誌中產生額外的警告訊息。在MySQL 5.7.2版本及以前版本由log_warnings控制，MySQL 5.7.2之後由log_error_verbosity控制
    log_warnings預設是啟用的(MySQL 5.7.2之前的預設值是1,5.7.2的預設值是2)。若要禁用，請將其設定為0。如果該值大於0，伺服器將記錄關於語句的訊息，這些訊息對於基於語句的日誌記錄來說是不安全的。如果該值大於1，伺服器將記錄中斷的連線和拒絕訪問的錯誤。
    為log_warnings賦值會為log_error_verbosity賦值，反之亦然。
    在MySQL 5.7.2及更高版本中，仍然允許使用log_warnings，但會對映到log_error_verbosity，如下所示:
    設定log_warnings=0等價於log_error_verbosity=1(僅有錯誤會記錄到error log中)。
    設定log_warnings=1等價於log_error_verbosity=2(錯誤，警告會記錄到error log中)。
    設定log_warnings=2(或更高)等同於log_error_verbosity=3 (錯誤，警告，notes都會記錄到error log中)，如果指定的值更大，伺服器將log_warnings設定為2。



5、 導致aborted connections連線中斷或aborted clients客戶端中斷的其他原因
    Other reasons for problems with aborted connections or aborted clients:
    The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld.
    Use of Ethernet protocol with Linux, both half and full duplex. Some Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file using FTP between the client and server machines. If a transfer goes in burst-pause-burst-pause mode, you are experiencing a Linux duplex syndrome. Switch the duplex mode for both your network card and hub/switch to either full duplex or to half duplex and test the results to determine the best setting.
    A problem with the thread library that causes interrupts on reads.
    Badly configured TCP/IP.
    Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.

    max_allowed_packet變數值太小，或者查詢需要的記憶體比您為mysqld分配的記憶體多。
    使用乙太網協議與Linux，一半和全雙工。一些Linux乙太網驅動程式有這個bug。您應該透過使用FTP在客戶機和伺服器機器之間傳輸一個巨大的檔案來測試這個bug。如果傳輸進入突發-暫停-突發-暫停模式，您就會遇到Linux雙工綜合徵。將網路卡和集線器/交換機的雙工模式切換為全雙工或半雙工，並測試結果以確定最佳設定。
    導致讀取中斷的執行緒庫問題。
    嚴重的TCP / IP配置。
    有故障的乙太網、集線器、交換機、電纜等等。只有更換硬體才能正確診斷。