Analysis of Difficulties in Hot Standby of MySQL Database

I have previously introduced to you the configuration method of MySQL database dual-machine hot standby. Friends who are interested can refer to it. In this section, we focus on summarizing and analyzing the important links and areas that need attention.

I. Introduction

mysql version: 5.7.20

The first main server ip:192.168.71.139

The second main server ip:192.168.71.141

2. Configuration

The first primary server 192.168.71.139

1: Modify the /etc/mysql/my.cnf file. Note that # here is a comment. Do not write it into the configuration file.

server-id = 141 #Server id, cannot be repeated, it is recommended to use the last three digits of the IP.
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema #Ignore the library that writes binlog logs
auto-increment-increment = 2 #Field change increment value
auto-increment-offset = 1 #The initial field ID is 1
slave-skip-errors = all #Ignore all replication errors

2: Log in to MySQL and create an account that allows other servers to replicate

GRANT REPLICATION SLAVE ON *.* to 'mysql account'@'%' identified by 'password';

3: Use show master status to query the status

The second primary server is 192.168.71.139

1: Modify the /etc/mysql/my.cnf file, where server-id = 139, and the rest remain unchanged.

Use show master status to query the status

At this point, you need to restart mysql on both servers

Execute synchronization statements at 192.168.71.141

The master_log_file value comes from the 139 server, the File field after executing show master status

 The master_log_file value comes from the 139 server, the Position field after executing show master status

change master to master_host='192.168.71.139',master_user='master2',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;

Execute synchronization statements at 192.168.71.139

The master_log_file value comes from the 141 server, the File field after executing show master status

change master to master_host='192.168.71.141',master_user='master1',master_password='123456',master_log_file='mysql-bin.000002', master_log_pos=154;

This is the end of the configuration. Restart MySQL, log in to MySQL, and use show slave status\G to check the configuration status. It is found that Slave_IO cannot be started and the following error occurs.

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

The log shows that the master and slave uuids are duplicated. Because the two servers are cloned, you need to modify /var/lib/mysql/auto.cnf

Here I only changed the last letter, because if I changed too much, I couldn't start mysql. After the modification is completed, restart MySQL, log in to MySQL and execute show slave status\G, as shown below

Three: Test

Execute the following sql on any server

create table tab141(id int primary key);

create table tab139(id int primary key);

Execute the following sql on the 139 server

insert into tab139 values(1);

Execute the following sql on the 141 server

insert into tab141 values(2);

The results are as follows:

If you have any questions, please feel free to discuss in the comment area below.