【MySQL】通过SQL_Thread快速恢复binlog

常见binlog增量恢复方式
先解析成sql文件，再导入MySQL

1mysqlbinlog mysql-bin.000001 --start-position=n > /data/add.sql2mysqlbinlog mysql-bin.000002 ... mysql-bin.n >> /data/add.sql3mysql -u -p -S < /data/add.sql
1.
直接管道到MySQL中

1mysqlbinlog mysql-bin.000001 --start-position=n | mysql -u -p -S2mysqlbinlog mysql-bin.000002 ... mysql-bin.n | mysql -u -p -S
1.
直接管道进去的方式，并不一定安全，手册上也有指明：

1If you have more than one binary log to execute on the MySQL server,2the safe method is to process them all using a single connection to the server.
1.
关于这种方式的更多内容，可以参考：
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呢？

通过sql_thread恢复
处理思路：
1）重新初始化一个实例，恢复全量备份文件。
2）找到第一个
binlog
文件的
position
，和剩下所有的
binlog
。
3）将
binlog
伪装成
relaylog
，通过
sql thread
增量恢复。

这里只介绍核心部分，即伪装成relaylog的过程。

① 将relay log info的repository改到file中，并生成这个文件。（
relay_log_info_repositor
写到配置文件中）

1SET GLOBAL relay_log_info_repository='FILE';2CHANGE MASTER TO master_host='1', master_password='1', master_user='1', master_log_file='1', master_log_pos=4;
1.
通过change命令，是为了告诉MySQL自己为一个slave实例，因为无需用到
IO_Thread
，故
host
，
password
，
user
等可以随意填写。
并且通过该步骤，生成
relay.info
文件。

② 关闭实例，将需要增量的binlog文件伪装成relaylog。

1cp mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 $relaylogdir2cd $relaylogdir3rename mysql-bin. mysql-relay. mysql-bin.0000*4chown mysql:mysql -R .
1.
通过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.00000321276895
1.
第二三行对应
Relay_log_name
和
Relay_log_pos
，等同于：

1mysqlbinlog mysql-relay.000003 --start-position=1276895 | mysql -u -p -S
1.
修改该文件是为了告诉
SQL_Thread
从哪一个
file
和哪一个
position
开始执行
events
。

再修改
relay-log.index
，清空原有信息，添加以下信息，为的是告诉
SQL_Thread
还有哪些
relaylog
是需要执行的。

1/data/mysql57/relaylog/mysql-relay.0000032/data/mysql57/relaylog/mysql-relay.0000043/data/mysql57/relaylog/mysql-relay.0000054/data/mysql57/relaylog/mysql-relay.0000065/data/mysql57/relaylog/mysql-relay.0000076/data/mysql57/relaylog/mysql-relay.0000087/data/mysql57/relaylog/mysql-relay.0000098/data/mysql57/relaylog/mysql-relay.000010
1.
④ 启动实例，开启
SQL_Thread
：

1START SLAVE sql_thread ;
1.
⑤ 检查复制状态：

1mysql> SHOW SLAVE STATUS\G 2*************************** 1. row *************************** 3Slave_IO_State: 4Master_Host: 1 5Master_User: 1 6Master_Port: 3306 7Connect_Retry: 60 8Master_Log_File: 1 9Read_Master_Log_Pos: 410Relay_Log_File: mysql-relay.000003 -- 已经执行到的日志名11Relay_Log_Pos: 11529982 -- 已经执行到日志的位置12Relay_Master_Log_File: 113Slave_IO_Running: No14Slave_SQL_Running: Yes15Replicate_Do_DB:16Replicate_Ignore_DB:17Replicate_Do_Table:18Replicate_Ignore_Table:19Replicate_Wild_Do_Table:20Replicate_Wild_Ignore_Table:21Last_Errno: 022Last_Error:23Skip_Counter: 024Exec_Master_Log_Pos: 1152998225Relay_Log_Space: 534703891326Until_Condition: None27Until_Log_File:28Until_Log_Pos: 029Master_SSL_Allowed: No30Master_SSL_CA_File:31Master_SSL_CA_Path:32Master_SSL_Cert:33Master_SSL_Cipher:34Master_SSL_Key:35Seconds_Behind_Master: 274354 -- 若变为0，则表示已经增量完毕36Master_SSL_Verify_Server_Cert: No37Last_IO_Errno: 038Last_IO_Error:39Last_SQL_Errno: 040Last_SQL_Error:41Replicate_Ignore_Server_Ids:42Master_Server_Id: 043Master_UUID:44Master_Info_File: /data/mysql57/master.info45SQL_Delay: 046SQL_Remaining_Delay: NULL47Slave_SQL_Running_State: Reading event from the relay log48Master_Retry_Count: 8640049………………………………
1.
至此，已经可以通过
sql_thread
来增量恢复
binlog
了。
当然，上述过程只针对于指定
--start-position
的方式来恢复，比如单点MySQL实例在
innodb_force_recovery=6
还无法启动的情况下，就需要通过最近一次可用的全量备份+剩下的binlog恢复。

该测试使用的版本为：
MySQL 5.7.16

效果：
快速恢复到指定位置点，即通过全备文件+
binlog
恢复到故障前的最后一个
position
。

针对--stop-position
比如在某一时刻执行了错误的sql，如truncate等操作，同样也可以通过该办法。
但与指定
--start-position
的方法有些许不同：
只需要将
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
。

当然，这种数据回档操作，也可以考虑带有
flashback
功能的工具。

性能对比
对于同一组binlog文件增量：
通过
mysqlbinlog
解析+导入的时间为
69min
。
而通过
SQL_Thread
的执行时间为
41min
。

并且在需要增量的binlog文件越大的情况下，效果越明显。

总结
优点：
1）可以断点恢复，人为控制进度，比如stop slave或者遇到错误时，可以知道出错点在哪。
2）性能相对较好，在大量binlog的情况下，可以加快恢复速度。
3）在某些版本可能可以通过MTS来加快增量速度，使恢复更快。

缺点：
1）需要关闭mysqld。
2）手动执行过程较mysqlbinlog方式更为复杂。

mysqlbinlog --start-position
与通过修改
relay.info
的第三行等效：
用途都是指定开始执行的第一个
position
。

mysqlbinlog --stop-position
与通过在启动
SQL_Thread
时指定
UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
等效：
用途都是指定结束执行的最后一个
position
。

全文完。

Enjoy MySQL :)
-----------------------------------
【MySQL】通过SQL_Thread快速恢复binlog
https://blog.51cto.com/imysql/3181867