万答#4，延迟从库加上MASTER_DELAY，主库宕机后如何快速恢复服务 mysql数据库sql

欢迎来到 GreatSQL社区分享的MySQL技术文章，如有疑问或想学习的内容，可以在下方评论区留言，看到后会进行解答
当主库宕机后，延迟从库如何才能"取消"主动延迟，以便恢复服务？

问题描述本问题来自一位群友，他遇到的情况我简单归纳一下：

实例A是主库，B是延迟从库（设置了延迟7200秒）。

当A挂掉后（已无法连接，或无法启动），希望用B提升成主库。
但是在B上执行 change master to MASTER_DELAY=0 后，B上已经保存的7200秒的relay文件也会被清除掉，并尝试再次从A获取binlog，这样会造成7200秒的数据丢失，未能达成目的。

TA想问：在这样的场景下，还有办法让B库尽快跑完这7200秒延迟数据吗，或者正确的办法是什么呢？
问题解决先回答问题：这个需求是有办法达成的（而且还不只一种方法），最正确的方法也并不麻烦/复杂（看到最后），只不过有些小窍门要注意下。
方法1，修改系统时间
也就是修改B主机的系统时间，将其往后调整超过7200秒，然后重启slave线程，就能让SQL_THREAD继续应用relay log了。所有relay log应用完毕后，再将系统时间修改回来。

# 修改系统时间，增加7200秒 [root@greatsql]# date -s "`date --date '7200 second'`"# 重启slave线程 [root@GreatSQL](none)>STOP SLAVE; START SLAVE;

注意：这种方法（潜在）影响很大，可能对其他系统应用有影响，或者MySQL里部分涉及到日期时间的结果也会受到影响，非常不推荐。
方法2，自行手动恢复relay log/binlog
当主库（A）宕机后，查看当前slave的状态：

[root@GreatSQL](none)> SHOW SLAVE STATUS\G ... Master_Log_File: binlog.000011 Read_Master_Log_Pos: 2671 Relay_Log_File: relay-bin.000005 Relay_Log_Pos: 361 Relay_Master_Log_File: binlog.000011 Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... Exec_Master_Log_Pos: 746 Relay_Log_Space: 2704 ... Seconds_Behind_Master: 387 ... SQL_Delay: 7200 SQL_Remaining_Delay: 6814 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

可以看到这时候有387秒的落后，主动延迟7200秒，还有6814秒之后才能应用最新的relay log。虽然事务有延迟，但其实slave已经把binlog都复制过来了，在relay log里。

Read_Master_Log_Pos: 2671<-- 已读取到最新的binlog了，pos = 2671 Relay_Log_File: relay-bin.000005<-- 对应的relay log Relay_Log_Pos: 361<-- 对应的relay log pos ... Exec_Master_Log_Pos: 746<-- 但只apply到 pos = 746

现在，只需要手动把这中间的差异数据拿出来apply就可以了。解析relay log，找到对应master binlog pos = 746的位置，或者直接指定realy log pos = 361的位置也可以：

[root@greatsql]# ls -la relay-bin.* -rw-r----- 1 mysql mysql418 Jun 13 13:31 relay-bin.000004 -rw-r----- 1 mysql mysql 2286 Jun 13 13:33 relay-bin.000005 -rw-r----- 1 mysql mysql58 Jun 13 13:31 relay-bin.index[root@greatsql]# mysqlbinlog --base64-output=decode-rows -vvv relay-bin.000005 ... # at 196 #7001018:00:00 server id 5001end_log_pos 0 CRC32 0xaf7e0f79Rotate to binlog.000011pos: 746 # at 240 #210613 11:26:08 server id 5001end_log_pos 0 CRC32 0xfa7fd769Start: binlog v 4, server v 8.0.22-13 created 210613 11:26:08 # at 361 #210613 13:33:41 server id 5001end_log_pos 825 CRC32 0xd5161853GTIDlast_committed=1sequence_number=3 rbr_only=yesoriginal_committed_timestamp=1623562421106412immediate_commit_timestamp=1623562421106412transaction_length=275 ...

确认对应的事务是存在的，接下来就可以利用relay log恢复数据了。

[root@greatsql]# mysqlbinlog relay-bin.000005 --start-position=361 | mysql -S./mysql.sock -f

当然了，如果此时A主机还可以连接，只是mysqld服务无法启动的话，亦可直接用A主机上的binlog进行恢复。
这种方法需要细心谨慎，手眼配合密切，容易出错，因此也不推荐。
方法3，正确理解MASTER_DELAY，一键搞定
前面铺垫了那么多，看起来想要让延迟从库快速恢复好像有点麻烦的样子。
事实上，只要正确理解，一键命令就搞定了。
【万答#4，延迟从库加上MASTER_DELAY，主库宕机后如何快速恢复服务】当主库发生故障宕机后，binlog其实已经都复制到从库并写入成relay log了。当然了，为避免误操作，建议先备份relay log。
接下来的操作很重要，看准了，一定不要做错：

