mysql的延迟从库与恢复

一：slave节点

(root@localhost) [(none)] stop slave;
Query OK, 0 rows affected (0.81 sec)

(root@localhost) [(none)] change master to master_delay=300;
Query OK, 0 rows affected (0.24 sec)

(root@localhost) [(none)] start slave;
Query OK, 0 rows affected (0.96 sec)

(root@localhost) [(none)] show slave status\G;
*************************** 1. row ***************************
                    SQL_Delay: 300              # 设置延迟时间为300秒。
          SQL_Remaining_Delay: 201     # 表示下一次sql进程执行回放relay日志事务时间是多少秒。
 

二：master节点

(root@localhost) [(none)] create database world;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [(none)] use world;
Database changed
(root@localhost) [world] create table t1(id int,age varchar(20));
Query OK, 0 rows affected (0.08 sec)

(root@localhost) [world] insert into t1 values(1,"xiaoming"),(2,"honghong");
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

(root@localhost) [world] drop database world;    #突然误操作了，删除了某个库
Query OK, 1 row affected (0.08 sec)
 

三：通过延时从库进行恢复world库

1：slave节点，停止sql线程

(root@localhost) [(none)] stop slave sql_thread;
Query OK, 0 rows affected (0.02 sec)

2：记录已经回放relay日志的位置点

(root@localhost) [mysql] show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.128.232.131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000004
          Read_Master_Log_Pos: 2063
               Relay_Log_File: mysql-130-relay-bin.000002    #回放relay的文件
                Relay_Log_Pos: 423                                         #回放relay的位置点
        Relay_Master_Log_File: mysql-binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
 

3：slave节点：窃取relay日志，进行source，进行手动回放，同时要找到删除world的操作位置点，下面两种方式可以查看回放relay日志的位置点对应master的binlog日志的位置点。

  (root@localhost) [mysql] show relaylog events in 'mysql-130-relay-bin.000002';

 4.通过gtid进行截取relay日志，由于再删除world数据库的时候，relay已经回放到了423，423位置点对应的gtid是9这个事务id，同时删除world这个操作事务id是12，因此不能包含，所以事务id是9-11，9是创建world数据库的事务id，因此现在从库是没有world库的，

 5.截取relay的日志，事务id为9-11.

[root@mysql-130 data]# mysqlbinlog -vv --include-gtids="ad37fab5-f8b7-11eb-afce-000c293ef59b:9-11" mysql-130-relay-bin.000002 >/tmp/world.sql
 

6.从库手动source 截取的relay日志，world库查看是否有了。

(root@localhost) [mysql] set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [mysql] source /tmp/world.sql;
Query OK, 0 rows affected (0.00 sec）

7.把恢复的world库，备份，由于是gtid，之前主库执行过操作，gtid会判断是执行过的gtid事务，备份的事情要过滤gtid，让主库产生新的gtid。

 mysqldump -uroot -p"1111aaA_"  -R -E --triggers --master-data=2  --single-transaction --max_allowed_packet=128M --set-gtid-purged=OFF -B world >/tmp/world_data.sql

    

8.master节点恢复从库备份的world库

[root@mysql-130 data]# scp /tmp/world_data.sql 192.128.232.131:/tmp/

#恢复到主库，可以看到数据恢复

 

9.slave节点：

   数据恢复到延迟从库，可以开启延时从库的sql线程， 

 

10. master执行一次测试操作，slave也是可以同步，主从一样可以继续使用，只是从库会延时主库很多日志。当然反正是延迟从库，不建议生产读使用，只是为了做恢复数据的一种方案。