共計 4230 個字符，預計需要花費 11 分鐘才能閱讀完成。

這篇文章給大家介紹如何通過 MySQL relaylog + SQL_Thread 增量恢復 binlog，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

數據回檔常常是使用全量備份 +binlog 增量實現的。
而數據量很大的情況下，增量恢復 binlog 一直是一個苦惱的問題，因為恢復 binlog 速度十分慢，并且容易出錯。

恢復 binlog 文件一般有兩種方法：

〇 先解析成 sql 文件，再導入 MySQL

mysqlbinlog mysql-bin.000001 –start-position=n /data/add.sql

mysqlbinlog mysql-bin.000002 … mysql-bin.n /data/add.sql

mysql -u -p -S /data/add.sql

〇 直接管道到 MySQL 中

mysqlbinlog mysql-bin.000001 –start-position=n | mysql -u -p -S

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 中，并生成這個文件。

SET GLOBAL relay_log_info_repository= FILE

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。

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

cd $relaylogdir

rename mysql-bin. mysql-relay. mysql-bin.0000*

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：

/data/mysql57/relaylog/mysql-relay.000003

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 是需要執行的。

/data/mysql57/relaylog/mysql-relay.000003

/data/mysql57/relaylog/mysql-relay.000004

/data/mysql57/relaylog/mysql-relay.000005

/data/mysql57/relaylog/mysql-relay.000006

/data/mysql57/relaylog/mysql-relay.000007

/data/mysql57/relaylog/mysql-relay.000008

/data/mysql57/relaylog/mysql-relay.000009

/data/mysql57/relaylog/mysql-relay.000010

④ 啟動實例，開啟 SQL_Thread：

START SLAVE sql_thread ;

只需要開啟 SQL_Thread 即可

⑤ 檢查復制狀態：

mysql SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 1

Master_User: 1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: 1

Read_Master_Log_Pos: 4

Relay_Log_File: mysql-relay.000003    — 已經執行到的日志名

Relay_Log_Pos: 11529982    — 已經執行到日志的位置

Relay_Master_Log_File: 1

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 11529982

Relay_Log_Space: 5347038913

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 274354    — 若變為 0，則表示已經增量完畢

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: /data/mysql57/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Reading event from the relay log

Master_Retry_Count: 86400

………………………………

該測試使用的版本為：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。

關于如何通過 MySQL relaylog + SQL_Thread 增量恢復 binlog 就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。