MySQL：延迟的主从复制 ( Delayed Replication )-优快云博客

本文介绍了MySQL 5.6中延迟复制的配置方法及测试步骤。通过设置备节点的延迟时间，可以实现数据保护和历史状态查看等功能。文章详细记录了从停止从节点、配置延迟参数到启动从节点的全过程，并验证了延迟复制的效果。

MySQL 5.6 已经支持延迟的流复制，可设置备节点的延迟时间，延迟复制是有意义的，例如防止主节点数据误删，查看数据库历史状态等。

重点：延迟复制实在原有的主从复制基础上、所以先要有主从复制的环境，然后配置延迟复制.

一、语法：

CHANGE MASTER TO MASTER_DELAY = 30;---表示延迟30s

备注：设置备节点延迟的时间，单位秒。

二、配置延迟复制

登录从节点服务器进行设置：

root@localhost:mysql>stop slave;

Query OK, 0 rows affected (0.21 sec)

root@localhost:mysql>CHANGE MASTER TO MASTER_DELAY = 30;

Query OK, 0 rows affected (0.17 sec)
root@localhost:mysql>start slave;

Query OK, 0 rows affected (0.27 sec)

三、查看从节点状态

root@localhost:mysql>select * from slave_relay_log_info\G
*************************** 1. row ***************************

  Number_of_lines: 7

   Relay_log_name: ./db2-relay-bin.000007

    Relay_log_pos: 281

  Master_log_name: bin-log.000041

   Master_log_pos: 361

        Sql_delay: 30
Number_of_workers: 0

               Id: 1

1 row in set (0.00 sec)
root@localhost:mysql>show slave status\G
*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.2.37

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: bin-log.000040

          Read_Master_Log_Pos: 604

               Relay_Log_File: db2-relay-bin.000002

                Relay_Log_Pos: 281

        Relay_Master_Log_File: bin-log.000040

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 604

              Relay_Log_Space: 452

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

                  Master_UUID: 0c130d47-22bb-11e4-aaaa-000c2986ac80

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 30

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

1 row in set (0.00 sec)

四、主节点创建表测试

root@localhost:mysql>create table test_delay(id int4 primary key,

                     create_time datetime default current_timestamp); 

 Query OK, 0 rows affected (0.59 sec)

root@localhost:mysql>insert into test_delay (id) values(1);

 Query OK, 1 row affected (0.07 sec)

五、备节点查询

root@localhost:mysql>select * from test_delay;

Empty set (0.00 sec)

root@localhost:mysql>select * from test_delay;

Empty set (0.00 sec)
这时在备节点上还查不到表。
 
root@localhost:mysql>show slave status\G
*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.2.37

                  Master_User: rep1

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: bin-log.000040

          Read_Master_Log_Pos: 1013

               Relay_Log_File: db2-relay-bin.000002

                Relay_Log_Pos: 281

        Relay_Master_Log_File: bin-log.000040

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: 

           Replicate_Do_Table: 

       Replicate_Ignore_Table: 

      Replicate_Wild_Do_Table: 

  Replicate_Wild_Ignore_Table: 

                   Last_Errno: 0

                   Last_Error: 

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 604

              Relay_Log_Space: 861

              Until_Condition: None

               Until_Log_File: 

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File: 

           Master_SSL_CA_Path: 

              Master_SSL_Cert: 

            Master_SSL_Cipher: 

               Master_SSL_Key: 

        Seconds_Behind_Master: 15

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

                  Master_UUID: 0c130d47-22bb-11e4-aaaa-000c2986ac80

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 30

          SQL_Remaining_Delay: 14

      Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

1 row in set (0.00 sec)
 
root@localhost:mysql>select * from test_delay;

+----+---------------------+
| id | create_time         |

+----+---------------------+
|  1 | 2014-12-12 14:38:47 |

+----+---------------------+

1 row in set (0.00 sec)