關(guān)于MySQL的狀態(tài)變量Aborted_clients & Aborted_connects分別代表的意義，以及哪些情況或因素會(huì)導(dǎo)致這些狀態(tài)變量變化呢？下文通過實(shí)驗(yàn)測(cè)試來驗(yàn)證一下，首先我們來看看狀態(tài)變量的描述：

Aborted Connect

Aborted Connect表示嘗試連接到MySQL服務(wù)器失敗的次數(shù)。這個(gè)狀態(tài)變量可以結(jié)合host_cache表和其錯(cuò)誤日志一起來分析問題。 引起這個(gè)狀態(tài)變量激增的原因如下：?

1. 客戶端沒有權(quán)限但是嘗試訪問MySQL數(shù)據(jù)庫(kù)。
2. 客戶端輸入的密碼有誤。?
3. A connection packet does not contain the right information.
4. 超過連接時(shí)間限制，主要是這個(gè)系統(tǒng)變量connect_timeout控制（mysql默認(rèn)是10s，基本上，除非網(wǎng)絡(luò)環(huán)境極端不好，一般不會(huì)超時(shí)。）

官方解釋如下：?

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表示由于客戶端沒有正確關(guān)閉連接而中止的連接數(shù)。官方解釋如下：

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”

當(dāng)Aborted Clients增大的時(shí)候意味著有客戶端成功建立連接，但是由于某些原因斷開連接或者被終止了，這種情況一般發(fā)生在網(wǎng)絡(luò)不穩(wěn)定的環(huán)境中。主要的可能性有：?

1. 客戶端程序在退出之前未調(diào)用mysql_close（）正確關(guān)閉MySQL連接。?
2. 客戶端休眠的時(shí)間超過了系統(tǒng)變量wait_timeout和interactive_timeout的值，導(dǎo)致連接被MySQL進(jìn)程終止
3. 客戶端程序在數(shù)據(jù)傳輸過程中突然結(jié)束

官方文檔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.??

如上介紹所示，有很多因素引起這些狀態(tài)變量的值變化，那么我們來一個(gè)個(gè)分析、演示一下吧。首先，我們來測(cè)試一下導(dǎo)致Aborted Connect狀態(tài)變量增加的可能因素

1、 客戶端沒有權(quán)限但是嘗試訪問MySQL數(shù)據(jù)庫(kù)。?

其實(shí)這里所說的沒有權(quán)限，個(gè)人理解是：客戶端使用沒有授權(quán)的賬號(hào)訪問數(shù)據(jù)庫(kù) 。打個(gè)比方，你嘗試用賬號(hào)kkk訪問MySQL數(shù)據(jù)庫(kù)，其實(shí)你也不知道數(shù)據(jù)庫(kù)是否存在這個(gè)用戶，實(shí)際上不存在這個(gè)用戶。

實(shí)驗(yàn)對(duì)比測(cè)試前，先將狀態(tài)變量清零。

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      |
+-------------------------------+-----------+

在本機(jī)的SecureCRT的另外一個(gè)窗口，使用不存在的賬號(hào)kkk訪問MySQL后，你會(huì)發(fā)現(xiàn)狀態(tài)變量Aborted_connects變?yōu)?了。

[root@DB-Server ~]# mysql -u kkk -p
Enter password:
ERROR 1045 (28000): Access denied for user 'kkk'@'localhost' (using password: YES)

也有可能，這個(gè)賬號(hào)本身存在，但是只允許特定IP地址才能訪問，實(shí)際環(huán)境中，可能是有人在進(jìn)行嘗試暴力破解�？赡苄苑浅６�。我們來測(cè)試一下限制IP訪問的情況

mysql> grant all on MyDB.* to mydbadmin@'10.20.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql>  show status like 'Abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)

如上所示，創(chuàng)建一個(gè)mydbadmin的行號(hào)，只允許10.20段的IP訪問，然后我們從192.168段的IP訪問MySQL數(shù)據(jù)庫(kù)

# mysql -h 10.20.57.24 -u mydbadmin -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mydbadmin'@'192.168.7.208' (using password: YES)

此時(shí)，狀態(tài)變量Aborted_connects就變?yōu)?了。

