MySQL 状态变量 Aborted_connects 与 Aborted_clients 浅析 - JAVA - 编程开发
设为首页 加入收藏

TOP

MySQL 状态变量 Aborted_connects 与 Aborted_clients 浅析(一)
2018-06-25 08:46:57 】 浏览:85
Tags: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     |
+--
编程开发网
首页 上一页 1 2 3 4 5 下一页 尾页 1/5/5
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇使用 lynis 进行 linux 漏洞扫描 下一篇ERROR 1044 (42000) : Access den..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

最新文章

热门文章

C 语言

C++基础

windows编程基础

linux编程基础

C/C++面试题目