Difficulties in hot standby and hot standby of Mysql database

Difficulties in hot standby and hot standby of Mysql database

In the past, I have introduced anyone interested in configuring hot standby for Mysql databases, in this section, we will summarize and analyze the important links and precautions.

I. Introduction

Mysql version: 5.7.20

Ip address of the first master server: 192.168.71.139

Ip address of the second master server: 192.168.71.141

Ii. Configuration

First master server 192.168.71.139

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

Server-id = 141 # server id, which cannot be repeated. We recommend that you use the last three ip addresses.
Log-bin = mysql-bin
Binlog-ignore-db = mysql, information_schema # ignore the database written to binlog
Auto-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 on to mysql and create an account that allows replication by other servers.

Grant replication slave on *. * to 'mysql account' @ '%' identified by 'Password ';

3: Use show master status to query the status

 

Second master server 192.168.71.139

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

Use show master status to query the status

In this case, you need to restart the mysql

Execute the synchronization statement at 192.168.71.141.

The master_log_file value is from the 139 server and the File field after the show master status Command is executed.

The master_log_file value is from the 139 server and the Position field after the show master status Command is executed.

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 the synchronization Statement on 192.168.71.139.

The master_log_file value is from the 141 server and the File field after the show master status Command is executed.

The master_log_file value is from the 141 server and the Position field after the show master status Command is executed.

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;

 

At this point, the configuration is complete. Restart mysql, log on to mysql, and useShow slave status \ G check configuration status, foundSlave_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.

Logs show that the uuids of the master and slave are repeated. Because the two servers are cloned, You need to modify them./Var/lib/mysql/auto. cnf

Here, I only modify the last letter. Because there are too many modifications, mysql cannot be started. After modification, restart mysql, log on to mysql, and execute show slave status \ G, as shown in figure

Iii. Test

Execute the following SQL statement on any server:

create table tab141(id int primary key);create table tab139(id int primary key);

Run the following SQL statement on the 139 Server:

insert into tab139 values(1);

Run the following SQL statement on the 141 Server:

insert into tab141 values(2);

The result is as follows:

If you have any questions, please refer to the comment area below.