Detailed explanation of MySQL delayed replication library method

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

You may also be interested in:
  • MySQL master-slave synchronization, read-write separation configuration steps
  • Two solutions to MySQL master-slave database asynchrony
  • The principle and configuration method of MySQL master-slave replication (more detailed)
  • Interpretation of MySQL master-slave configuration and its principle analysis (Master-Slave)
  • Mysql master-slave synchronization backup strategy sharing
  • Analysis and solution of MYSQL master-slave asynchrony delay principle
  • Detailed steps to implement MySQL hot backup under Linux system (MySQL master-slave replication)
  • Mysql master-slave replication (master-slave) actual operation case
  • Detailed explanation of MySQL master-slave replication steps and solutions to common errors
  • An example of solving MySQL master-slave synchronous replication error
  • The implementation principle of Mysql master-slave synchronization
  • Introduction to the heartbeat function of MySQL master-slave replication configuration
  • Configuration example of specifying master-slave synchronization of MySQL database server under Linux
  • Solution to the problem of MySQL master-slave database being out of sync
  • Master-slave synchronization backup steps of MySQL database under Windows environment (one-way synchronization)
  • MYSQL master-slave database synchronization backup configuration method
  • Sharing of the master-slave configuration method of MySQL database
  • Detailed explanation of reducing MySQL master-slave data synchronization delay

<<:  VMware's detailed tutorial on how to create a Linux virtual machine and set up a virtual machine network

>>:  VMware vSphere6.0 server virtualization deployment and installation diagram (detailed steps)

Recommend

If I change a property randomly in Vue data, will the view be updated?

Interviewer: Have you read the source code of Vue...

Detailed explanation of Mysql communication protocol

1.Mysql connection method To understand the MySQL...

JavaScript to achieve fireworks effects (object-oriented)

This article shares the specific code for JavaScr...

Two ways to completely delete users under Linux

Linux Operation Experimental environment: Centos7...

Add a startup method to Linux (service/script)

Configuration file that needs to be loaded when t...

How to choose the right MySQL datetime type to store your time

When building a database and writing a program, i...

Implementation of CSS equal division of parent container (perfect thirds)

The width of the parent container is fixed. In or...

Detailed explanation of JSONObject usage

JSONObject is just a data structure, which can be...

Install Mininet from source code on Ubuntu 16.04

Mininet Mininet is a lightweight software defined...

Detailed explanation of SSH password-free login configuration under Linux

Assume there are two Linux servers A and B, and w...

How to write the introduction content of the About page of the website

All websites, whether official, e-commerce, socia...

Detailed explanation of MySQL combined query

Using UNION Most SQL queries consist of a single ...

The difference between Input's size and maxlength attributes

I recently used the input size and maxlength attri...

View the frequently used SQL statements in MySQL (detailed explanation)

#mysql -uroot -p Enter password mysql> show fu...