mysqldump有什么坑嗎？

想必大家都知道，mysqldump備份時可以使用--single-transaction + --master-data兩個選項執行備份（老實講，為圖方便，本人之前很長一段時間，生產庫也是使用mysqldudmp遠程備份的），這樣備份過程中既可以盡量不鎖表，也可以獲取到binlog pos位置，備份文件可以用于數據恢復，也可以用于搭建備庫。看起來那么美好，然而，其實一不小心你就發現自己已經在坑里了。

1.3.1. 坑一

使用--single-transaction + --master-data時，myisam表持續不斷插入，并用于搭建備庫。

首先在A庫上把myisam表的數據行數弄到100W以上

A庫新開一個ssh會話2，使用如下腳本持續對表t_luoxiaobo2進行插入操作（該表為myisam表），限于篇幅，請到如下為知筆記鏈接獲取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac1Rgvxq1vgkhL21ibWU2cLidk

A庫新開一個ssh會話3，清空查詢日志：

現在，A庫在ssh會話3中，使用mysqldump備份整個實例

備份完成之后，A庫在ssh會話2中，停止持續造數腳本

A庫在ssh會話2中，查看備份文件中的binlog pos

A庫在ssh會話3中，查看查詢日志，可以發現在UNLOCK TABLES之后，select *…t_luoxiaobo2表之前，還有數據插入到該表中：

現在，我們將這個備份文件用于B庫上搭建備庫，并啟動復制，可以發現有如下復制報錯：

從上面的結果中可以看到，主鍵沖突了，也就是說備份的表t_luoxiaobo2中的數據與備份文件中獲取的binlog pos點并不一致，咱們現在在B庫中，查詢一下這個表中大于等于這個沖突主鍵的數據，從下面的結果中可以看到，備份文件中如果嚴格按照一致性要求，備份文件中的數據必須和binlog pos點一致，但是現在，備份文件中的數據卻比獲取的binlog pos點多了5行數據：

現在，咱們去掉--single-transaction選項，重新執行本小節以上步驟，重新搭建從庫，看看是否還有問題（這里限于篇幅，步驟省略，只貼出最后結果）：

從上面的show slave status輸出信息中我們可以看到，去掉了--single-transaction選項之后的備份，用于搭建備庫就正常了。另外，我們重新在A庫上查看查詢日志也可以發現，只搜索到flush語句而沒有搜索到unlock tables、set session transaction.. 、start transaction.. 語句，說明備份過程沒有開啟一致性快照事務，沒有修改隔離級別，是全程加全局讀鎖的，mysqldump備份進程結束退出之后mysql server自動回收鎖資源：

也許你會說，我們數據庫環境很規范，沒有myisam表，不會有這個問題，OK，贊一個。

1.3.2. 坑二

使用--single-transaction + --master-data時，innodb表執行online ddl，備份文件用于搭建備庫（注意，本小節中的數據庫實例與前一小節不同）。

這次我們操作Innodb表，在A庫上先把t_luoxiaobo表的數據也弄到幾百萬行。

A庫在ssh會話2中，使用如下腳本持續對表t_luoxiaobo進行DDL操作（該表為innodb表），限于篇幅，請到如下為知筆記鏈接獲取：

http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac0tjwkE3KHkhU2_9gwt3mTldI

A庫在ssh會話3中，清空查詢日志：

現在，A庫在ssh會話3中，使用mysqldump備份整個實例：

A庫在ssh會話2中，停止DDL添加腳本。

A庫在ssh會話2中，查看備份文件中的binlog pos：

現在，我們將這個備份文件用于在B庫中搭建備庫，并啟動復制，從下面的結果中可以看到，復制狀態正常：

現在我們回到A庫上，對表t_luoxiaobo插入一些測試數據：

在B庫上查詢復制狀態和表t_luoxiaobo中的數據：

到這里，看起來一切正常，對不對？開心嗎？先等等，請保持DBA一貫嚴謹的優良傳統，咱們在主庫上使用pt-table-checksum工具檢查一下：

從上面的信息中可以看到，表luoxiaobo.t_luoxiaobo的檢測DIFFS 列為16，代表主從有數據差異，神馬情況？別急，咱們先來分別在AB庫查詢下這張表的數據行數，從下面的結果可以看到，該表主從數據差異2097152行！！！

發生什么了？也許你會說，平時使用mysqldump不都是這樣的嗎？沒毛病啊。

