MySQL令人頭疼的Aborted告警案例分析

實戰

Part1:寫在最前

在MySQL的error log中，我們會經常性看到一些各類的Aborted connection錯誤，本文中會針對這類錯誤進行一個初步分析，并了解一個問題産生後的基本排查思路和方法。掌握這種方法是至關重要的，而不是出現問題了，去猜，去試。資料庫出現問題的時候需要DBA在短時間内快速解決問題，是以一個好與壞的DBA，差別也在于此。

Part2:種類

<code>[Warning] Aborted connection 305628 to db: </code><code>'db'</code> <code>user: </code><code>'dbuser'</code> <code>host: </code><code>'hostname'</code> <code>(Got an error reading communication packets)</code>

<code>[Warning] Aborted connection 81 to db:</code><code>'unconnected'</code> <code>user: </code><code>'root'</code> <code>host: </code><code>'127.0.0.1'</code> <code>(Got timeout reading communication</code>

<code>packets)</code>

<code>[Warning] Aborted connection 109 to db:</code><code>'helei1'</code> <code>user: </code><code>'sys_admin'</code> <code>host: </code><code>'192.168.1.1'</code> <code>(Got an error writing communication packets)</code>

<code>[Warning] Access denied </code><code>for</code> <code>user </code><code>'root'</code><code>@</code><code>'127.0.0.1'</code> <code>(using password: YES)</code>

<code>[Warning] Got an error writing communication packets</code>

Part3:重點參數分析

<code>wait_timeout</code>

Command-Line Format

<code>--wait-timeout=#</code>

System Variable

Name

Variable Scope

Global, Session

Dynamic Variable

Yes

Permitted Values (Windows)

Type

integer

Default

Min Value

Max Value

Permitted Values (Other)

這個參數指的是資料庫系統在關閉它之前，伺服器等待非互動式連接配接上的活動的秒數。

<code>interactive_timeout</code>

<code>--interactive-timeout=#</code>

Permitted Values

這個參數指的是在關閉互動式連接配接之前，伺服器等待活動的秒數

Warning:警告這兩個參數建議一起調節，能夠避免一些坑。

本文的兩個參數值采用的是預設值

<code>mysql> show global variables like </code><code>'%timeout%'</code><code>;</code>

<code>| Variable_name | Value |</code>

<code>| connect_timeout | 10 |</code>

<code>| delayed_insert_timeout | 300 |</code>

<code>| innodb_lock_wait_timeout | 50 |</code>

<code>| innodb_rollback_on_timeout | OFF |</code>

<code>|interactive_timeout | 28800 |</code>

<code>| lock_wait_timeout | 31536000 |</code>

<code>| net_read_timeout | 30 |</code>

<code>| net_write_timeout | 60 |</code>

<code>| slave_net_timeout | 3600 |</code>

<code>|wait_timeout | 28800 |</code>

另外在資料庫中，我們重點關注下這兩個參數，看看什麼情況下Aborted_clients會提升，什麼情況下Aborted_connects 會提升

<code>mysql>show global status like </code><code>'aborted%'</code><code>;</code>

<code>|Variable_name | Value |</code>

<code>|Aborted_clients | 19 |</code>

<code>|Aborted_connects | 0 |</code>

<code>2 rows inset (0.00 sec)</code>

Part4:案例1

這裡我故意輸入錯誤的密碼5次，來看下資料庫的error log和Aborted的哪個參數記載了這一問題

<code>[root@HE3~]</code><code># mysql -uroot -pwrongpass -h127.0.0.1</code>

<code>ERROR 1045 (28000): Access denied </code><code>for</code> <code>user </code><code>'root'</code><code>@</code><code>'127.0.0.1'</code> <code>(using password: YES)</code>

可以看出，這裡的Aborted_connects 記錄了密碼錯誤的這一問題

<code>|Aborted_connects | 5 |</code>

error log中，也記載了這類密碼輸錯的資訊

<code>[Warning] Access denied </code><code>for</code> <code>user</code><code>'root'</code><code>@</code><code>'127.0.0.1'</code> <code>(using password: YES)</code>

<code>[Warning] Access denied </code><code>for</code> <code>user </code><code>'root'</code><code>@</code><code>'127.0.0.1'</code> <code>(using password:YES)</code>

Part5:案例2

接下來我們看下文章第三節提到的兩個重點參數對資料庫連接配接的行為影響

這裡我們将這兩個參數均配置為10秒

<code>mysql></code><code>set</code> <code>global wait_timeout=10;</code>

<code>Query OK,0 rows affected (0.00 sec)</code>

<code>mysql></code><code>set</code> <code>global interactive_timeout=10;</code>

<code>mysql>show processlist;</code>

