mariadb延迟从库搭建与恢复到某个问题时间点

一 搭建延迟从库

从库变成延迟从库

ssh 10.10.30.107
stop slave;
change master to master_delay=3600;
start slave;

show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.30.108
                  Master_User: rep
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4725
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 627
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 328
              Relay_Log_Space: 989
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: 2421942
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 1300301089
                   Using_Gtid: Current_Pos
                  Gtid_IO_Pos: 0-1300301089-26
                Parallel_Mode: conservative
                    SQL_Delay: 3600       #延迟3600秒
          SQL_Remaining_Delay: 3596       #当前延迟时间(秒)
      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event  #sql线程状态
1 row in set (0.00 sec)

二 延迟从库恢复到某个问题点

--1 主库,查看delete前的binlog,准备将延迟从库恢复到 delete 前的位置
dba@10.10.30.108(恢复测试):school 04:55:46>show binlog events in 'mysql-bin.000001';
+------------------+------+-------------------+------------+-------------+-------------------------------------------------------+
| Log_name         | Pos  | Event_type        | Server_id  | End_log_pos | Info                                                  |
+------------------+------+-------------------+------------+-------------+-------------------------------------------------------+
| mysql-bin.000001 | 5962 | Gtid              | 1300301089 |        6004 | BEGIN GTID 0-1300301089-33                            |
| mysql-bin.000001 | 6004 | Annotate_rows     | 1300301089 |        6079 | insert into li values(3,'s03',now()),(4,'s04',now())  |
| mysql-bin.000001 | 6079 | Table_map         | 1300301089 |        6131 | table_id: 99 (school.li)                              |
| mysql-bin.000001 | 6131 | Write_rows_v1     | 1300301089 |        6192 | table_id: 99 flags: STMT_END_F                        |
| mysql-bin.000001 | 6192 | Xid               | 1300301089 |        6223 | COMMIT /* xid=6757198 */                              |
| mysql-bin.000001 | 6223 | Gtid              | 1300301089 |        6265 | GTID 0-1300301089-34                                  |
| mysql-bin.000001 | 6265 | Query             | 1300301089 |        6364 | use `school`; create table li_bak_02 like li          |
| mysql-bin.000001 | 6364 | Gtid              | 1300301089 |        6406 | BEGIN GTID 0-1300301089-35                            |
| mysql-bin.000001 | 6406 | Annotate_rows     | 1300301089 |        6456 | delete from li where sno =4                           |
| mysql-bin.000001 | 6456 | Table_map         | 1300301089 |        6508 | table_id: 99 (school.li)                              |
| mysql-bin.000001 | 6508 | Delete_rows_v1    | 1300301089 |        6555 | table_id: 99 flags: STMT_END_F                        |
| mysql-bin.000001 | 6555 | Xid               | 1300301089 |        6586 | COMMIT /* xid=6757767 */                              |
+------------------+------+-------------------+------------+-------------+-------------------------------------------------------+
102 rows in set (0.00 sec)

--2 延迟从库
ssh 10.10.30.107
stop slave;
change master to master_delay=0;
START SLAVE  UNTIL  MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 6364;

--3 在延迟从库,导出数据,然后在主库恢复