Detailed explanation of MySQL delayed replication library method

Simply put, delayed replication is to set a fixed delay time, such as 1 hour, so that the slave database lags behind the master database by one hour.

MySQL delayed replication library function

Existence is reasonable, and delayed replication can be used to solve the following three types of problems:

1. Used to quickly restore data after database misoperation.

For example, if someone operates a table by mistake, the data in the slave database will not change during the delay period, so the data in the slave database can be used for quick recovery.

Stop the service, import the table from the slave database directly into the master database, and fill in the binlog. Binglog cannot restore the table.

2. Used for delay testing

For example, if you have done a good job of database read-write separation and use the slave database as the read database, then you want to know what will happen when the data is delayed. Then you can use this feature to simulate latency as well.

When the pressure is not too great, the delay will be very small. What is it like when the delay is larger? What does a 5 minute master-slave delay look like?

3. Used for querying old data and other needs

For example, if you often need to check the value of a table or field from a certain day ago, you may need to restore the backup and check it.

If there is a delay from the database, such as a delay of one week, then similar requirements can be solved. Of course, not all teams have this need.

Setting delayed replication

Delayed replication configuration is achieved by setting the MASTER TO MASTER_DELAY parameter on the Slave:

CHANGE MASTER TO MASTER_DELAY = N;

N is a number of seconds. This statement sets the slave database to delay N seconds before synchronizing data with the master database.

Specific operations:

Log in to the Slave database server

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

Check that the value of SQL_Delay is 600, indicating that the setting is successful.

Command Notes:

SQL_Delay: A non-negative integer representing the number of seconds that the slave lags behind the master.

SQL_Remaining_Delay: When Slave_SQL_Running_State waits until MASTER_DELAY seconds have passed, the Master executes the event.

This field contains an integer indicating how many seconds the delay is in. At other times, this field is 0.

Well, that’s it for today. Some small bits of knowledge are short and beautiful, while long articles are very useful. However, we actually learn very little. Let’s accumulate this little knowledge. For more information about MySQL delayed replication library methods, please see the following related links