mysql主从延迟设置

延迟复制背景：

     1.1）系统已上线，风控及灾备考虑，单机数据库缺陷风险高；

       A：数据库目前是主从结构，为避免误操作，所以从库是延迟15分钟，如果发现误操作可以在15分钟内暂停从库同步，

并从从库获取相关数据修复被误操作的主库。并在每天凌晨3点非业务高峰时间对mysql-master进行备份。

     1.2）后续数据分析可能连接从库获取数据

       A：数据分析需要确认是否有实时性要求，如果实时性要求目前延迟15分钟可能会影响分析，

该延迟时间可以根据航天方具体要求进行调整或新增一个实时同步从库并允许只读让数据分析工具介入。

1.延迟复制配置

设置Slave上的MASTER TO MASTER_DELAY=N,N为多少秒，设置从数据库延时N秒后，再与主数据库进行数据同步复制。

步骤：

登录从服务器的mysql服务，停止slave节点服务，设置时间，再开启slave节点服务，指令如下：

    stop slave;
    CHANGE MASTER TO MASTER_DELAY = 600；
    start slave;
    show slave status \G;

 如果结果显示如图，则表示配置成功
 

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

CHANGE MASTER TO MASTER_DELAY = N；

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

具体操作：

登陆到Slave数据库服务器

mysql>stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 600；

mysql>start slave;

mysql>show slave status \G;

查看SQL_Delay的值为600，表示设置成功。

注释：

SQL_Delay：一个非负整数，表示秒数，Slave滞后多少秒于master。

SQL_Remaining_Delay：当 Slave_SQL_Running_State 等待，直到MASTER_DELAY秒后，Master执行的事件，

此字段包含一个整数，表示有多少秒左右的延迟。在其他时候，这个字段是NULL。

参考：Mysql官网(http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html)

 

 

实验：

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的恢复。