How to configure master-slave replication of MySQL database?

How to configure master-slave replication of MySQL database?

Master-slave replication of MySQL database is a common data backup and high availability solution. By configuring master-slave replication, you can synchronize data from one MySQL server (master server) to another (slave server), thereby improving database availability and performance.

The following will introduce how to configure master-slave replication in a MySQL database and provide corresponding code examples.

1. Make sure the MySQL server is installed and started

First, make sure the MySQL server has been installed in your system and the MySQL service has been started.

2. Configure the main server

Configure the following on the main server:

2.1 Modify the configuration file of the main server

Find the main server The server's configuration file (generally located in /etc/mysql/my.cnf or /etc/my.cnf), and add the following configuration:

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

Among them, server-id is used to identify the unique ID of the server, in Uniqueness needs to be maintained in master-slave replication. log_bin specifies the file name of the binary log, and binlog_do_db specifies the name of the database that needs to be synchronized (multiple can be set, separated by commas).

2.2 Restart the main server

After saving and closing the configuration file, restart the main server to make the configuration take effect.

$ sudo service mysql restart

3. Configure the slave server

Configure the following on the slave server:

3.1 Modify the configuration file of the slave server

Found From the server's configuration file (generally located at /etc/mysql/my.cnf or /etc/my.cnf), and add the following configuration:

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

where server-id is also used to identify the unique ID of the server . relay-log specifies the relay log file name of the slave server. Log_slave_updates is set to 1 to indicate that the slave server also records binary logs. Read_only is set to 1 to indicate that the slave server is read-only.

3.2 Restart the slave server

After saving and closing the configuration file, restart the slave server to make the configuration take effect.

$ sudo service mysql restart

4. Connect to the master server and create a replication user

First, connect to the master server using the MySQL client and create a user for replication:

$ mysql -u root -p
mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'your_password';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;

Among them, 'replication' is the user name of the replication user, and 'your_password' is the password of the replication user. Please modify it according to the actual situation.

Finally, execute the SHOW MASTER STATUS command and record the values ​​of File and Position, which will be used on the slave server later.

5. Connect to the slave server and configure master-slave replication

Use the MySQL client to connect to the slave server and configure master-slave replication:

$ mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='master_log_file', MASTER_LOG_POS=master_log_pos;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS G;

where , 'master_host' is the IP address or host name of the master server, 'replication' is the user name of the replication user, 'your_password' is the password of the replication user, 'master_log_file' and 'master_log_pos' are the File and The value of Position.

Finally, execute the SHOW SLAVE STATUS G command and check whether the values ​​of Slave_IO_Running and Slave_SQL_Running are 'Yes' to ensure that master-slave replication has been successfully configured.

After the configuration is completed, the data on the master server will be synchronized to the slave server, realizing master-slave replication.

Summary

This article introduces how to configure master-slave replication in a MySQL database and provides corresponding code examples. Through master-slave replication, data backup and high availability can be achieved, and the availability and performance of the database can be improved. The configuration process is relatively simple, but in actual applications, you still need to pay attention to issues such as configuration consistency and fault handling to ensure the stability and reliability of master-slave replication.

The above is the detailed content of How to configure master-slave replication of MySQL database?. For more information, please follow other related articles on the PHP Chinese website!