Shulou(Shulou.com)06/01 Report--

In the past, the editor has introduced to you the configuration method of dual-computer hot standby in Mysql database for reference. In this section, we focus on the important links and places that need to be paid attention to.

One: introduction

Mysql version: 5.7.20

The first master server ip:192.168.71.139

The second master server ip:192.168.71.141

Two: configuration

The first primary server 192.168.71.139

1: modify the / etc/mysql/my.cnf file. Note that the # here is a comment and should not be written into the configuration file.

Server-id = 141 # server id, which cannot be repeated. It is recommended to use the last three digits of ip.

Log-bin = mysql-bin

Binlog-ignore-db = mysql,information_schema # ignores libraries written to binlog logs

Auto-increment-increment = 2 # field change increment value

Auto-increment-offset = 1 # initial field ID is 1

Slave-skip-errors = all # ignores all replication errors

2: log in to mysql and create an account that allows other servers to copy

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

3: use show master status to query status

The second primary server 192.168.71.139

1: modify the / etc/mysql/my.cnf file, where server-id = 139, the rest remains the same.

Use show master status to query status

At this point, you need to restart the mysql of both servers

Execute the synchronization statement at 192.168.71.141

The master_log_file value comes from the server. The File field after show master status is executed.

The master_log_file value comes from the server. The Position field 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 after show master status is executed.

Execute the synchronization statement at 192.168.71.139

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

The master_log_file value comes from the 141server. The Position field after show master status 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

This is the end of the configuration. Restart mysql, log in to mysql, use show slave status\ G to check the configuration status, and find that Slave_IO cannot be started with the following error

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

Through the log, it is found that the uuids of master and slave is duplicated, because the two servers are cloned, so the / var/lib/mysql/auto.cnf needs to be modified.

Here I only modify the last letter, because there are too many changes, my mysql can not be started. After the modification is complete, restart mysql, log in to mysql and execute show slave status\ G, as shown below

Three: testing

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 server 139

Insert into tab139 values (1)

Execute the following sql on the 141server

Insert into tab141 values (2)

The result is as follows:

If there is anything you don't understand, please feel free to discuss it in the comments area below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.