# 只关闭SQL_THREAD，而不是直接关闭整个SLAVE服务 [root@GreatSQL](none)> STOP SLAVE SQL_THREAD; # 修改MASTER_DELAY，使之不再延迟 [root@GreatSQL](none)> CHANGE MASTER TO MASTER_DELAY=0; # 再次启动SQL_THREAD [root@GreatSQL](none)> START SLAVE SQL_THREAD;

主库虽然宕机了，但从库上只有IO_THREAD会报告连接错误，SQL_THREAD还是可以正常工作的。
上述操作的关键点在于，修改 MASTER_DELAY 时并没有先停掉 IO_THREAD，否则会清空所有的relay log，尝试去主库再次拉取。
再次启动 SQL_THREAD 之后，从库就会继续应用relay log，待到全部应用完毕后，完成必要的数据校验，即可提成成为新的主库，对外提供服务了。
看，真的挺简单的吧。
现在来验证下：完全停掉SLAVE服务后修改MASTER_DELAY值，再启动SLAVE服务，此时会清空重置relay log。

# 主库宕机后，查看SLAVE状态 [root@GreatSQL](none)> SHOW SLAVE STATUS\G ... Master_Log_File: binlog.000017 Read_Master_Log_Pos: 471 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000017 Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... Exec_Master_Log_Pos: 196 ... Seconds_Behind_Master: 69 ... SQL_Delay: 7200 SQL_Remaining_Delay: 7132 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event ...# 查看relay log状态 [root@greatsql]# ls -la relay-bin.* -rw-r----- 1 mysql mysql 213 Jun 13 15:11 relay-bin.000001 -rw-r----- 1 mysql mysql 596 Jun 13 15:11 relay-bin.000002 -rw-r----- 1 mysql mysql58 Jun 13 15:11 relay-bin.index# 关闭SLAVE服务，修改MASTER_DELAY，再启动SLAVE服务 [root@GreatSQL](none)> STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0; START SLAVE; # 再次查看relay log，发现被清空重置了 [root@greatsql]# ls -la relay-bin.* -rw-r----- 1 mysql mysql 156 Jun 13 15:18 relay-bin.000001 -rw-r----- 1 mysql mysql29 Jun 13 15:18 relay-bin.index

所以，请记住了，当延迟从库要修改延迟设置时，只需重启SQL_THREAD，千万别图省事重启整个SLAVE服务，这样relay log就不会被清空重置了，也就能实现快速恢复并提升为主库。为以防万一，也请务必备份好relay log。
因为relay log是由 IO_THREAD 负责的，所以只要 IO_THREAD 不重启，就不会清空重置。看下MySQL文档中关于relay log的说明：

#原文出处 https://dev.mysql.com/doc/refman/8.0/en/replica-logs.htmlThe replica's relay log, which is written by the replication I/O thread, contains the transactions read from the replication source server's binary log. The transactions in the relay log are applied on the replica by the replication SQL thread. For information about the relay log, see Section 17.2.4.1, “The Relay Log”.

P.S，本文使用的GreatSQL 8.0.22-13，关于这个版本的说明详见 GreatSQL，打造更好的MGR生态。
延伸阅读

13.4.2.1 CHANGE MASTER TO Statement, https://dev.mysql.com/doc/ref...
18.17.2.4 Relay Log and Replication Metadata Repositories, https://dev.mysql.com/doc/ref...
18.17.4.11 Delayed Replication, https://dev.mysql.com/doc/ref...

Enjoy GreatSQL :)
文章推荐：技术分享 | MGR最佳实践(MGR Best Practice)
https://mp.weixin.qq.com/s/66...
技术分享 | 万里数据库MGR Bug修复之路
https://mp.weixin.qq.com/s/Ia...
Macos系统编译percona及部分函数在Macos系统上运算差异
https://mp.weixin.qq.com/s/jA...
技术分享 | 利用systemd管理MySQL单机多实例
https://mp.weixin.qq.com/s/iJ...
产品 | GreatSQL，打造更好的MGR生态
https://mp.weixin.qq.com/s/By...
产品 | GreatSQL MGR优化参考
https://mp.weixin.qq.com/s/5m...
关于 GreatSQL GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
微信&QQ群：
可扫码添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群，亦可直接扫码加入GreatSQL/MGR交流QQ群。