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

# 修改系统时间，增加7200秒
[root@greatsql]# date -s "`date --date '7200 second'`"

# 重启slave线程
[root@GreatSQL](none)>  STOP SLAVE; START SLAVE;
1.
2.
3.
4.
5.

[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
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

          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
1.
2.
3.
4.
5.

[root@greatsql]# ls -la relay-bin.*
-rw-r----- 1 mysql mysql  418 Jun 13 13:31 relay-bin.000004
-rw-r----- 1 mysql mysql 2286 Jun 13 13:33 relay-bin.000005
-rw-r----- 1 mysql mysql   58 Jun 13 13:31 relay-bin.index

[root@greatsql]# mysqlbinlog --base64-output=decode-rows -vvv relay-bin.000005
...
# at 196
#700101  8:00:00 server id 5001  end_log_pos 0 CRC32 0xaf7e0f79         Rotate to binlog.000011  pos: 746
# at 240
#210613 11:26:08 server id 5001  end_log_pos 0 CRC32 0xfa7fd769         Start: binlog v 4, server v 8.0.22-13 created 210613
 11:26:08
# at 361
#210613 13:33:41 server id 5001  end_log_pos 825 CRC32 0xd5161853       GTID    last_committed=1        sequence_number=3
       rbr_only=yes    original_committed_timestamp=1623562421106412   immediate_commit_timestamp=1623562421106412     transaction_length=275
...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

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

# 只关闭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;
1.
2.
3.
4.
5.
6.
7.
8.

# 主库宕机后，查看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 mysql  58 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 mysql  29 Jun 13 15:18 relay-bin.index
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.

#原文出处 https://dev.mysql.com/doc/refman/8.0/en/replica-logs.html

The 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”.
1.
2.
3.