mysql sql_thread很慢_【MySQL】通过SQL_Thread快速恢复binlog

将数据库回档至指定时间点或位置，常常是使用全量备份+binlog增量实现的。

而数据量很大的情况下，增量恢复binlog一直是一个苦恼的问题。

因为恢复binlog速度十分慢，并且容易出错。

p.s. 以下所有框可左右滑动

建议横屏阅读

常见binlog增量恢复方式

先解析成sql文件，再导入MySQL

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

2mysqlbinlog mysql-bin.000002 ... mysql-bin.n >>/data/add.sql

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

直接管道到MySQL中

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

2mysqlbinlog mysql-bin.000002 ... mysql-bin.n | mysql -u -p -S

直接管道进去的方式，并不一定安全，手册上也有指明：

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.

关于这种方式的更多内容，可以参考：

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;

通过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 $relaylogdir

2cd $relaylogdir

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

4chown 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

21276895

第二三行对应Relay_log_name和Relay_log_pos，等同于：

1mysqlbinlog mysql-relay.000003 --start-position=1276895 | mysql -u -p -S

修改该文件是为了告诉SQL_Thread从哪一个file和哪一个position开始执行events。

再修改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：

1START SLAVE sql_thread ;

⑤ 检查复制状态：

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: 4

10Relay_Log_File: mysql-relay.000003    -- 已经执行到的日志名

11Relay_Log_Pos: 11529982        -- 已经执行到日志的位置

12Relay_Master_Log_File: 1

13Slave_IO_Running: No

14Slave_SQL_Running: Yes

15Replicate_Do_DB:

16Replicate_Ignore_DB:

17Replicate_Do_Table:

18Replicate_Ignore_Table:

19Replicate_Wild_Do_Table:

20Replicate_Wild_Ignore_Table:

21Last_Errno: 0

22Last_Error:

23Skip_Counter: 0

24Exec_Master_Log_Pos: 11529982

25Relay_Log_Space: 5347038913

26Until_Condition: None

27Until_Log_File:

28Until_Log_Pos: 0

29Master_SSL_Allowed: No

30Master_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: No

37Last_IO_Errno: 0

38Last_IO_Error:

39Last_SQL_Errno: 0

40Last_SQL_Error:

41Replicate_Ignore_Server_Ids:

42Master_Server_Id: 0

43Master_UUID:

44Master_Info_File: /data/mysql57/master.info

45SQL_Delay: 0

46SQL_Remaining_Delay: NULL

47Slave_SQL_Running_State: Reading event from the relay log

48Master_Retry_Count: 86400

49………………………………

至此，已经可以通过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。