<code>ERROR 2006 (HY000): MySQL server has gone away</code>

<code>No connection. Trying to reconnect... Connection </code><code>id</code><code>: 79 Current database: *** NONE ***</code>

這裡三次操作，可以看到clients數上升，這是由于timeout參數控制的，已經連接配接上資料的連接配接被殺掉。

<code>No connection. Trying to reconnect... Connection </code><code>id</code><code>: 81 Current database: *** NONE ***</code>

<code>|Aborted_clients | 22 |</code>

error log中記載的是

<code>[Warning] Aborted connection 81 to db: </code><code>'unconnected'</code> <code>user: </code><code>'root'</code> <code>host: </code><code>'127.0.0.1'</code> <code>(Got timeout reading communication packets)</code>

<code>[Warning] Aborted connection 78 to db: </code><code>'unconnected'</code> <code>user: </code><code>'root'</code> <code>host: </code><code>'127.0.0.1'</code> <code>(Got timeout reading communication packets) </code>

<code>[Warning] Aborted connection 79 to db: </code><code>'unconnected'</code> <code>user: </code><code>'root'</code> <code>host: </code><code>'127.0.0.1'</code> <code>(Got timeout reading communication packets)</code>

Part6:案例3

在這個案例中我們看下最大連接配接數對資料庫連接配接的行為影響

<code>mysql>show global variables like </code><code>'max_conn%'</code><code>;</code>

<code>|Variable_name | Value |</code>

<code>|max_connect_errors | 1000 |</code>

<code>|max_connections | 1024 |</code>

<code>mysql></code><code>set</code> <code>global max_connections=2;</code>

這裡看到爆出了連接配接數過多的問題

<code>[root@HE3~]</code><code># mysql -uroot -pMANAGER -h127.0.0.1</code>

<code>ERROR 1040 (HY000): Too many connections</code>

而錯誤日志沒有任何記錄

Part7:案例4

第三方工具navicat select結果沒有出來的時候選擇停止則出現

clients上漲

<code>|Aborted_clients | 28 |</code>

<code>|Aborted_connects | 10 |</code>

error log日志記錄

<code>170626 16:26:56 [Warning] Aborted connection 109 to db: </code><code>'helei1'</code> <code>user: </code><code>'sys_admin'</code> <code>host: </code><code>'192.168.1.1'</code> <code>(Got an error writing communication packets)</code>

Part8:原因總結

在MySQL中sleep狀态數百秒的而且經常重複連接配接是應用程式在工作後沒有關閉連接配接的症狀之一，而是依靠資料庫wait_timeout來關閉它們。強烈建議在操作結束時更改應用程式邏輯以正确關閉連接配接；

檢查以確定max_allowed_packet的值足夠高，并且用戶端沒有收到“資料包太大”消息。這種情況他會中止連接配接，而不正确關閉它；

另一種可能性是TIME_WAIT。建議您确認連接配接被妥善管理并且是在應用端正常關閉；

確定事務正确送出（開始和送出），以便一旦應用程式“完成”連接配接，它将處于“clean”的狀态；

您應該確定用戶端應用程式不中止連接配接。例如，如果PHP的選項max_execution_time設定為5秒，增加connect_timeout是沒用的，因為PHP會殺死腳本。其他程式設計語言和環境也有類似的選項；

連接配接延遲的另一個原因是DNS問題。檢查是否啟用了skip-name-resolve，檢查主機根據其IP位址而不是其主機名進行身份驗證；

嘗試增加MySQL的net_read_timeout和net_write_timeout值，看看是否減少了錯誤的數量。

——總結——

通過這4個案例，我們能夠了解到，Aborted_clients、和Aborted_connects的差別，以及什麼情況下會爆出什麼樣的錯誤日志，文章第二節中的幾個Aborted錯誤是常見的錯誤，這類錯誤出現的時候腦海裡要有一個理論知識，知道什麼情況下，會出現什麼樣的錯誤，以便快速定位問題。由于筆者的水準有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準确的地方，不妥之處懇請讀者批評指正。

<b> 本文轉自 dbapower 51CTO部落格，原文連結：http://blog.51cto.com/suifu/1942302</b><b>，如需轉載請自行聯系原作者</b>

MySQL令人頭疼的Aborted告警案例分析

繼續閱讀

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

Mac｜Windows系統本地照片自動上傳到伺服器

MySQL的4種隔離級别？出現問題

配置apache支援PHP（win7）

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Cloud Studio初體驗

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

php 去掉字元串的最後一個字元及截取原字元串1,2,3,4,5,6,

登入plsql 報錯 the account is locked --使用者被鎖

php——水印

SequoiaDB巨杉資料庫C++驅動概述