568
備份策略 - 單表備份的隱患與應對方案
作者介紹：
劉鵬鬆，任職雲和恩墨北區交付部，負責山東部分客戶業務交付。
在通過mysqldump進行MySQL數據庫(InnoDB引擎)邏輯備份的時候經常使用的參數組合就是--single-transaction --master-data=2。
今天的案例就是來分析下通過--single-transaction --master-data=2參數組合進行單表備份而引發的性能問題。
某業務係統(數據庫采用的MySQL數據庫)上午10點左右部分業務業務反應係統緩慢，用戶登陸係統出現超時的現象。
登錄數據庫服務器使用 top 來查看操作係統負載。
top發現：CPU:load average 超過10且是mysqld進程占用，確定是MySQL等待。
通過show processlist查看：發現大量的Waiting for global read lock 。（信息已經脫敏處理）
這裏第一感覺是正在進行mysqldump備份數據，然後show processlit查看數據庫的進程信息，果然發現了backup 用戶正在執行 FLUSH TABLES WITH READ LOCK。
通過操作係統 ps –ef|grep mysqldump 查看，發現mysqldump進程。
mysqldump進程是root發起的，仔細查看mysqldump 進程的pid是不同的，且dbname和tablename也是不同的，很顯然這是一個for循環在mysqldump備份單個表。
切換到root用戶，查看定時任務通過crontab –l發現有一個每天上午 4點30分開始執行的mbak.sh腳本，查看腳本是mysqldump備份數據庫，通過分析備份腳本發現這個腳本的邏輯是這樣的：
整庫備份一次使用的是--all-database參數
分別備份每個數據庫為一個備份文件
單表備份一次，即一個表備份成一個文件
部分腳本節選如下：
所有的數據庫備份一個文件的腳本
每個庫一個備份文件的腳本
每個表一個備份文件的腳本
很顯然出問題的時候是在備份單個表，通過mbak.sh腳本的邏輯來看，是先全庫備份，全庫完成再單庫備份，單庫備份完成之後再單表備份。
現在卡在單表備份的FLUSH TABLES WITH READ LOCK，這是一個全庫級別的鎖，單表備份為什麼會鎖整個庫呢？仔細查看上麵的mysqldump備份命令，可以發現每次mysqldump都添加了 --single-transaction --master-data=2，這是問題的關鍵。
下麵通過開啟general log來分析問題。
開啟general_log。
備份test庫下麵的t表，使用--single-transaction --master-data=2參數
查看general log：
發現其執行了
FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK
備份test 庫下麵的 t 表，不使用--single-transaction --master-data=2 參數
mysqldump -uroot –ppassword --default-character-set=utf8 test t >test_t.sql
查看general log：
發現：沒有執行FLUSH /*!40101 LOCAL */ TABLES、FLUSH TABLES WITH READ LOCK。而是執行的LOCK TABLES t READ，對備份的t表進行一個lock。
備份全庫使用--all-databases及--single-transaction --master-data=2 參數
mysqldump -uroot -ppassword --default-character-set=utf8 --single-transaction --master-data=2 --all-databases >all.singel-2.sql
結論：不管是全庫備份還是單表備份使用了--single-transaction --master-data=2 參數會執行FLUSH /*!40101 LOCAL */ TABLES、FLUSH TABLES WITH READ LOCK 來獲取 show master status的一致性。
通過進一步測試發現mysqldump備份的時候隻使用 --single-transaction 不使用--master-data=2參數是不會進行鎖表的。隻使用 --master-data=2參數為了獲取show master status，會執行FLUSH TABLES WITH READ LOCK的全局鎖。
隻使用 --single-transaction 備份單表的general log
結論：隻使用--single-transaction 不執行任何lock table，也就是說可以進行熱備份。
隻使用 --master-data=2參數備份單表的general log
也就是說通過這幾次實驗發現：
隻要添加了--master-data=2參數就會執行 FLUSH TABLES WITH READ LOCK全局鎖，即使你是備份單個表也會鎖整個庫來獲取show master status;從而導致整個庫的一個等待。
在業務低峰期，獲取全局鎖會很快，但是由於整個庫有700多個表，有部分表在上午10點也就是業務高峰期還沒備份完成，由於高峰期有大量的DML操作，從而獲取全局鎖更加困難，Waiting for global read lock 的時間會更長，從而影響了業務的正常運行。
問題解決
找到mbak.sh 腳本的pid號，進行kill操作，數據庫恢複正常。
改善
調整備份策略：
1、取消備份每個單表為一個文件，減少全局鎖（經過生產環境實際測試mysqldump全庫(17G數據)備份一次不到5分鍾）；
2、如果有必要進行單表備份的話，禁用--master-data=2參數，隻使用--single-transaction 參數；
3、采用XtraBackup 物理備份替換mysqldump的邏輯備份，來進行在線熱備數據庫（InnoDB引擎）。
單表備份：
禁用--master-data=2參數
全庫備份：
--master-data=2 和 --single-transaction 組合，實現InnoDB的在線熱備。
來源：數據和雲
原文鏈接
最後更新：2017-08-31 10:33:06