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

Aborted Connect

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

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

官方解釋如下：?

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增大的時候意味著有客戶端成功建立連接，但是由于某些原因斷開連接或者被終止了，這種情況一般發(fā)生在網絡不穩(wěn)定的環(huán)境中。主要的可能性有：?

1. 客戶端程序在退出之前未調用mysql_close（）正確關閉MySQL連接。?
2. 客戶端休眠的時間超過了系統(tǒng)變量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.??

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

1、 客戶端沒有權限但是嘗試訪問MySQL數據庫。?

其實這里所說的沒有權限，個人理解是：客戶端使用沒有授權的賬號訪問數據庫 。打個比方，你嘗試用賬號kkk訪問MySQL數據庫，其實你也不知道數據庫是否存在這個用戶，實際上不存在這個用戶。

實驗對比測試前，先將狀態(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      |
+-------------------------------+-----------+

在本機的SecureCRT的另外一個窗口，使用不存在的賬號kkk訪問MySQL后，你會發(fā)現狀態(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)

也有可能，這個賬號本身存在，但是只允許特定IP地址才能訪問，實際環(huán)境中，可能是有人在進行嘗試暴力破解�？赡苄苑浅６唷Ｎ覀儊頊y試一下限制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)建一個mydbadmin的行號，只允許10.20段的IP訪問，然后我們從192.168段的IP訪問MySQL數據庫

# 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)

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

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

如下所示，使用test賬號訪問MySQL數據，但是輸入了一個錯誤密碼

[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就會發(fā)現狀態(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.?

這個比較容易構造，可以對MySQL的端口進行端口測試（ping 端口），因為psping的包不包含正確的信息（right information），測試之前，先將狀態(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服務所在的主機進行端口連通性驗證（psping）

如上所示，psping測試后，Aborted_connects變成了5，如果繼續(xù)進行psping測試，那么這個狀態(tài)變量就會繼續(xù)增長。

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

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

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

# tc qdisc add dev eth0 root netem delay 11000ms

在另外一臺MySQL服務器ping這臺MySLQ服務器，如下所示，你會看到網絡時延為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

此時訪問MySQL數據庫，由于網絡時延為11秒，超出了系統(tǒng)變量connect_timeout的10秒，就會出現下面錯誤，此時狀態(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是那個引起的呢？ 單從狀態(tài)變量本身是無法區(qū)分的，但是可以結合performance_schema.host_cache來稍微做判別、甄別。

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

1、 客戶端沒有權限但是嘗試訪問MySQL數據庫。

每次都會引起COUNT_AUTHENTICATION_ERRORS增1 ，第一次會引起COUNT_NAMEINFO_PERMANENT_ERRORS也增1

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

每次都會引起COUNT_AUTHENTICATION_ERRORS增1 ，第一次會引起COUNT_NAMEINFO_PERMANENT_ERRORS也增1

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

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

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

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、 超過連接時間限制

如果是超時引起，那么就會出現下面狀況：

• 每次引起SUM_CONNECT_ERRORS增1，
• 每次引起COUNT_HANDSHAKE_ERRORS增1
• 第一次會引起COUNT_NAMEINFO_PERMANENT_ERRORS增1

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

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

1、 客戶端程序在退出之前未調用mysql_close（）正確關閉MySQL連接。?

在實驗前，使用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>

寫一個簡單的Python測試腳本python_mysql.py，如下所示，將關閉數據庫連接的地方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ā)現狀態(tài)變量Aborted_clients的值增1了。

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

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設置為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數據庫，不做任何操作，過來4秒后，你去操作就會出現錯誤“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服務器你就會看到狀態(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值)由于不方便構造，在此略過。另外，其實我們還可以通過tcpdump抓包工具來追蹤分析。下面舉個例子（這里?

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

在MySQL服務器使用tcpdump抓包

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

然后在另外一臺MySQL服務器，使用不存在的賬號或錯誤的密碼訪問MySQL數據庫

# 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結束抓包分析，然后查看分析。如下截圖所示：

[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/

標簽： dns dns解析 linux Mysql 服務器 服務器使用 腳本 權限 數據庫 網絡

版權申明：本站文章部分自網絡，如有侵權，請聯系：west999com@outlook.com
特別注意：本站所有轉載文章言論不代表本站觀點！
本站所提供的圖片等素材，版權歸原作者所有，如需使用，請與原作者聯系。