通過MySQL relaylog + SQL_Thread 增量恢復binlog

神諭丶發表於2017-08-17
MySqlthread
資料回檔常常是使用全量備份+binlog增量實現的。
而資料量很大的情況下，增量恢復binlog一直是一個苦惱的問題，因為恢復binlog速度十分慢，並且容易出錯。

恢復binlog檔案一般有兩種方法：

〇 先解析成sql檔案，再匯入MySQL
  1. mysqlbinlog mysql-bin.000001 --start-position=n > /data/add.sql
  2. mysqlbinlog mysql-bin.000002 ... mysql-bin.n >> /data/add.sql
  3. mysql -u -p -S < /data/add.sql

〇 直接管道到MySQL中
  1. mysqlbinlog mysql-bin.000001 --start-position=n | mysql -u -p -S
  2. mysqlbinlog mysql-bin.000002 ... mysql-bin.n | mysql -u -p -S
關於這種方式的更多info，可以參考：
https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

然而這兩種方式原理都是一樣的，通過mysqlbinlog解析成sql並匯入到MySQL中。

〇 優點：
    操作方便，邏輯簡單。
    無需關閉mysqld。

〇 缺點：
    遇到ERROR難以定位位置，難以“斷點恢復”。
    特殊字元或字符集的問題。
    max_allowed_packet問題。
    恢復速度慢。
    

因為relaylog和binlog本質實際上是一樣的，所以是否可以利用MySQL自身的sql_thread來增量binlog呢？

〇 處理思路：
    1）重新初始化一個例項，恢復全量備份檔案。
    2）找到第一個binlog檔案的position，和剩下所有的binlog。
    3）將binlog偽裝成relaylog，通過sql thread增量恢復。

這裡只介紹核心部分，即偽裝成relaylog的過程。


① 將relay log info的repository改到file中，並生成這個檔案。
  1. SET GLOBAL relay_log_info_repository='FILE';
  2. CHANGE MASTER TO master_host='1',master_password='1',master_user='1',master_log_file='1',master_log_pos=4;
通過change命令，是為了告訴MySQL自己為一個slave例項，因為無需用到IO_Thread，故host，password，user等可以隨意填寫。
並且通過該步驟，生成relay.info檔案。


② 關閉例項，將需要增量的binlog檔案偽裝成relaylog。
  1. cp mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 $relaylogdir
  2. cd $relaylogdir
  3. rename mysql-bin. mysql-relay. mysql-bin.0000*
  4. chown mysql:mysql -R .
通過cp命令將binlog移動到$relaylogdir裡，該變數取決於例項的選項引數，預設放在datadir下。
再將binlog批量改名成relaylog，並且給予對應的許可權，否則會報錯OS error code  13:  Permission denied。


③ 修改relay.info檔案和relay-log.index檔案
將relay.info的第二三行改成需要執行的第一個binlog（現在是relaylog）的檔名和position：
  1. /data/mysql57/relaylog/mysql-relay.000003
  2. 1276895
第二三行對應Relay_log_name和Relay_log_pos，等同於：
mysqlbinlog mysql-relay.000003 --start-position=1276895 | mysql -u -p -S
修改該檔案是為了告訴SQL_Thread從哪一個檔案和哪一個position開始執行事務

再修改relay-log.index，清空原有資訊，新增以下資訊，為的是告訴SQL_Thread還有哪些relaylog是需要執行的。
  1. /data/mysql57/relaylog/mysql-relay.000003
  2. /data/mysql57/relaylog/mysql-relay.000004
  3. /data/mysql57/relaylog/mysql-relay.000005
  4. /data/mysql57/relaylog/mysql-relay.000006
  5. /data/mysql57/relaylog/mysql-relay.000007
  6. /data/mysql57/relaylog/mysql-relay.000008
  7. /data/mysql57/relaylog/mysql-relay.000009
  8. /data/mysql57/relaylog/mysql-relay.000010

④ 啟動例項，開啟SQL_Thread：
  1. START SLAVE sql_thread ;
只需要開啟SQL_Thread即可

⑤ 檢查複製狀態：
  1. mysql> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3. Slave_IO_State:
  4. Master_Host: 1
  5. Master_User: 1
  6. Master_Port: 3306
  7. Connect_Retry: 60
  8. Master_Log_File: 1
  9. Read_Master_Log_Pos: 4
  10. Relay_Log_File: mysql-relay.000003    -- 已經執行到的日誌名
  11. Relay_Log_Pos: 11529982        -- 已經執行到日誌的位置
  12. Relay_Master_Log_File: 1
  13. Slave_IO_Running: No
  14. Slave_SQL_Running: Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 11529982
  25. Relay_Log_Space: 5347038913
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: 274354        -- 若變為0，則表示已經增量完畢
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id: 0
  43. Master_UUID:
  44. Master_Info_File: /data/mysql57/master.info
  45. SQL_Delay: 0
  46. SQL_Remaining_Delay: NULL
  47. Slave_SQL_Running_State: Reading event from the relay log
  48. Master_Retry_Count: 86400
  49. ………………………………

該測試使用的版本為：MySQL 5.7.16

效果：恢復全備檔案+binlog恢復到故障前的最後一個position。

其他場景也適用，比如在某一時刻執行了錯誤的sql，如truncate等操作，同樣也可以通過該辦法。
只需要將START SLAVE sql_thread後新增一個 UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos 即可。
該選項用於控制SQL_Thread執行到的最後的position，類似於mysqlbinlog mysql-bin.n --stop-position=$log_pos 。

除了更準確的能夠恢復錯誤之外，還有一個最大的好處是加快了binlog增量的速度。

補充一個額外的測試資料
對於同一組binlog檔案增量：
通過mysqlbinlog解析+匯入的時間為69min。
而通過SQL_Thread的執行時間為41min。

並且在需要增量的binlog檔案越大的情況下，效果越明顯。

〇 優點：
    可以斷點恢復，人為控制進度，比如stop slave或者遇到錯誤時，可以斷點恢復。
    效能好，在大量binlog的情況下，可以加快恢復速度。
    在某些版本可以利用多執行緒複製來加快增量速度，時恢復更快。

〇 缺點：
    需要關閉mysqld。
    手動執行過程較mysqlbinlog方式更為複雜。

〇 總結：
mysqlbinlog --start-position 與 通過修改relay.info的第三行等效：
用途都是指定開始執行的第一個position。

mysqlbinlog --stop-position 與 通過在啟動SQL_Thread時指定UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos等效：
用途都是指定結束執行的最後一個position。


作者微信公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-2143726/，如需轉載，請註明出處，否則將追究法律責任。

相關文章