How to achieve data replication and synchronization in MySQL?

MySQL is currently one of the most popular relational database management systems. It supports data replication and synchronization to achieve high availability and data protection. This article will introduce how to implement data replication and synchronization in MySQL and provide some code examples.

1. Basic principles of MySQL data replication
MySQL data replication is achieved by copying data from one database to another database. It is based on the master-slave replication model, in which one database acts as the master server, responsible for receiving and processing all write operations; while other databases act as slave servers, responsible for requesting and obtaining data updates from the master server. Data replication is accomplished by transferring the binary log on the master server to the slave server.

1. Configuring the main server
   First, we need to enable the binary log function on the main server. Add the following content in the my.cnf configuration file:

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

Where, server -id is the unique identifier of the master server, and log-bin specifies the name of the binary log file.

Then, restart the MySQL service to make the configuration take effect.

2. Configuring the slave server
   Next, we need to configure replication on the slave server. Add the following content in the my.cnf configuration file:

[mysqld]
server-id=2
relay-log=mysql-relay-bin
log-slave- updates=1

Among them, server-id is the unique identifier of the slave server, relay-log specifies the name of the relay log file, and log-slave-updates indicates whether the slave server records binary logs.

Then, restart the MySQL service to make the configuration take effect.

3. Establish a replication channel
   Create a dedicated user for replication on the main server and authorize it:

CREATE USER 'replication'@'% ' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON . TO 'replication'@'%';

Then, obtain the binary log file name and Location:

SHOW MASTER STATUS;

Execute the following command on the slave server to establish a replication channel:

CHANGE MASTER TO MASTER_HOST='Main server IP', MASTER_USER='replication ', MASTER_PASSWORD='password', MASTER_LOG_FILE='Binary log file name', MASTER_LOG_POS=position;

Then, start the replication function from the server:

START SLAVE;

2. How to implement MySQL data synchronization
MySQL data synchronization refers to keeping the data between multiple databases consistent to ensure the real-time and consistency of the data. The following introduces several common data synchronization implementation methods.

1. Data synchronization based on binlog
   The binary log of MySQL records all write operations of the database. We can use the binary log to achieve data synchronization. The specific steps are as follows:

(1) Enable the binary log function on the main server and configure the binlog format to ROW:

binlog_format=ROW

(2) Configure replication on the secondary server, use the CHANGE MASTER command to establish a replication channel, and start replication.

(3) Create a trigger on the slave server to synchronize data when the data on the master server changes.

DELIMITER $$
CREATE TRIGGER sync_data AFTER INSERT ON table name FOR EACH ROW
BEGIN
INSERT INTO table name VALUES (NEW.Field 1, NEW.Field 2, ...) ;
END$$
DELIMITER ;

When the data on the master server changes, the trigger will perform the corresponding INSERT operation on the slave server to achieve data synchronization.

2. Data synchronization based on primary key auto-increment column
   When synchronizing data between multiple databases, primary key conflicts are often encountered. One solution is to use primary key auto-increment columns to ensure that primary keys are not duplicated between different databases.

The specific steps are as follows:

(1) When creating a table in each database, set the primary key field to an auto-increment column.

(2) When inserting data into each database, there is no need to specify the value of the primary key field, the database will automatically generate a unique primary key value for it.

(3) Synchronize the data in each database to other databases through scheduled tasks or triggers.

Code example:

CREATE TABLE table name (
id INT AUTO_INCREMENT PRIMARY KEY,
Field 1 VARCHAR(50),
Field 2 VARCHAR(50)
);

INSERT INTO table name (field 1, field 2) VALUES ('data 1', 'data 2');

3. Tool-based data synchronization
   In addition to data synchronization methods based on binlog and primary key auto-increment columns, there are also some third-party tools that can achieve MySQL data synchronization, such as Maxwell and Debezium. These tools can parse the binary log of the database and convert it into a readable format to achieve data synchronization.

There are no code examples yet. Please refer to the official documentation and usage examples of the tool.

Summary:
This article introduces methods to achieve data replication and synchronization in MySQL, as well as related code examples. Through data replication and synchronization, we can achieve high availability of the database and data protection, and improve the stability and reliability of the system. At the same time, choosing appropriate methods and tools based on actual needs can better meet business needs.

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