MySql data distribution: how to implement master-slave replication mechanism

MySql is a popular relational database management system. Data replication is widely used to achieve data backup, load balancing and high availability. In MySql, you can use the master-slave replication mechanism to achieve data distribution, allowing multiple systems to access the same data. This article will introduce how to implement the master-slave replication mechanism in MySql.

1. The basic concept of the master-slave replication mechanism

The master-slave replication mechanism is an important feature of the MySql database. It can help achieve data distribution and backup. By transferring the data on the main server Synchronously replicate to one or more slave servers to achieve data distribution. In MySql, the master server is responsible for processing all data update operations, while the slave server achieves data access by copying the data of the master server.

In the master-slave replication mechanism of MySql, the master server writes its own data change records to its binary log file (binary log), and the slave server obtains the information on the master server by reading the binary log file. data update operation. In this way, the slave server can synchronize the data on the master server in real time, making it easier and more efficient for multiple systems to access data at the same time.

2. Configuration method of master-slave replication mechanism

1. Prerequisites

Before configuring the master-slave replication mechanism, you need to ensure that the master server and slave server MySql database has been installed and the version is the same. In addition, you need to ensure that the master and slave servers can access each other, and the master server has enabled the binary log function.

2. Configure the main server

Step 1: Create a user on the main server for replication from the server and authorize it to access all databases on the main server. For example, we can create a user named replicator and give it access to all databases.

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'replicator'@'%' WITH GRANT OPTION;

Step 2: Enable the binary log function of the main server. Add the following parameters in the my.cnf configuration file of the master server:

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

The server-id parameter represents the unique identifier of the master server. Each master server should have a different identifier. Here we set it to 1 and set the prefix of the binary log files to mysql-bin.

Step 3: Restart the main server to make the changes in the configuration file take effect.

3. Configure the slave server

Step 1: Create a user on the slave server for access to the master server and authorize it to access all databases that need to be replicated. For example, we can create a user named backup and give it access to all databases.

CREATE USER 'backup'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'backup'@'%' WITH GRANT OPTION;

Step 2: Add the following parameters to the my.cnf configuration file of the slave server:

[mysqld]
server-id=2
relay-log=mysql-relay
log_slave_updates=1
read_only=1

Unlike the master server, the slave server does not need to enable the binary log function. The value of the server-id parameter needs to be different from the main server, here we set it to 2. The relay-log parameter specifies the storage location of the data change records received from the server. Here we name it mysql-relay. The log_slave_updates parameter indicates that the slave server also needs to write the data change records it generates to its own binary log file. The read_only parameter indicates that the slave server can only be used for read operations and cannot write operations to the database.

Step 3: Restart the slave server to make the changes in the configuration file take effect.

4. Start replication

Step 1: Execute the following command on the slave server to establish a connection with the master server.

CHANGE MASTER TO MASTER_HOST='主服务器IP地址', MASTER_USER='replicator', 
MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', 
MASTER_LOG_POS=107;

Among them, the MASTER_HOST parameter is the IP address of the master server, and the MASTER_USER and MASTER_PASSWORD parameters are the username and password of the user created on the master server for replication from the server. The MASTER_PORT parameter indicates the port number of the main server, here we set it to 3306. The MASTER_LOG_FILE and MASTER_LOG_POS parameters specify which binary log file position to start copying data from the server and maintain synchronization. Here we assume that we want to start copying from the 107th byte of the first binary log file (mysql-bin.000001) data.

Step 2: Start the replication function of the slave server.

START SLAVE;

With this command, the slave server starts copying data from the master server. You can check the replication status through the "SHOW SLAVE STATUSG" command.

5. Test Replication

In order to test whether replication is working properly, we can insert some data on the master server and see whether the data has been successfully replicated on the slave server .

Execute the following command on the main server to insert a record into the data table.

INSERT INTO test_table (name, age) VALUES ('Tom', 25);

Execute the following command on the slave server to check whether the data in the test_table table has been synchronized.

SELECT * FROM test_table;

If the inserted data can be seen on the slave server, it means that the master-slave replication mechanism has been successfully configured and started.

3. Summary

MySql’s master-slave replication mechanism is an important means to achieve data distribution and synchronization, and is widely used in actual projects. This article introduces how to configure the master-slave replication mechanism in MySql, including configuring the master server and slave servers, and establishing a replication channel between the master and slave servers. Through the above steps, you can easily implement the master-slave replication function in MySql.

The above is the detailed content of MySql data distribution: how to implement master-slave replication mechanism. For more information, please follow other related articles on the PHP Chinese website!