Shulou(Shulou.com)05/31 Report--

How to implement a deferred replication library in MySQL? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

The role of MySQL delayed replication Library

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

1. It is used to recover data quickly after misoperation of the database.

For example, if someone mismanipulates the table, then the data from the database has not changed during the delay time, and can be quickly recovered with the data from the database.

Stop the service and import the tables from the slave database directly into the master database. The tables cannot be restored by adding binlog,binglog.

two。 Used for delay testing.

For example, if you separate the read and write of the database and use the slave library as the read library, then you want to know what happens when the data is delayed. Then you can use this feature to simulate latency as well.

When the pressure is low, the delay will be very small, what will it be like when the delay is relatively large? What is the master-slave delay of 5 minutes?

3. Requirements such as queries for old data

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

If there is a delay from the library, such as an one-week delay, then similar requirements can be addressed. Of course, not all teams have such a need.

Set delayed replication

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

CHANGE MASTER TO MASTER_DELAY = N

N is the number of seconds, this statement sets the slave database to delay N seconds, and then replicate data synchronously 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, which indicates that the setting is successful.

Command comments:

SQL_Delay: a non-negative integer indicating the number of seconds that Slave lags behind master.

SQL_Remaining_Delay: the event that Master executes when Slave_SQL_Running_State waits until MASTER_DELAY seconds later

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

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.