mysql利用延迟复制恢复误删的表

（1）在主库3306中创建测试数据

(root@localhost) [(none)] create database test;
Query OK, 1 row affected (0.00 sec)
(root@localhost) [(none)] use test ;
Database changed
(root@localhost) [test] create table t1(id int primary key);
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [test] insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@localhost) [test] 
(root@localhost) [test] 
(root@localhost) [test] select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

（2）在从库3307中查看数据是否同步

(root@localhost) [(none)] use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost) [test] select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

（3）在从库开启延迟复制

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

(root@localhost) [test] change master to master_delay=3600;
Query OK, 0 rows affected (0.01 sec)

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

(root@localhost) [test] show slave status\G

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes        
                    SQL_Delay: 3600
          SQL_Remaining_Delay: NULL 
            Executed_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:1-3

(root@localhost) [test] 

（4）在主库中新增一条记录，并删除t1表

(root@localhost) [test] insert into t1 values(4);
Query OK, 1 row affected (0.01 sec)

(root@localhost) [test] drop table t1;
Query OK, 0 rows affected (0.01 sec)

(root@localhost) [test] select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

（5）在主库中查看drop操作在binlog的位置

(root@localhost) [test] show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1183
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:1-5
1 row in set (0.00 sec)


(root@localhost) [test] pager grep -iB 5 drop
PAGER set to 'grep -iB 5 drop'
(root@localhost) [test] show binlog events in 'mysql-bin.000001';
| mysql-bin.000001 |  815 | Query          | 4294967295 |         887 | BEGIN                                                             |
| mysql-bin.000001 |  887 | Table_map      | 4294967295 |         932 | table_id: 163 (test.t1)                                           |
| mysql-bin.000001 |  932 | Write_rows     | 4294967295 |         972 | table_id: 163 flags: STMT_END_F                                   |
| mysql-bin.000001 |  972 | Xid            | 4294967295 |        1003 | COMMIT /* xid=152786 */                                           |
| mysql-bin.000001 | 1003 | Gtid           | 4294967295 |        1068 | SET @@SESSION.GTID_NEXT= 'bb4b388c-e113-11ee-ba3f-fa163e8de5fc:5' |
| mysql-bin.000001 | 1068 | Query          | 4294967295 |        1183 | use `test`; DROP TABLE `t1` /* generated by server */             |
18 rows in set (0.00 sec)

可以看到drop操作前的一个事务的结束位点是1003

（6）将从库恢复到指定的执行位置点

start slave until SQL_BEFORE_GTIDS ='bb4b388c-e113-11ee-ba3f-fa163e8de5fc:5' ;

注：
START SLAVE UNTIL SQL_BEFORE_GTIDS会让从属SQL线程处理所有在gtid_set中首个GTID出现之前发生的事务，而START SLAVE UNTIL SQL_AFTER_GTIDS会让从属线程处理所有事务，包括那些GTID在gtid_set中找到的事务，直到每个线程都遇到一个GTID不属于该集合的事务为止。

同时也可以用

start slave until  MASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = 1003 ;

(7)查看从库主从同步信息和t1表

(root@localhost) [test] show slave status\G
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1183
               Relay_Log_File: hecs-161929-relay-bin.000002
                Relay_Log_Pos: 667
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
          Exec_Master_Log_Pos: 1003
              Relay_Log_Space: 1060
              Until_Condition: SQL_BEFORE_GTIDS
             Master_Server_Id: 4294967295
                  Master_UUID: bb4b388c-e113-11ee-ba3f-fa163e8de5fc
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
           Retrieved_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:4-5
            Executed_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:1-4
  
1 row in set (0.00 sec)

(root@localhost) [test] select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

此时t1 drop前的数据就已经复制过来了，可以导出t1表，导入主库
（8）在从库继续执行start slave ;可恢复正常的主从

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
       Retrieved_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:4-5
            Executed_Gtid_Set: bb4b388c-e113-11ee-ba3f-fa163e8de5fc:1-5
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

(root@localhost) [test] select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

可以看到从库继续同步主库的下一个事务bb4b388c-e113-11ee-ba3f-fa163e8de5fc:5 t1表被drop掉了。