ITPub博客

首页 > 数据库 > MySQL > Mysql 错误日志出现大量[Warning] Aborted connection to db user host的解决思路

Mysql 错误日志出现大量[Warning] Aborted connection to db user host的解决思路

原创 MySQL 作者:lusklusklusk 时间:2021-02-09 16:23:45 0 删除 编辑

官方文档
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配置。
    有故障的以太网、集线器、交换机、电缆等等。只有更换硬件才能正确诊断。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2756996/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    438
  • 访问量
    791969