How to achieve database high availability and disaster recovery in MySQL?

How to achieve database high availability and disaster recovery in MySQL?

With the rapid development of the Internet, databases have become an indispensable part of modern applications. In terms of high concurrency, high availability, and disaster recovery, the stability and reliability of the database have become more important. MySQL is one of the most commonly used and mature relational databases. This article will introduce how to achieve high availability and disaster recovery of the database in MySQL.

1. Master-Slave Replication
Master-Slave Replication is a common solution in MySQL to achieve database high availability. Through master-slave replication, we can copy the data of the master database to one or more slave databases, thereby achieving data backup and separation of reading and writing.

In MySQL, configuring master-slave replication is mainly divided into the following steps:

1. On the master server, find the my.cnf configuration file (usually located in /etc /mysql or /etc/my.cnf), add the following configuration:

   [mysqld]
   log_bin=mysql-bin
   server_id=1

   log_binThe parameter is used to enable the binary log to record all update operations on the main server. The server_id parameter is used to identify the unique master server.

2. Restart the MySQL service to make the configuration take effect.

   sudo service mysql restart

3. Create a new user for accessing the main server from the server. Open the MySQL command line and execute the following command:

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

   where replication is the customized user name and password is the password. Please set it according to the actual situation.

4. On the master server, execute the following command to view the status of the master server, and record the values ​​of File and Position (will be used when configuring the slave server):

   SHOW MASTER STATUS;

5. On the slave server, find the my.cnf configuration file and add the following configuration:

   [mysqld]
   server_id=2

   server_idThe parameter is used to identify the unique slave server.

6. Restart the MySQL service to make the configuration take effect.

7. On the slave server, open the MySQL command line and execute the following command to configure the master-slave replication:

   CHANGE MASTER TO MASTER_HOST='主服务器IP地址', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='从步骤4中的File值', MASTER_LOG_POS=从步骤4中的Position值;

   Among them, The master server IP address is required Replace with the actual primary server IP address, replication and password need to be replaced with the user and password created in step 3, from the File value in step 4 and are replaced from the Position value in step 4 with the File and Position values ​​of the main server status respectively.

8. Start the replication process from the slave server:

   START SLAVE;

9. View the replication status from the slave server:

   SHOW SLAVE STATUS G;

   If configured successfully, you can Confirm whether the status is normal by observing the two fields Slave_IO_Running and Slave_SQL_Running.

2. Master-slave switching (Failover)
Master-slave replication can realize data backup and read-write separation, but when the master server goes down, you need to manually switch to the slave server. . In order to achieve automatic switching, we can combine other tools, such as MHA (MySQL High Availability) or ProxySQL to perform master-slave switching.

MHA is a tool specifically used for MySQL high availability and disaster recovery configuration. We can implement master-slave switching through the following steps:

1. Install the MHA toolkit:

   sudo apt-get install mha4mysql-node

2. Create an MHA configuration file, such as/etc/mha/app1.cnf, the content is as follows:

   [server default]
   manager_workdir=/var/log/masterha/app1
   manager_log=/var/log/masterha/app1/manager.log
   
   [server1]
   hostname=主服务器IP地址
   candidate_master=1
   recovery_user=replication
   recovery_password=password
   
   [server2]
   hostname=从服务器IP地址
   candidate_master=1
   recovery_user=replication
   recovery_password=password

   Among them, Master server IP address and Slave server IP address need to be replaced with Actual IP address.

3. Execute the following command to run the MHA management tool on the slave server:

   masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf

   This command will monitor the master server and perform master operations when the master server goes down. Switch from.

3. Data Backup and Recovery
In addition to master-slave replication and master-slave switching, regular data backup is also an important way to ensure database availability and disaster recovery. In MySQL, we can use the mysqldump command for data backup and recovery.

1. Backup database:

   mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

   Among them, user name and database name need to be replaced with the actual user name and database name , Backup file.sql is the path and file name of the backup file.

2. Restore the database:

   mysql -u 用户名 -p 数据库名 < 备份文件.sql

   Among them, user name and database name need to be replaced with the actual user name and database name , Backup file.sql is the backup file that needs to be restored.

The above is a brief introduction on how to achieve high availability and disaster recovery of the database in MySQL. Through master-slave replication, master-slave switching and data backup, we can ensure the stability and availability of the database to better meet the needs of modern applications. Of course, MySQL also has some other high availability and disaster recovery solutions, and readers can choose and configure them according to actual needs.

Reference materials:

1. MySQL Replication - Setting up Replication: https://dev.mysql.com/doc/refman/8.0/en/replication.html
2. MHA: https://code.google.com/archive/p/mysql-master-ha/wikis/GettingStarted.wiki

The above is the detailed content of How to achieve database high availability and disaster recovery in MySQL?. For more information, please follow other related articles on the PHP Chinese website!