2、 客戶端輸入的密碼有誤或者根本就是嘗試各個(gè)密碼。(A client uses an incorrect password)?

如下所示，使用test賬號(hào)訪問MySQL數(shù)據(jù)，但是輸入了一個(gè)錯(cuò)誤密碼

[root@DB-Server ~]# mysql -u test -p
Enter password:
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
[root@DB-Server ~]#

你檢查狀態(tài)變量Aborted_connects就會(huì)發(fā)現(xiàn)狀態(tài)變量Aborted_connects變?yōu)?了。

mysql>  show status like 'Abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 2     |
+------------------+-------+
2 rows in set (0.00 sec)

3： A connection packet does not contain the right information.?

這個(gè)比較容易構(gòu)造，可以對(duì)MySQL的端口進(jìn)行端口測(cè)試（ping 端口），因?yàn)閜sping的包不包含正確的信息（right information），測(cè)試之前，先將狀態(tài)變量清空。

mysql> flush status;
 Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)

在客戶端對(duì)MySQL服務(wù)所在的主機(jī)進(jìn)行端口連通性驗(yàn)證（psping）

如上所示，psping測(cè)試后，Aborted_connects變成了5，如果繼續(xù)進(jìn)行psping測(cè)試，那么這個(gè)狀態(tài)變量就會(huì)繼續(xù)增長(zhǎng)。

另外，如果超過max_connect_error的限制后，某一個(gè)客戶端持續(xù)訪問MySQL，這個(gè)是否會(huì)引起狀態(tài)變量Aborted_connects變化呢，實(shí)驗(yàn)測(cè)試的答案是不會(huì)。有興趣的可以驗(yàn)證一下，很奇怪，網(wǎng)上有不少文章都說如果連接數(shù)滿了，也會(huì)導(dǎo)致Aborted_connects狀態(tài)變量增加，實(shí)際上這個(gè)是不會(huì)引起狀態(tài)變量Aborted_connects變化的。?

4、 超過連接時(shí)間限制，主要是這個(gè)參數(shù)connect_timeout控制（mysql默認(rèn)是10s，基本上，除非網(wǎng)絡(luò)環(huán)境極端不好，一般不會(huì)超時(shí)。）?

首先在一臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器上執(zhí)行下面命令，我們用Linux下的netem與tc命令模擬構(gòu)造出復(fù)雜環(huán)境下的網(wǎng)絡(luò)傳輸延時(shí)案例，延時(shí)11秒。?

# tc qdisc add dev eth0 root netem delay 11000ms

在另外一臺(tái)MySQL服務(wù)器ping這臺(tái)MySLQ服務(wù)器，如下所示，你會(huì)看到網(wǎng)絡(luò)時(shí)延為11秒?

# ping 10.20.57.24
PING 10.20.57.24 (10.20.57.24) 56(84) bytes of data.
64 bytes from 10.20.57.24: icmp_seq=1 ttl=61 time=11001 ms
64 bytes from 10.20.57.24: icmp_seq=2 ttl=61 time=11001 ms
64 bytes from 10.20.57.24: icmp_seq=3 ttl=61 time=11001 ms
64 bytes from 10.20.57.24: icmp_seq=4 ttl=61 time=11001 ms
64 bytes from 10.20.57.24: icmp_seq=5 ttl=61 time=11001 ms

此時(shí)訪問MySQL數(shù)據(jù)庫(kù)，由于網(wǎng)絡(luò)時(shí)延為11秒，超出了系統(tǒng)變量connect_timeout的10秒，就會(huì)出現(xiàn)下面錯(cuò)誤，此時(shí)狀態(tài)變量Aborted_connects的值變化！

# mysql -h 10.20.57.24 -u test -p
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

那么如何區(qū)分狀態(tài)變量Aborted Connect是那個(gè)引起的呢？ 單從狀態(tài)變量本身是無法區(qū)分的，但是可以結(jié)合performance_schema.host_cache來稍微做判別、甄別。

