How to implement asynchronous replication and delayed replication of data in MySQL?

MySQL is a commonly used relational database management system. In practical applications, we often encounter scenarios that require data replication. Data replication can be divided into two forms: synchronous replication and asynchronous replication. Synchronous replication means that the data must be copied to the slave database immediately after the master database writes the data, while asynchronous replication means that the data can be delayed for a certain period of time after the master database writes the data before copying. This article will focus on how to implement asynchronous replication and delayed replication of data in MySQL.

First of all, in order to achieve asynchronous replication and delayed replication, we need to set the binlog format in the MySQL configuration file to ROW mode. Open the MySQL configuration file (usually my.cnf) and add the following configuration:

[mysqld]
binlog_format=ROW

Next, we need to create a master-slave replication environment. First, start the main database MySQL service, create an account for replication, and give appropriate permissions:

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT replication slave ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Then, edit the MySQL configuration file and add the following configuration to the main database:

[mysqld]
server-id=1
log-bin=master

Then, restart the MySQL service of the main database.

Then, start the MySQL service in the slave library, and also create an account for replication and give appropriate permissions:

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
GRANT replication slave ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Edit the MySQL configuration file in the slave library and add the following configuration :

[mysqld]
server-id=2
relay-log=slave

Restart the MySQL service from the database.

Execute the following command in the main library to obtain the status information of the current main library:

SHOW MASTER STATUS;

Record the values ​​of File and Position, which will be used to configure replication in the slave library.

Next, execute the following command in the slave library to configure replication:

CHANGE MASTER TO MASTER_HOST='主库IP地址', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='主库的File值', MASTER_LOG_POS=主库的Position值;

Then, start the replication process:

START SLAVE;

In the slave library, you can use the following command to Check the replication status:

SHOW SLAVE STATUSG;

Next, we will introduce how to implement delayed replication. In MySQL 5.6.6 and above, MySQL provides a parameter for controlling replication delay - slave_pending_jobs_size. This parameter is used to control the number of transactions waiting to be replicated from the database. We can implement delayed replication by setting the value of this parameter appropriately.

Execute the following command in the slave library to set the replication delay to 30 seconds:

SET GLOBAL slave_pending_jobs_size=100000;

Finally, let’s verify whether the replication and delayed replication are successful. We insert a piece of data in the main library, and then check whether the copy is successful in the slave library:

Execute the following command in the main library to insert a piece of data:

USE 数据库名;
INSERT INTO 表名 (字段1, 字段2) VALUES ('value1', 'value2');

Then, in the slave library Execute the following command to check whether the replication is successful:

USE 数据库名;
SELECT * FROM 表名;

If the inserted data is successfully queried from the database, it means that both replication and delayed replication have been successfully implemented.

To sum up, this article introduces how to implement asynchronous replication and delayed replication of data in MySQL. Asynchronous data replication can be achieved by setting the binlog format in the MySQL configuration file to ROW mode and configuring the corresponding parameters and permissions in the master-slave database. Delayed replication of data can be achieved by setting the value of the replication delay parameter slave_pending_jobs_size. These functions can help us better manage and use MySQL database.

The above is the detailed content of How to implement asynchronous replication and delayed replication of data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!