How to set up master-slave synchronization operation in MySQL

1. MySQL 8.0 master-slave synchronization

Master-slave synchronization process (principle):

• ##master Record changes to the binary log file (binary log), that is, the file specified by log-bin in the configuration file. These records are called binary log events;

• master Send the binary log file to the slave;

• slave Read the contents of the file through the I/O thread and write it to the relay log;

• slave Execute the events in the relay log to complete the local storage of data.

Things to note when setting up a master-slave server:

• The master-slave server operating system version and number of digits are the same;

• Master and Slave The database versions must be consistent;

• The versions in the Master and Slave databases The data must be consistent;

• Master enables binary logs, and the server_id of Master and Slave must be unique within the LAN.

2. MySQL master-slave setup

2.1 Operations on Master

Modify the master configuration (my.cnf) and add the following Content:

[mysqld]
  log-bin=mysql-bin
  # id 必须唯一！！！
  server-id=1

Restart master:

systemctl restart mysql

Create a user for master-slave synchronization in master:

mysql> # 创建用户
mysql> CREATE USER 'hugh'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> # 授权用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'hugh'@'%';
mysql> # 刷新权限
mysql> FLUSH PRIVILEGES;

Check the main server status:

show master status;

Record the values ​​​​of

File and Position , which will be used later.

2.2 Operations on Slave

Modify the slave configuration (my.cnf) and add the following content:

[mysqld]
  # id 必须唯一！！！
  server-id=2

Restart slave:

systemctl restart mysql

Log in to MySQL and run the following command to set the master node parameters:

mysql> CHANGE MASTER TO
MASTER_HOST='master的IP地址',
MASTER_USER='hugh',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000006',
MASTER_LOG_POS=856;

The last two lines are what we recorded

File and the value of Position.

Check the status of master-slave synchronization:

mysql> show slave status\G;

Check the following information, if all are yes, it means the construction is successful:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

The above is the detailed content of How to set up master-slave synchronization operation in MySQL. For more information, please follow other related articles on the PHP Chinese website!