• COUNT_NAMEINFO_PERMANENT_ERRORS IP到主機(jī)名稱DNS解析期間的永久性錯(cuò)誤數(shù)。
• COUNT_AUTHENTICATION_ERRORS 驗(yàn)證失敗導(dǎo)致的錯(cuò)誤數(shù)量
• SUM_CONNECT_ERRORS： 被視為“ 阻塞 ”的連接錯(cuò)誤的數(shù)量 （根據(jù)max_connect_errors系統(tǒng)變量進(jìn)行評(píng)估）。只有協(xié)議握手錯(cuò)誤才會(huì)被計(jì)數(shù)，只有通過驗(yàn)證（HOST_VALIDATED = YES）的主機(jī)才會(huì)被計(jì)數(shù)

1、 客戶端沒有權(quán)限但是嘗試訪問MySQL數(shù)據(jù)庫(kù)。

每次都會(huì)引起COUNT_AUTHENTICATION_ERRORS增1 ，第一次會(huì)引起COUNT_NAMEINFO_PERMANENT_ERRORS也增1

2、 客戶端輸入的密碼有誤

每次都會(huì)引起COUNT_AUTHENTICATION_ERRORS增1 ，第一次會(huì)引起COUNT_NAMEINFO_PERMANENT_ERRORS也增1

其實(shí)對(duì)于與1和2，兩者無法判別，最簡(jiǎn)單有效的將系統(tǒng)變量log_warnings設(shè)置為2，然后分析、查看錯(cuò)誤日志信息：

mysql> set global log_warnings=2;
Query OK, 0 rows affected (0.00 sec)
mysql>

那么此時(shí)1和2都會(huì)記錄到錯(cuò)誤日志里面去，然后你就可以通過分析錯(cuò)誤日志，結(jié)合狀態(tài)變量Aborted Connect來分析， 如下測(cè)試案例所示：

2018-06-20 22:44:16 18026 [Warning] IP address '192.168.xxx.xxx' could not be resolved: Name or service not known
2018-06-20 22:44:16 18026 [Warning] Access denied for user 'kkkk'@'192.168.xxx.xxx' (using password: YES)
2018-06-20 22:45:18 18026 [Warning] Access denied for user 'test'@'192.168.xxx.xxx' (using password: YES)

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

每次引起COUNT_HANDSHAKE_ERRORS增1，

每次引起SUM_CONNECT_ERRORS增1

 
 
 
PsPing v2.10 - PsPing - ping, latency, bandwidth measurement utility
 
Copyright (C) 2012-2016 Mark Russinovich
 
Sysinternals - www.sysinternals.com
 
 
 
TCP connect to 10.20.57.24:3306:
 
5 iterations (warmup 1) ping test:
 
Connecting to 10.20.57.24:3306 (warmup): from 192.168.103.34:55327: 1.93ms
 
Connecting to 10.20.57.24:3306: from 192.168.103.34:55328: 10.08ms
 
Connecting to 10.20.57.24:3306: from 192.168.103.34:55329: 3.35ms
 
Connecting to 10.20.57.24:3306: from 192.168.103.34:55330: 3.71ms
 
Connecting to 10.20.57.24:3306: from 192.168.103.34:55331: 2.32ms
 
 
 
TCP connect statistics for 10.20.57.24:3306:
 
  Sent = 4, Received = 4, Lost = 0 (0% loss),
 
  Minimum = 2.32ms, Maximum = 10.08ms, Average = 4.87ms

4、 超過連接時(shí)間限制

如果是超時(shí)引起，那么就會(huì)出現(xiàn)下面狀況：

• 每次引起SUM_CONNECT_ERRORS增1，
• 每次引起COUNT_HANDSHAKE_ERRORS增1
• 第一次會(huì)引起COUNT_NAMEINFO_PERMANENT_ERRORS增1

注意： 3與4不會(huì)寫入錯(cuò)誤日志，3與4的區(qū)別可以通過COUNT_NAMEINFO_PERMANENT_ERRORS的值來區(qū)別。

下面我們來實(shí)驗(yàn)測(cè)試一下狀態(tài)變量Aborted Clients的變化因素，

1、 客戶端程序在退出之前未調(diào)用mysql_close（）正確關(guān)閉MySQL連接。?

在實(shí)驗(yàn)前，使用flush status清理一下狀態(tài)變量

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)

mysql>

