mysqldump與innobackupex備份過程你知多少（三）

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表），限於篇幅，請到如下為知筆記連結獲取：

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表），限於篇幅，請到如下為知筆記連結獲取：

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”，精彩內容不容錯過，敬請期待！！