MySQL delayed replication database

Preface:

I believe that many people have encountered the problem of MySQL master-slave replication latency. The solution is basically what you can find, so today I will talk about how to delay MySQL replication. For a newly established team, this may save your project.

To put it simply, delayed replication is to set a fixed delay time, for example, one hour, so that the slave database lags behind the master database for one hour.

Delayed replication

The existence is reasonable. Delayed replication can be used to solve the following three problems:

1. It is used to quickly restore data after Database misoperations. For example, if a table is mistakenly operated, the data in the slave database does not change during the delay period, and the data in the slave database can be quickly restored.

2. It is used for latency testing. For example, if you do a good job of database read/write splitting and use slave database as the read database, you want to know what will happen when data is delayed. Then you can use this feature to simulate latency.

3. for requirements such as query of old data, for example, you often need to view the values of the previous table or field one day, you may need to view the backup after recovery, if there is a delay from the database, for example, if the delay is one week, you can solve such similar needs.

Of course, not all teams have such requirements.

Set delayed replication

Delayed replication configuration, implemented by setting the master to MASTER_DELAY parameter on the Slave:

Change master to MASTER_DELAY = N;

The number of seconds for N. The statement is set to synchronize and replicate data with the primary database after the delay of N seconds from the database.

Specific operations:

Log on to the Slave database server

Mysql> stop slave;

Mysql> change master to MASTER_DELAY = 600;

Mysql> start slave;

Mysql> show slave status \ G;

The value of SQL _Delay is 600, indicating that the setting is successful.

Command note:

SQL _Delay: a non-negative integer, indicating the number of seconds. The number of seconds Slave lags behind the master.

SQL _Remaining_Delay: The event executed by the Master when Slave_ SQL _Running_State is waiting until MASTER_DELAY seconds later,

This field contains an integer indicating the latency of about seconds. In other cases, this field is 0.