幾天前收到某個業務項目,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,至此問題徹底解決了,也沒有改變參數的全局值影響會話。