How to use MySQL's replication function to achieve data backup and disaster recovery

How to use MySQL’s replication function to achieve data backup and disaster recovery

Introduction:
MySQL is a commonly used relational database management system. In practical applications, in order to ensure data security and high Availability often requires data backup and disaster recovery. The replication function of MySQL can realize data backup and disaster recovery. This article will introduce how to use the replication function of MySQL to realize data backup and disaster recovery, and comes with code examples.

1. Introduction to the replication function
The replication function of MySQL is implemented through the binary log (Binary Log). Simply put, the replication function records all write operations of the primary database in the binary log, and then replays these operations from the secondary database based on the information in the binary log, thereby ensuring that the data of the secondary database is consistent with the primary database.

2. Set up the main database

1. Generate a replication account
   First, generate an account for replication in the main database. You can use the following statement to create an account named repl in the main database and grant replication permissions to the repl account.

CREATE USER 'repl'@'%' IDENTIFIED BY 'password'; -- '%'代表允许来自任意IP地址访问
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

2. Enable binary log
   Enable binary log in the configuration file (my.cnf) of the main database. Add the following configuration to the my.cnf file:

log-bin=mysql-bin

Then restart the MySQL service.

3. Get the main database status
   Use the following statement to get the status information of the main database for subsequent use.

SHOW MASTER STATUS;

Record the values ​​of the File and Position fields, which will be used in the database.

3. Set the slave database

1. Set the replication configuration of the slave database
   Configure the slave database configuration file (my.cnf) as follows:

server-id=2  -- 从数据库的唯一标识，不同数据库必须有不同的值

Then restart the MySQL service.

2. Start replication
   Use the following statement in the slave database to start replication:

CHANGE MASTER TO
  MASTER_HOST='主库IP', -- 主数据库的IP地址
  MASTER_PORT=主库端口,  -- 主数据库的端口
  MASTER_USER='repl',
  MASTER_PASSWORD='repl账号的密码',
  MASTER_LOG_FILE='主库的File字段值',
  MASTER_LOG_POS=主库的Position字段值;

START SLAVE;

Use the SHOW SLAVE STATUS; command to view the replication status of the slave database and ensure Slave_IO_Running and Slave_SQL_Running fields are both Yes.

4. Data backup and disaster recovery test

1. Data backup
   Create a test table in the main database and insert some data.

CREATE DATABASE test;
USE test;
CREATE TABLE tb_test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));

INSERT INTO tb_test (name) VALUES ('张三'),('李四'),('王五');

SELECT * FROM tb_test;

Then, check in the slave database whether the data in the master database is automatically synchronized.

2. Disaster recovery test
   To simulate the main database downtime, you can simulate it by stopping the MySQL service of the main database.

After the master database stops, the slave database will continue to work and can read and write to the slave database.

After completion, restart the MySQL service of the main database. When the main database returns to normal, the slave database will automatically synchronize the data of the main database.

5. Summary
Through the replication function of MySQL, we can easily achieve data backup and disaster recovery. The process of setting up the master and slave databases is relatively simple and requires only a few configurations and commands. Using the replication function, you can ensure data synchronization between the master and slave databases, and ensure that the slave database can continue to work when the master database goes down, improving system availability.

Code example:

The above is the detailed content of How to use MySQL's replication function to achieve data backup and disaster recovery. For more information, please follow other related articles on the PHP Chinese website!