Deferred replication delayed replication

Mysql5.6 supports deferred replication. The default master_delay is 0 seconds.

CHANGE MASTER TO MASTER_DELAY = N

Indicates a delay of N seconds

Principle: the essence of deferred replication is that sql_thread needs to wait for a delay before it can be executed.

Defer replication is applicable in scenarios:

(1) prevent the main database from misoperation. You can stop synchronization before replication synchronization.

(2) as a test, master-slave delay can be achieved without simulating load.

(3) it is used to check the previous data of the database, such as setting the delay to 1 week, so that you can see and compare the data of a week ago without backup and recovery.

(4) reset slave clears the value of SQL_delay and Master_Log_File equivalents, but does not affect replication

Create a deferred replication: slave:root@localhost [testdb] > stop slave;root@localhost [testdb] > change master to master_delay=60;root@localhost [testdb] > start slave;root@localhost [testdb] > show slave status\ G. SQL_Delay: 60-- delay time SQL_Remaining_Delay: 56-- remaining time Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event-- waiting delay. Master:root@localhost [testdb] > delete from T1 where C1 delay 4 slaveberry rootless localhost [testdb] > select * from T1 +-+-+ | C1 | c2 | +-+-+ | 1 | aaa | | 2 | bbb | 3 | ccc | | 4 | ddd | +-+ root@localhost [testdb] > show processlist +-+ -Id | User | Host | db | Command | Time | State | Info | + -+-+-- +-+ | 26 | root | localhost | testdb | Query | 0 | starting | | show processlist | | 27 | system user | | NULL | Connect | 345 | Waiting for master to send event | NULL | | 28 | system user | | NULL | Connect | 10 | Waiting until MASTER_DELAY seconds after master executed event | NULL | +-- + | -+- -+ # View the relay-log log before reaching 60 seconds It is found that it has been written into the relay-lo, indicating that the delay is blocking the SQL_ thread [root@Darren1 data] # mysqlbinlog-vv-- base64-output=decode-rows relaymurbin.000003BEGINBUBEINBUBUBINBUBUBINBUBINBUBINBUBINBUBINBUBINBUBINBUBINBUBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINBINB # at 452 "170409 22:12:27 server id 330622 end_log_pos 5624 CRC32 0x86f7edf4 Table_map: `testdb`.`t1` mapped to number 14" at 502 "170409 22:12:27 server id 330622 end_log_pos 5668 CRC32 0x697c52ed Delete_rows: table id 147flags: STMT_END_F### DELETE FROM `testdb`.`t1` # WHERE### @ 1room3 / * INT meta=0 nullable=0 is_null=0 * / # # @ 2roomccc' / * VARSTRING (30) meta=30 nullable=1 is_null=0 * / root@localhost [testdb] > select * from T1 +-+-+ | C1 | c2 | +-+-+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +-+-+