寫一個(gè)簡(jiǎn)單的Python測(cè)試腳本python_mysql.py，如下所示，將關(guān)閉數(shù)據(jù)庫(kù)連接的地方dbcon.close注釋掉，

import mysql.connector

try:
 
 dbcon=mysql.connector.connect(
 host='127.0.0.1',
 user='root' ,
 passwd='xxxxxxx',
 database='information_schema'
 )

 cursor= dbcon.cursor()
 sql_tex='select count(*) from MyDB.test'
 cursor.execute(sql_tex)
 dtlist= cursor.fetchall()
 print dtlist
except mysql.connector.Error as e:

  print('operation the sql fail!{0}'.format(e))
  
finally:

  cursor.close;
 # dbcon.close;

然后執(zhí)行一下腳本，檢查狀態(tài)變量Aborted_clients，然后發(fā)現(xiàn)狀態(tài)變量Aborted_clients的值增1了。

2、 客戶端休眠的時(shí)間超過了系統(tǒng)變量wait_timeout和interactive_timeout的值，導(dǎo)致連接被MySQL進(jìn)程終止

mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

將全局系統(tǒng)變量interactive_timeout 和wait_timeout設(shè)置為4秒

mysql> set global interactive_timeout=4;
Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout=4;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)

然后在客戶端連接到MySQL數(shù)據(jù)庫(kù)，不做任何操作，過來4秒后，你去操作就會(huì)出現(xiàn)錯(cuò)誤“ERROR 2013 (HY000): Lost connection to MySQL server during query”

# mysql -h 10.20.57.24 -u test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select current_user();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

在MySQL服務(wù)器你就會(huì)看到狀態(tài)變量Aborted_clients變?yōu)?了。

mysql> show status like 'Abort%';

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

| Variable_name    | Value |

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

| Aborted_clients  | 1     |

| Aborted_connects | 0     |

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

2 rows in set (0.00 sec

還有其他一些原因(客戶端異常中斷或查詢超出了max_allowed_packet值)由于不方便構(gòu)造，在此略過。另外，其實(shí)我們還可以通過tcpdump抓包工具來追蹤分析。下面舉個(gè)例子（這里?

簡(jiǎn)單介紹一下tcpdump,后續(xù)文章再做展開分析）

在MySQL服務(wù)器使用tcpdump抓包

[root@DB-Server ~]# tcpdump -i eth0 port 3306 -s 1500 -w tcpdump.log

然后在另外一臺(tái)MySQL服務(wù)器，使用不存在的賬號(hào)或錯(cuò)誤的密碼訪問MySQL數(shù)據(jù)庫(kù)

# mysql -h 10.20.57.24 -u kkk -p

Enter password:

ERROR 1045 (28000): Access denied for user 'kkk'@'192.168.7.208' (using password: YES)

# mysql -h 10.20.57.24 -u test -p

Enter password:

ERROR 1045 (28000): Access denied for user 'test'@'192.168.7.208' (using password: YES)

[root@GETLNX28 ~]#

執(zhí)行完命令后，你可以使用CTRL + C結(jié)束抓包分析，然后查看分析。如下截圖所示：

[root@DB-Server ~]# tcpdump -i eth0 port 3306 -s 1500 -w tcpdump.log

tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes

28 packets captured

28 packets received by filter

0 packets dropped by kernel

[root@DB-Server ~]# strings tcpdump.log

參考資料：

• https://dev.mysql.com/doc/refman/8.0/en/communication-errors.html
• http://www.olivierdoucet.info/blog/2012/05/08/customer-case-finding-cause-of-max_user_connections/

標(biāo)簽： dns dns解析 linux Mysql 服務(wù)器 服務(wù)器使用 腳本 權(quán)限 數(shù)據(jù)庫(kù) 網(wǎng)絡(luò)

版權(quán)申明：本站文章部分自網(wǎng)絡(luò)，如有侵權(quán)，請(qǐng)聯(lián)系：west999com@outlook.com
特別注意：本站所有轉(zhuǎn)載文章言論不代表本站觀點(diǎn)！
本站所提供的圖片等素材，版權(quán)歸原作者所有，如需使用，請(qǐng)與原作者聯(lián)系。