MySQL 5.6 delayed replication, database recovery after misoperation

MySQL 5.6supports deferred replication, and you can specify a value for latency on the Slave server. The default value is 0 seconds. Use the MASTER_DELAY option to set the N-second delay for CHANGE MASTERTO.

1. The following is an actual demonstration, which is executed normally from the slave node, and the setting delay is 100 seconds.

STOP SLAVE;CHANGE MASTER TO MASTER_DELAY = 100s start SLAVE;SHOW SLAVE STATUS\ G; display SQL_Delay: 100s-delay 100s SQL_Remaining_Delay: NULL

two。 Set to cancel delayed replication

STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0;START SLAVE;SHOW SLAVE STATUS\ G; SQL_Delay: 0-cancel delay SQL_Remaining_Delay: NULL

3. test

The master node creates a library CREATE DATABASE TEST; from the slave node to view the SHOW SLAVE STATUS\ Ghost. SQL_Delay: 100 SQL_Remaining_Delay: 83 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event.

4. Refer to the official website documentation

Https://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html