• 回想一下，從咱們上篇"mysqldump與innobackupex備份過程你知多少（二）"中 提到的"WITH CONSISTENT SNAPSHOT語句的作用" 時的演示過程可以知道，DDL的負載是刻意加上去的，還記得之前演示mysqldump使用savepoint的作用的時候，使用start transaction with consistent snapshot語句顯式開啟一個事務之后，該事務執行select之前，該表被其他會話執行了DDL之后無法查詢數據，我們知道mysqldump備份數據的時候，就是在start transaction with consistent snapshot語句開啟的一個一致性快照事務下使用select語句查詢數據進行備份的。

為了證實這個問題，下面我們打開查詢日志查看一下在start transaction with consistent snapshot語句和select … 之間是否有DDL語句，如下：

現在，我們打開備份文件，找到表t_luoxiaob的備份語句位置，可以看到并沒有生成INSERT語句：

到這里，是不是突然心弦一緊呢？ so……如果你決定繼續使用mysqldump，那么以后搭建好備庫之后，一定要記得校驗一下主備數據一致性！！！

1.3.3. 有辦法改善這這些問題嗎？

在尋找解決辦法之前，咱們先來看看mysqldump的備份選項--single-transaction和--master-data[=value]的作用和使用限制。

• --single-transaction 
  * 此選項將事務隔離模式設置為REPEATABLE READ，并在備份數據之前向server發送START TRANSACTION SQL語句以顯示開啟一個事務快照。僅適用于InnoDB這樣的事務表，由于是在事務快照內進行備份，這樣可以使得備份的數據與獲取事務快照時的數據是一致的，而且不會阻塞任何應用程序對server的訪問。 
  * 在進行單事務備份時，為確保有效的備份文件（正確的表內容和二進制日志位置），不能有其他連接應使用語句：ALTER TABLE，CREATE TABLE，DROP TABLE，RENAME TABLE，TRUNCATE等DDL語句。這會導致一致狀態被破壞，可能導致mysqldump執行SELECT檢索表數據時查詢到不正確的內容或備份失敗

  * 注意：該選項僅適用于事務引擎表，對于MyISAM或MEMORY表由于不支持事務，所以備份過程中這些引擎表的數據仍可能發生更改

• --master-data[=value] 

  * 使用此選項備份時會在備份文件中生成change master to語句，使用的binlog pos是使用的備份server自己的binlog pos，可使用備份文件用于將另一臺服務器（恢復這個備份文件的服務器）設置為備份server的從庫。 
  * 與--dump-slave選項類似，如果選項值為2，則CHANGE MASTER TO語句將作為SQL注釋寫入備份文件，因此僅供參考;當備份文件被重新加載時，這個注釋不起作用。如果選項值為1，則該語句不會注釋，并在重新加載備份文件時會生效（被執行）。如果未指定選項值，則默認值為1。
  * 指定此選項的用戶需要RELOAD權限，并且server必須啟用二進制日志，因為這個位置是使用show master status獲取的（如果沒有開啟log_bin參數，則show master status輸出信息為空），而不是使用show slave status獲取的。 
  * --master-data選項自動關閉 --lock-tables選項。同時還會打開--lock-all-tables，除非指定了--single-transaction選項，在指定了--single-transaction選項之后，只有在備份開始時間內才加全局讀取鎖。

so……--single-transaction選項中明確說明了如果使用了該選項，那么在備份期間如果發生DDL，則可能導致備份數據一致性被破壞，select檢索不到正確的內容。另外，該選項僅僅只適用于事務引擎表，不適用于非事務引擎。作為DBA，很多時候是非常無奈的，雖然有各種規范，但是保不齊就是有漏網之魚，這個時候，生活還得繼續，工作還得做好， 那么，有什么辦法可以緩解這個問題嗎？有的：

• 就如同上文中演示步驟中那樣，去掉--single-transaction選項進行備份，此時單獨使用--master-data選項時會自動開啟--lock-all-tables，備份過程中整個實例全程鎖表，不會發生備份數據與獲取的binlog pos點不一致的問題，這樣，用該備份來搭建備庫時就不會出現數據沖突。但是問題顯而易見，備份期間數據庫不可用，如果采用這種方法，至少需要在業務低峰期進行備份。

• 使用innobackupex備份工具。

下一篇"mysqldump與innobackupex備份過程你知多少（四）"我們將接著介紹"innobackupex”，精彩內容不容錯過，敬請期待！！