MySQL5.7 利用延时复制来恢复

前几天做的实验，基于时间点的恢复以及基于position的恢复有同样的问题，就是需要手动一个一个的应用所有binlog（除非  自己开发一个程序自动应用binlog） ，并且恢复到全备状态需要比较长的时间，并且有可能要停止服务一段时间。

如果有一个延时复制的备库，在备库执行有害语句之前就发现问题的话，那么基于时间点恢复就更快更容易了，而且是不需要停止主库的服务，只需要要slave库恢复完后主从切换就可以了。

下面做一个实验：

1.开启slave复制延时

Mysql （需5.6以上版本）延迟复制配置，通过设置Slave上的MASTER TO MASTER_DELAY参数实现：
CHANGE MASTER TO MASTER_DELAY = N；

N为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制

设置slave延时600秒

    mysql> stop slave;
    Query OK, 0 rows affected (0.03 sec)
     
    mysql> change master to master_delay=600;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.61.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql_bin.000037
              Read_Master_Log_Pos: 154
                   Relay_Log_File: slave_relay_bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql_bin.000037
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    。。。
              Exec_Master_Log_Pos: 154
                  Relay_Log_Space: 527
    。。。
                 Master_Server_Id: 10000
                      Master_UUID: 8d8746fb-2cc6-11e8-b1b6-000c295c63e0
                 Master_Info_File: /u01/mysql/master.info
                        SQL_Delay: 600
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
    。。。
    1 row in set (0.00 sec)

2.在master上做一些变更操作：

    mysql> insert into t1 values(1,'A',11,1);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> insert into t1 values(2,'B',22,2);
    Query OK, 1 row affected (0.07 sec)
     
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> select * from t1;
    +------+------+------+----+
    | c1   | c2   | c5   | c6 |
    +------+------+------+----+
    |    1 | A    |   11 |  1 |
    |    2 | B    |   22 |  2 |
    +------+------+------+----+
    2 rows in set (0.00 sec)

3.在master执行一个有害的sql,误删除表T1

    mysql> drop table t1;
    Query OK, 0 rows affected (0.01 sec)

4.在slave查看同步的状态：

    mysql> mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.61.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql_bin.000037
              Read_Master_Log_Pos: 864
                   Relay_Log_File: slave_relay_bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql_bin.000037
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    。。。
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 154
                  Relay_Log_Space: 1237
                  Until_Condition: None
       。。。
                 Master_Server_Id: 10000
                      Master_UUID: 8d8746fb-2cc6-11e8-b1b6-000c295c63e0
                 Master_Info_File: /u01/mysql/master.info
                        SQL_Delay: 600
              SQL_Remaining_Delay: 522
          Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
               Master_Retry_Count: 86400
                  。。。
    1 row in set (0.00 sec)

Slave_SQL_Running_State正在等待MASTER_DELAY所设置的时间到达后再同步。

    mysql> use l5m;
    Database changed
    mysql> select * from t1;
    Empty set (0.10 sec)

变更的数据自然还没有被同步，暂时停掉slave的同步。

    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)

此时master数据库是不受影响的，如果在master做些操作，看最后能不能slave仅仅是跳过了drop操作，能恢复对t2的操作。

    mysql> insert into t2 values(1,'abc',22,33);
    Query OK, 1 row affected (0.00 sec)
     
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> select * from t2;
    +------+------+------+----+
    | c1   | c2   | c3   | c4 |
    +------+------+------+----+
    |    1 | abc  |   22 | 33 |
    +------+------+------+----+
    1 row in set (0.00 sec)

5.查看master库drop操作的position:

    [root@qht131 mysql]# mysqlbinlog mysql_bin.000037 | grep -n 'DROP'
    73:DROP TABLE `t1` /* generated by server */
    [root@qht131 mysql]# mysqlbinlog mysql_bin.000037 | sed -n '60,80p'
    nm3YWhMQJwAAMQAAAF0CAAAAAGwAAAAAAAEAA2w1bQACdDEABAMPAwMCHgAHA2kBiA==
    nm3YWh4QJwAAMgAAAI8CAAAAAGwAAAAAAAEAAgAE//ACAAAAAUIWAAAAAgAAAMVZ2os=
    '/*!*/;
    # at 655
    #180419 18:21:18 server id 10000  end_log_pos 686 CRC32 0x19ac01c9      Xid = 33
    COMMIT/*!*/;
    # at 686
    #180419 18:22:11 server id 10000  end_log_pos 751 CRC32 0x04591b0c      Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 751
    #180419 18:22:11 server id 10000  end_log_pos 864 CRC32 0xc587394c      Query  thread_id=4      exec_time=0     error_code=0
    use `l5m`/*!*/;
    SET TIMESTAMP=1524133331/*!*/;
    DROP TABLE `t1` /* generated by server */
    /*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过mysqlbinlog查看，drop操作发生在mysql_bin.000037的#751，#751之前的事务是#686

6.在slave上使用start slave until来同步drop之前的事务

    mysql> start slave until master_log_file='mysql_bin.000037',master_log_pos=686;
    Query OK, 0 rows affected, 1 warning (0.01 sec)
     
    mysql> use l5m
    Database changed
    mysql> select * from t1;
    +------+------+------+----+
    | c1   | c2   | c5   | c6 |
    +------+------+------+----+
    |    1 | A    |   11 |  1 |
    |    2 | B    |   22 |  2 |
    +------+------+------+----+
    2 rows in set (0.00 sec)

    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.61.131
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql_bin.000037
              Read_Master_Log_Pos: 864
                   Relay_Log_File: slave_relay_bin.000002
                    Relay_Log_Pos: 852
            Relay_Master_Log_File: mysql_bin.000037
                 Slave_IO_Running: Yes
                Slave_SQL_Running: No
     。。。
              Exec_Master_Log_Pos: 686
                  Relay_Log_Space: 1610
                  Until_Condition: Master
                   Until_Log_File: mysql_bin.000037
                    Until_Log_Pos: 686
               Master_SSL_Allowed: No
              。。。
                 Master_Server_Id: 10000
                      Master_UUID: 8d8746fb-2cc6-11e8-b1b6-000c295c63e0
                 Master_Info_File: /u01/mysql/master.info
                        SQL_Delay: 600
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State:
               Master_Retry_Count: 86400
              。。。

7.设置sql_slave_skip_counter=1来调过有害的语句，之后重新打开slave复制

    mysql> set global sql_slave_skip_counter=1;
    Query OK, 0 rows affected (0.01 sec)

    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql>  change master to master_delay=0;
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> start slave;
    Query OK, 0 rows affected (0.02 sec)

重新打开slave之前需要先将master_delay设置为0来立即同步所有的操作（当然如果不至一条有害的sql需要过滤，则还不能立即同步后面所有的binlog）。

slave库的t1表drop之前的数据都完好无损，并且drop之后的操作也都同步了过来。

    mysql> use l5m;
    Database changed
    mysql> select * from t1;
    +------+------+------+----+
    | c1   | c2   | c5   | c6 |
    +------+------+------+----+
    |    1 | A    |   11 |  1 |
    |    2 | B    |   22 |  2 |
    +------+------+------+----+
    2 rows in set (0.00 sec)
     
    mysql> select * from t2;
    +------+------+------+----+
    | c1   | c2   | c3   | c4 |
    +------+------+------+----+
    |    1 | abc  |   22 | 33 |
    +------+------+------+----+
    1 row in set (0.00 sec)
     

8.最后需要做的就是主从的切换操作，这样就实现了不停机操作对有害sql的恢复。