如何恢復(fù)MySQL主從數(shù)據(jù)一致性

最近被告知，MySQL主從數(shù)據(jù)庫(kù)的數(shù)據(jù)不一致，猜測(cè)備庫(kù)在同步過(guò)程中出現(xiàn)了問(wèn)題，于是，登上備庫(kù)，使用 mysql> show slave status\G查看，果然，備庫(kù)在insert語(yǔ)句中因違反主鍵約束，導(dǎo)致備庫(kù)停止了同步。現(xiàn)在的問(wèn)題很明確，就是如何恢復(fù)主從庫(kù)數(shù)據(jù)的一致性。

可選方案如下：

一、查看Master最新的Position，將其作為Slave復(fù)制的起點(diǎn)。

這種思路體現(xiàn)的是過(guò)去的不一致既往不咎，現(xiàn)在保持同步即可。看起來(lái)，這個(gè)思路和恢復(fù)主從庫(kù)數(shù)據(jù)的一致性的初衷有所違背，但這種方法，簡(jiǎn)單，高效，在測(cè)試環(huán)境，對(duì)歷史數(shù)據(jù)要求不高的場(chǎng)景中可使用。

二、必須嚴(yán)格的恢復(fù)主從庫(kù)數(shù)據(jù)的一致性。

在這里，也有兩種思路：

1. 備份主庫(kù)數(shù)據(jù)，并在從庫(kù)上恢復(fù)，在歷史數(shù)據(jù)一致性的基礎(chǔ)上開(kāi)啟同步，但這種方法比較麻煩，必須在主庫(kù)上執(zhí)行鎖表操作，阻止客戶(hù)端對(duì)于表數(shù)據(jù)的更新操作，而且在數(shù)據(jù)量大的情況下，備份也是個(gè)耗時(shí)的工程。其實(shí)，這種方法在實(shí)際生產(chǎn)環(huán)境中也很少用。

2. Skip掉相關(guān)錯(cuò)誤

其實(shí)，這個(gè)說(shuō)活不是很?chē)?yán)謹(jǐn)，準(zhǔn)備的說(shuō)，是跳過(guò)相關(guān)的事務(wù)。在我今天這種情況下，就是skip掉因違反主鍵約束而失敗的insert語(yǔ)句。

如何跳過(guò)相關(guān)事務(wù)

一、停止slave服務(wù)

二、SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

三、開(kāi)啟slave服務(wù)。

這里跳過(guò)的是一個(gè)事務(wù)。當(dāng)然，也可以跳過(guò)多個(gè)事務(wù)，但要謹(jǐn)慎，畢竟，你并不知道跳過(guò)的是什么事務(wù)。

建議：可反復(fù)執(zhí)行上述步驟，仔細(xì)查看導(dǎo)致從庫(kù)不能同步的語(yǔ)句。有的時(shí)候，阻止從庫(kù)的事務(wù)太多，這種方法就顯得略為低效。

可分析主庫(kù)日志的事務(wù)，來(lái)確定SQL_SLAVE_SKIP_COUNTER的合適值。具體步驟如下：

1、在備庫(kù)中執(zhí)行show slave status\G，確認(rèn)以下兩個(gè)參數(shù)

根據(jù)上述兩個(gè)參數(shù)的值，在主庫(kù)中查看當(dāng)前阻礙從庫(kù)復(fù)制的事務(wù)以及之后的事務(wù)。

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776;

這個(gè)是查看日志文件mysql-bin.000217中事務(wù)ID為673146776后的所有事務(wù)。

當(dāng)然，SHOW BINLOG EVENTS的用法還是相當(dāng)靈活的，下述方式均可。

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776\G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776 limit 10;

也可在主機(jī)環(huán)境下通過(guò)mysqlbinlog命令查看

代碼如下:# mysqlbinlog mysql-bin.000217 --start-position=673146776

如何查詢(xún)語(yǔ)句的執(zhí)行情況

在從庫(kù)跳過(guò)相關(guān)事務(wù)，重新啟動(dòng)Slave后，Slave_IO_Running，Slave_SQL_Running兩項(xiàng)均顯示“YES”，但Seconds_Behind_Master并沒(méi)有馬上下降，反而緩慢上升。

這時(shí)候，通過(guò)show processlist語(yǔ)句查看線程的執(zhí)行情況，發(fā)現(xiàn)第一條語(yǔ)句執(zhí)行時(shí)間太長(zhǎng)，“State”列顯示“Sending data”。關(guān)于“Sending data”的含義，官方說(shuō)明如下：

可見(jiàn)，該語(yǔ)句涉及了大量的磁盤(pán)讀。

為了進(jìn)一步分析該語(yǔ)句的耗時(shí)分布，可設(shè)置profiling變量。步驟如下：

一、在查詢(xún)開(kāi)始之前，設(shè)置set profiling=on;

二、在語(yǔ)句執(zhí)行完畢后，通過(guò)show profiles查看語(yǔ)句的Query_ID。

三、通過(guò)show profile for queryQuery_ID 查看語(yǔ)句的具體執(zhí)行情況。

最后也發(fā)現(xiàn)，該語(yǔ)句在Sending data階段耗時(shí)過(guò)久。

總結(jié)：

1. 在執(zhí)行stop slave的時(shí)候，stop slave命令被hang住了，在網(wǎng)上查詢(xún)了相關(guān)資料，可能與Slave中有長(zhǎng)SQL或Locked的SQL執(zhí)行有關(guān)，在這里，除show processlist外，最好不要執(zhí)行show slave status以及slave stop等slave相關(guān)命令。那么如何解決該問(wèn)題呢？等待鎖定SlaveSQL的線程結(jié)束，或者重啟數(shù)據(jù)庫(kù)。我選擇了后者。

2. 在重啟備庫(kù)的過(guò)程中，還有段小插曲，在執(zhí)行start slave命令的時(shí)候，報(bào)如下錯(cuò)誤：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository。網(wǎng)上很多資料都是推薦重新配置主從集群，這樣又回到了開(kāi)頭的方案選擇部分了。奇怪的時(shí)，我關(guān)閉了從庫(kù)，重新啟動(dòng)，又好了。而兩次啟動(dòng)命令唯一的差別就是前一次啟動(dòng)使用的是mysqld，后一次啟動(dòng)使用的是mysqld_safe，而且多帶了一個(gè)--user參數(shù)。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com