幾天前收到某個業務項目，MySQL資料庫邏輯備份mysqldump備份失敗的郵件，本是在休假，但本著工作認真負責，7*24小時不間斷運維的高尚職業情操，開始了DBA的排錯之路（一開始資料庫的備份都是成功的，巧的是我休假就出問題，懷疑是數據量又有增長） 首先我們瞭解下mysqldump備份，數據流向的 ...
幾天前收到某個業務項目，MySQL資料庫邏輯備份mysqldump備份失敗的郵件，本是在休假，但本著工作認真負責，7*24小時不間斷運維的高尚職業情操，開始了DBA的排錯之路（一開始資料庫的備份都是成功的，巧的是我休假就出問題，懷疑是數據量又有增長）
首先我們瞭解下mysqldump備份，數據流向的一個過程：MySQL Server端從數據文件中檢索出數據，然後分批將數據返回給mysqldump客戶端，然後mysqldump再把數據寫入到NFS上。一般情況下存儲不是SSD或者是普通磁碟，那麼向NFS上寫入數據比Server端檢索完數據發送給mysqldump客戶端要慢得多，這就有可能mysqldump無法及時接收MySQL Server端發送過來的數據，導致Server端檢索出來的數據在記憶體中積壓等待發送。當超過等待的時間net_write_timeout（預設60s）時就連接斷開，同時拋出錯誤。
1、定位問題
登錄到機器上，先查看了備份文件的邏輯，再查看備份的日誌和備份文件大小，確認備份失敗並定位到是備份命令mysqldump行執行一半失敗（根據備份文件較之前的幾天減少了一半且腳本運行日誌來斷定）。凌晨的備份無效，因而手動觸發腳本執行備份，發現了報錯如下：
[root@mysql_query hk_sa]# bash /opt/shells/mysqldump.sh Warning: Using a password on the command line interface can be insecure. mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `rrd_api_log` at row: 2821866
2、排查問題
查看備份失敗的表的行數為4982704，查看手動備份失敗處的行信息是2017-02-05 04:03:18寫入，之前都沒有出現過這個備份失敗的問題。於是開始懷疑是不是最近數據增長太大或者表的欄位太寬的問題（其他資料庫的表更大，有的甚至達到400G也沒有出現過這個問題，表數據量太大的可能性不大，但單行備份失敗，懷疑大欄位的問題）
查看表結構如下：
[root@localhost] | 08:42:21 | [heika0516] > desc rrd_api_log; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | api_command | varchar(30) | NO | | NULL | | | request_info | text | NO | | NULL | | | response_info | text | NO | | NULL | | | create_time | datetime | NO | | NULL | | +---------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
方向定了剩下的就是驗證自己的猜想了，於是我開始查找資料，果然被我百度到了一些有價值的東西，包括MySQL官方的一些說法：https://bugs.mysql.com/bug.php?id=46103。其中大概的意思就是這個問題老版本會出現，可以調整兩個參數net_write_timeout或者max_allowed_packet的值大一些。net_write_timeout的預設值是60s，而max_allowed_packet的預設值是67108864，又是一通查資料後發現，有些前輩調整max_allowed_packet無效，而調整net_write_timeout才有效，於是我也站著巨人的肩膀上搞了一把，果然備份成功了，挺過了休假時被煩惱的問題。
然而新的問題又出來了，很多前輩都講增大net_write_timeout的值，Server端會消耗更多的記憶體甚至導致swap的使用影響性能，但又不確定是不是參數調整所致，存在潛在的風險。不過這種說法我並不同意，因為我執行的過程中發現，MEM的free反而變多了，你沒有聽錯真的變多了。
#mysqldump備份執行前
[root@mysql_query hk_sa]# free -m
total used free shared buffers cached
Mem: 16080 13305 2775 0 121 3729
-/+ buffers/cache: 9454 6626
Swap: 8191 349 7842
#增大了net_write_timeout的值
[root@localhost] | 08:51:53 | [(none)] > set @@global.net_write_timeout=500;
Query OK, 0 rows affected (0.01 sec)
#bash完腳本發現備份OK的
[root@mysql_query hk_sa]# ls -lh /opt/app/mysql/data/heika0516/rrd_api_log.ibd
-rw-r--r-- 1 mysql mysql 4.1G Aug 7 22:03 /opt/app/mysql/data/heika0516/rrd_api_log.ibd
#mysqldump備份執行後
[root@mysql_query hk_sa]# free -m
total used free shared buffers cached
Mem: 16080 12434 3646 0 93 2890
-/+ buffers/cache: 9450 6630
Swap: 8191 349 7842
到此為止，mysqldump備份失敗確實是解決，但是前輩們反映的問題是消耗更多的記憶體，到我這反而釋放了更多的記憶體，這樣修改參數終究是會存在安全隱患，且這個參數會影響所有的會話連接。那就先不管了，過個好假期再搞。不過我始終有個疑問，我這維護的400G的大表，且部分表比這更大，也有大欄位就沒出現過這個問題，怎麼會突然出現呢，因此我懷疑不是表數據過多的問題，還是大欄位的問題。
休假回來後，立馬開始test排錯的工作，先分析確認好切入點，我就把net_write_timeout的值改為預設60，但這樣備份肯定會失敗的，於是想到了max_allowed_packet參數，但全局調整這個參數，對網路發包和各個會話也有影響，繼續對mysqldump這個備份進行調研，居然讓我發現了一個牛逼的可接參數max_allowed_packet，在mysqldump後面加了這個選項，值大小大家可以根據表的大小進行設置，我這裡給的500M，至此問題徹底解決了，也沒有改變參數的全局值影響會話。
