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

mysqldump有什么坑嗎？

想必大家都知道，mysqldump備份時可以使用--single-transaction + --master-data兩個選項(xiàng)執(zhí)行備份（老實(shí)講，為圖方便，本人之前很長一段時間，生產(chǎn)庫也是使用mysqldudmp遠(yuǎn)程備份的），這樣備份過程中既可以盡量不鎖表，也可以獲取到binlog pos位置，備份文件可以用于數(shù)據(jù)恢復(fù)，也可以用于搭建備庫?？雌饋砟敲疵篮茫欢?，其實(shí)一不小心你就發(fā)現(xiàn)自己已經(jīng)在坑里了。

1.3.1. 坑一

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

首先在A庫上把myisam表的數(shù)據(jù)行數(shù)弄到100W以上

A庫新開一個ssh會話2，使用如下腳本持續(xù)對表t_luoxiaobo2進(jìn)行插入操作（該表為myisam表），限于篇幅，請到如下為知筆記鏈接獲?。?/span>

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

A庫新開一個ssh會話3，清空查詢?nèi)罩荆?/span>

現(xiàn)在，A庫在ssh會話3中，使用mysqldump備份整個實(shí)例

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

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

A庫在ssh會話3中，查看查詢?nèi)罩荆梢园l(fā)現(xiàn)在UNLOCK TABLES之后，select *…t_luoxiaobo2表之前，還有數(shù)據(jù)插入到該表中：

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

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

現(xiàn)在，咱們?nèi)サ?-single-transaction選項(xiàng)，重新執(zhí)行本小節(jié)以上步驟，重新搭建從庫，看看是否還有問題（這里限于篇幅，步驟省略，只貼出最后結(jié)果）：

從上面的show slave status輸出信息中我們可以看到，去掉了--single-transaction選項(xiàng)之后的備份，用于搭建備庫就正常了。另外，我們重新在A庫上查看查詢?nèi)罩疽部梢园l(fā)現(xiàn)，只搜索到flush語句而沒有搜索到unlock tables、set session transaction.. 、start transaction.. 語句，說明備份過程沒有開啟一致性快照事務(wù)，沒有修改隔離級別，是全程加全局讀鎖的，mysqldump備份進(jìn)程結(jié)束退出之后mysql server自動回收鎖資源：

也許你會說，我們數(shù)據(jù)庫環(huán)境很規(guī)范，沒有myisam表，不會有這個問題，OK，贊一個。

1.3.2. 坑二

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

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

A庫在ssh會話2中，使用如下腳本持續(xù)對表t_luoxiaobo進(jìn)行DDL操作（該表為innodb表），限于篇幅，請到如下為知筆記鏈接獲?。?/span>

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

A庫在ssh會話3中，清空查詢?nèi)罩荆?/span>

現(xiàn)在，A庫在ssh會話3中，使用mysqldump備份整個實(shí)例：

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

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

現(xiàn)在，我們將這個備份文件用于在B庫中搭建備庫，并啟動復(fù)制，從下面的結(jié)果中可以看到，復(fù)制狀態(tài)正常：

現(xiàn)在我們回到A庫上，對表t_luoxiaobo插入一些測試數(shù)據(jù)：

在B庫上查詢復(fù)制狀態(tài)和表t_luoxiaobo中的數(shù)據(jù)：

到這里，看起來一切正常，對不對？開心嗎？先等等，請保持DBA一貫嚴(yán)謹(jǐn)?shù)膬?yōu)良傳統(tǒng)，咱們在主庫上使用pt-table-checksum工具檢查一下：

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

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

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

為了證實(shí)這個問題，下面我們打開查詢?nèi)罩静榭匆幌略趕tart transaction with consistent snapshot語句和select … 之間是否有DDL語句，如下：

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

到這里，是不是突然心弦一緊呢？ so……如果你決定繼續(xù)使用mysqldump，那么以后搭建好備庫之后，一定要記得校驗(yàn)一下主備數(shù)據(jù)一致性！?。?/span>

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

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

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

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

• --master-data[=value] 

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

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

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

• 使用innobackupex備份工具。

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