MySQL状态变量Aborted_connects与Aborted_clients浅析

 

关于MySQL的状态变量Aborted_clients & Aborted_connects分别代表的意义，以及哪些情况或因素会导致这些状态变量变化呢？下文通过实验测试来验证一下，首先我们来看看状态变量的描述：

 

 

Aborted Connect

 

Aborted Connect表示尝试连接到MySQL服务器失败的次数。这个状态变量可以结合host_cache表和其错误日志一起来分析问题。 引起这个状态变量激增的原因如下：

 

 

    1、 客户端没有权限但是尝试访问MySQL数据库。

 

    2、 客户端输入的密码有误。

 

    3、 A connection packet does not contain the right information.

 

   4、 超过连接时间限制，主要是这个系统变量connect_timeout控制（mysql默认是10s，基本上，除非网络环境极端不好，一般不会超时。）

 

官方解释如下：

 

 

If a client is unable even to connect, the server increments the Aborted_connects status variable. Unsuccessful connection attempts can occur for the following reasons:

 

·         A client attempts to access a database but has no privileges for it.

 

·         A client uses an incorrect password.

 

·         A connection packet does not contain the right information.

 

·         It takes more than connect_timeout seconds to obtain a connect packet. See Section 5.1.7, “Server System Variables”.

 

 

 

 

 

 

Aborted Clients：

 

 

Aborted Clients表示由于客户端没有正确关闭连接而中止的连接数。官方解释如下：

  

   The number of connections that were aborted because the client died without closing the connection properly. See Section B.5.2.10, “Communication Errors and Aborted Connections”

 

 

当Aborted Clients增大的时候意味着有客户端成功建立连接，但是由于某些原因断开连接或者被终止了，这种情况一般发生在网络不稳定的环境中。主要的可能性有：

 

 

 

   1、 客户端程序在退出之前未调用mysql_close（）正确关闭MySQL连接。

  

   2、 客户端休眠的时间超过了系统变量wait_timeout和interactive_timeout的值，导致连接被MySQL进程终止

 

   3、 客户端程序在数据传输过程中突然结束

 

 

官方文档B.5.2.10 Communication Errors and 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. See Section 5.1.7, “Server System Variables”.

 

·         The client program ended abruptly in the middle of a data transfer.

 

 

 

 

 

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. See Section B.5.2.9, “Packet Too Large”.

 

·         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.

 

 

 

如上介绍所示，有很多因素引起这些状态变量的值变化，那么我们来一个个分析、演示一下吧。首先，我们来测试一下导致Aborted Connect状态变量增加的可能因素

 

 

1、 客户端没有权限但是尝试访问MySQL数据库。

 

 

其实这里所说的没有权限，个人理解是：客户端使用没有授权的账号访问数据库 。打个比方，你尝试用账号kkk访问MySQL数据库，其实你也不知道数据库是否存在这个用户，实际上不存在这个用户。

 

实验对比测试前，先将状态变量清零。

 

mysql> flush status;

Query OK, 0 rows affected (0.01 sec)

mysql> show status like 'Abort%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| Aborted_clients  | 0     |

| Aborted_connects | 0     |

+------------------+-------+

2 rows in set (0.01 sec)

mysql> 

mysql> select host,user from mysql.user;

+-------------------------------+-----------+

| host                          | user      |

+-------------------------------+-----------+

| %                             | mydba     |

| %                             | root      |

| %                             | test      |

| 127.0.0.1                     | root      |

| 192.168.%                     | mydbadmin |

| 192.168.103.18,192.168.103,22 | LimitIP   |

| ::1                           | root      |

| db-server.localdomain         | root      |

| localhost                     | backuser  |

| localhost                     | root      |

+-------------------------------+-----------+