How to use master-slave replication in MySQL to achieve data backup and recovery?

How to use master-slave replication in MySQL to achieve data backup and recovery?

Data backup and recovery is a very important part of database management. MySQL provides the Master-Slave Replication function, which can realize automatic backup and recovery of data. This article will introduce in detail how to configure and use the master-slave replication function in MySQL.

1. Configure the master server (Master)

1. In the my.cnf configuration file, add the following configuration:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-do-db = your_database_name

Among them, server-id is the server ID, which can be set to any positive integer; log-bin is the name prefix of the binary log file; binlog-do-db specifies the name of the database that needs to be synchronized.

2. Restart the MySQL service.

sudo service mysql restart

3. Create an account for master-slave replication and grant replication permissions.

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;

4. View the main server status.

SHOW MASTER STATUS;

Record the values ​​of File and Position for later use.

2. Configure the slave server (Slave)

1. In the my.cnf configuration file, add the following configuration:

[mysqld]
server-id = 2

Among them, server-id is the server ID and can be set to any positive integer.

2. Restart the MySQL service.

sudo service mysql restart

3. Connect to the slave server and execute the following command:

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='master_log_file', MASTER_LOG_POS=master_log_pos;

Replace master_ip with the IP address of the master server and replication_user with the replication account of the master server. Replace your_password with the password of the replication account, master_log_file with the File value of the master server, and master_log_pos with the Position value of the master server.

4. Start replication from the server.

START SLAVE;

5. View slave server status.

SHOW SLAVE STATUSG

If the values ​​of Slave_IO_Running and Slave_SQL_Running are both "Yes", it means that the master-slave replication configuration is successful.

3. Data backup and recovery

1. Data backup

When the data on the main server changes, MySQL will record these changes to the binary log In the file, the slave server will synchronize data by reading the binary log file of the master server.

2. Data Recovery

If the master server fails, it needs to be switched to the slave server to provide services. At this point, you only need to upgrade the slave server to the master server.

STOP SLAVE;
RESET SLAVE;  -- 清除从服务器的主从配置
RESET MASTER; -- 清除主服务器的主从配置

Then modify the configuration of the slave server, set its server-id to 1, and restart the MySQL service.

In this way, the slave server is upgraded to the new master server. After the original master server is repaired, it can be configured as a slave server again.

So far, we have learned how to use master-slave replication in MySQL to implement data backup and recovery. By properly configuring the master-slave server, you can ensure data security and availability, reduce the risk of data loss, and improve system reliability and efficiency.

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