《Mysql應用通過案例分析MySQL中令人頭疼的Aborted告警》要點：
本文介紹了Mysql應用通過案例分析MySQL中令人頭疼的Aborted告警，希望對您有用。如果有疑問，可以聯系我們。
MYSQL實例本文主要給大家介紹的是關于MySQL中Aborted告警的相關內容,分享出來供大家參考學習,下面來一起看看詳細的介紹：
MYSQL實例實戰
MYSQL實例Part1:寫在最前
MYSQL實例在MySQL的error log中,我們會經常性看到一些各類的Aborted connection錯誤,本文中會針對這類錯誤進行一個初步分析,并了解一個問題產生后的基本排查思路和方法.掌握這種方法是至關重要的,而不是出現問題了,去猜,去試.數據庫出現問題的時候需要DBA在短時間內快速解決問題,因此一個好與壞的DBA,區別也在于此.
MYSQL實例Part2:種類
MYSQL實例
[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)
[Warning] Aborted connection 81 to db:'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication
packets)
[Warning] Aborted connection 109 to db:'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
[Warning] Got an error writing communication packets
MYSQL實例Part3:重點參數分析
MYSQL實例wait_timeout
| Command-Line Format | --wait-timeout=# | ||
| System Variable | Name | wait_timeout | |
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values?(Windows) | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 2147483 | ||
| Permitted Values?(Other) | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 31536000 | ||
MYSQL實例這個參數指的是數據庫系統在關閉它之前,服務器等待非交互式連接上的活動的秒數.
MYSQL實例interactive_timeout
| Command-Line Format | --interactive-timeout=# | ||
| System Variable | Name | interactive_timeout | |
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
MYSQL實例這個參數指的是在關閉交互式連接之前,服務器等待活動的秒數
MYSQL實例Warning:警告這兩個參數建議一起調節,能夠避免一些坑.
MYSQL實例本文的兩個參數值采用的是默認值
MYSQL實例
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
|interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
|wait_timeout | 28800 |
+----------------------------+----------+
10 rows in set (0.01 sec)
MYSQL實例另外在數據庫中,我們重點關注下這兩個參數,看看什么情況下Aborted_clients會提升,什么情況下Aborted_connects 會提升
MYSQL實例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 19 |
|Aborted_connects | 0 |
+------------------+-------+
2 rows inset (0.00 sec)
MYSQL實例Part4:案例1
MYSQL實例這里我故意輸入錯誤的密碼5次,來看下數據庫的error log和Aborted的哪個參數記載了這一問題
MYSQL實例
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
MYSQL實例可以看出,這里的Aborted_connects 記錄了密碼錯誤的這一問題
MYSQL實例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 19 |
|Aborted_connects | 5 |
+------------------+-------+
2 rows inset (0.00 sec)
MYSQL實例error log中,也記載了這類密碼輸錯的信息
MYSQL實例
[Warning] Access denied for user'root'@'127.0.0.1' (using password: YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
MYSQL實例Part5:案例2
MYSQL實例接下來我們看下文章第三節提到的兩個重點參數對數據庫連接的行為影響
MYSQL實例這里我們將這兩個參數均配置為10秒
MYSQL實例
mysql>set global wait_timeout=10;
Query OK,0 rows affected (0.00 sec)
mysql>set global interactive_timeout=10;
Query OK,0 rows affected (0.00 sec)
mysql>show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id: 79 Current database: *** NONE ***
+----+------+-----------------+------+---------+------+-------+------------------+
| Id |User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+-------+------------------+
| 79 |root | 127.0.0.1:42016 | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
MYSQL實例這里三次操作,可以看到clients數上升,這是由于timeout參數控制的,已經連接上數據的連接被殺掉.
MYSQL實例
mysql>show global status like 'aborted%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id: 81 Current database: *** NONE ***
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 22 |
|Aborted_connects | 5 |
+------------------+-------+
2 rows in set (0.01 sec)
MYSQL實例error log中記載的是
MYSQL實例
[Warning] Aborted connection 81 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
[Warning] Aborted connection 78 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
[Warning] Aborted connection 79 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
MYSQL實例Part6:案例3
MYSQL實例在這個案例中我們看下最大連接數對數據庫連接的行為影響
MYSQL實例
mysql>show global variables like 'max_conn%';
+--------------------+-------+
|Variable_name | Value |
+--------------------+-------+
|max_connect_errors | 1000 |
|max_connections | 1024 |
+--------------------+-------+
2 rows in set (0.00 sec)
mysql>set global max_connections=2;
Query OK,0 rows affected (0.00 sec)
MYSQL實例這里看到爆出了連接數過多的問題
MYSQL實例
[root@HE3~]# mysql -uroot -pMANAGER -h127.0.0.1
ERROR 1040 (HY000): Too many connections
MYSQL實例而錯誤日志沒有任何記錄
MYSQL實例Part7:案例4
MYSQL實例第三方工具navicat select結果沒有出來的時候選擇停止則出現
MYSQL實例clients上漲
MYSQL實例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 28 |
|Aborted_connects | 10 |
+------------------+-------+
2 rows in set (0.00 sec)
MYSQL實例error log日志記錄
MYSQL實例
170626 16:26:56 [Warning] Aborted connection 109 to db: 'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)
MYSQL實例Part8:原因總結
MYSQL實例總結
MYSQL實例通過這4個案例,我們能夠了解到,Aborted_clients、和Aborted_connects的區別,以及什么情況下會爆出什么樣的錯誤日志,文章第二節中的幾個Aborted錯誤是常見的錯誤,這類錯誤出現的時候腦海里要有一個理論知識,知道什么情況下,會出現什么樣的錯誤,以便快速定位問題.由于筆者的水平有限,編寫時間也很倉促,文中難免會出現一些錯誤或者不準確的地方,不妥之處懇請讀者批評指正.
MYSQL實例好了,以上就是這篇文章的全部內容了,希望本文的內容對大家的學習或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對維易PHP的支持.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/1276.html
