恢複MySQL主從資料一致性的總結

恢複MySQL主從資料一致性的總結

今日上午，同事告知，MySQL主從資料庫的資料不一致，猜測備庫在同步過程中出現了問題，於是，登上備庫，使用 mysql> show slave status\G查看，果然，備庫在insert語句中因違反主鍵約束，導致備庫停止了同步。現在的問題很明確，就是如何恢複主從庫資料的一致性。

可選方案如下：

一、查看Master最新的Position，將其作為Slave複製的起點。

這種思路體現的是過去的不一致既往不咎，現在保持同步即可。看起來，這個思路和恢複主從庫資料的一致性的初衷有所違背，但這種方法，簡單，高效，在測試環境，對曆史資料要求不高的情境中可使用。

二、必須嚴格的恢複主從庫資料的一致性。

在這裡，也有兩種思路：

1. 備份主庫資料，並在從庫上恢複，在曆史資料一致性的基礎上開啟同步，但這種方法比較麻煩，必須在主庫上執行鎖表操作，阻止用戶端對於表資料的更新操作，而且在資料量大的情況下，備份也是個耗時的工程。其實，這種方法在實際生產環境中也很少用。

2. Skip掉相關錯誤

其實，這個說活不是很嚴謹，準備的說，是跳過相關的事務。在我今天這種情況下，就是skip掉因違反主鍵約束而失敗的insert語句。

 

如何跳過相關事務

一、停止slave服務

二、SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

三、開啟slave服務。

這裡跳過的是一個事務。當然，也可以跳過多個事務，但要謹慎，畢竟，你並不知道跳過的是什麼事務。

建議：可反覆執行上述步驟，仔細查看導致從庫不能同步的語句。有的時候，阻止從庫的事務太多，這種方法就顯得略為低效。

可分析主庫日誌的事務，來確定SQL_SLAVE_SKIP_COUNTER的合適值。具體步驟如下：

一、在備庫中執行show slave status\G，確認以下兩個參數

根據上述兩個參數的值，在主庫中查看當前阻礙從庫複製的事務以及之後的事務。

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

這個是查看記錄檔mysql-bin.000217中事務ID為673146776後的所有事務。

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

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

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

也可在主機環境下通過mysqlbinlog命令查看

# mysqlbinlog mysql-bin.000217 --start-position=673146776

 

如何查詢語句的執行情況

在從庫跳過相關事務，重新啟動Slave後，Slave_IO_Running，Slave_SQL_Running兩項均顯示“YES”，但Seconds_Behind_Master並沒有馬上下降，反而緩慢上升。

這時候，通過show processlist語句查看線程的執行情況，發現第一條語句執行時間太長，“State”列顯示“Sending data”。關於“Sending data”的含義，官方說明如下：

可見，該語句涉及了大量的磁碟讀。

為了進一步分析該語句的耗時分布，可設定profiling變數。步驟如下：

一、在查詢開始之前，設定set profiling=on;

二、在語句執行完畢後，通過show profiles查看語句的Query_ID。

三、通過show profile for query Query_ID 查看語句的具體執行情況。

最後也發現，該語句在Sending data階段耗時過久。

總結：

1. 在執行stop slave的時候，stop slave命令被hang住了，在網上查詢了相關資料，可能與Slave中有長SQL或Locked的SQL執行有關，在這裡，除show processlist外，最好不要執行show slave status以及slave stop等slave相關命令。那麼如何解決該問題呢？等待鎖定Slave SQL的線程結束，或者重啟資料庫。我選擇了後者。

2. 在重啟備庫的過程中，還有段小插曲，在執行start slave命令的時候，報如下錯誤：ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository。網上很多資料都是推薦重新設定主從叢集，這樣又回到了開頭的方案選擇部分了。奇怪的時，我關閉了從庫，重新啟動，又好了。而兩次啟動命令唯一的差別就是前一次啟動使用的是mysqld，後一次啟動使用的是mysqld_safe，而且多帶了一個--user參數。

本文永久更新連結地址：