MySQL master copy (dual-master replication) configuration steps

The MySQL master copy structure differs from the master-slave replication structure. In the primary replication structure, any changes to the data inventory on either of the two servers are synchronized to another server, so that the two servers are focused on each other and can be serviced externally.
With the master-slave copy of the previous section, the primary master copy is easy.

First, modify the configuration file

Server A (192.168.1.254) is configured as follows

Log-bin = Mysql-bin
Server-id = 1
Expire-logs-days = 100
REPLICATE-DO-DB = Test
binlog-ignore-db = MySQL
Binlog-ignore-db = Information_schema
Auto-increment-increment = 2
Auto-increment-offset = 1
Server B (192.168.1.252) configuration

Log-bin = Mysql-bin
Server-id = 2
Expire-logs-days = 100
REPLICATE-DO-DB = Test
binlog-ignore-db = MySQL
Binlog-ignore-db = Information_schema
Auto-increment-increment = 2
Auto-increment-offset = 2
Both servers are restarted

Mysql> Service mysqld Restart
Note: Two are only server-id different and auto-increment-offset different
Auto-increment-offset is used to set the starting point for automatic growth in the database, the two servers have set an automatic growth value of 2, so they must start a different starting point, so as to avoid two server data synchronization when the primary key conflict
Replicate-do-db Specify a synchronized database, we only synchronize the test database between the two servers
Another: The value of auto-increment-increment should be set to the total number of servers in the entire structure, this case uses two servers, so the value is set to 2

Second, synchronizing data

This article is an experiment with test to export the Test.sql file from 254 server to 252 server
Lock the table before backing up data to ensure data consistency

Mysql> FLUSH TABLES with READ LOCK;

# mysqldump-uroot-p123456 test>/tmp/test.sql;

Mysql> UNLOCK TABLES;

Scp/tmp/test.sql root@192.168.1.252:/tmp
Third, mutually authorized user (in a server authorize a user to allow B access, and vice versa)

On Server A (192.168.1.254)

Mysql> GRANT REPLICATION SLAVE on *.* to ' Mysync ' @ ' 192.168.1.252 ' identified by PASSWORD ' 123456 ';
mysql> flush Privileges;
On Server B (192.168.1.252)

Mysql> GRANT REPLICATION SLAVE on *.* to ' Mysync ' @ ' 192.168.1.254 ' identified by PASSWORD ' 123456 ';
mysql> flush Privileges;
Iv. Mutual information of Bin-log

In Server A (192.168.1.254)

Mysql> Show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+--------------------------+
|      mysql-bin.000006 |      106 | | Mysql,information_schema |
+------------------+----------+--------------+--------------------------+
In Server A (192.168.1.252)

Mysql> Show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+--------------------------+
|      mysql-bin.000008 |      192 | | Mysql,information_schema |
+------------------+----------+--------------+--------------------------+
Execute on Server A (192.168.1.254)

mysql> Change Master to master_host= ' 192.168.1.252 ', master_user= ' Mysync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000008 ', master_log_pos=192;
Execute on Server B (192.168.1.252)

mysql> Change Master to master_host= ' 192.168.1.254 ', master_user= ' Mysync ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000006 ', master_log_pos=106;
The following commands are executed on both servers

mysql> start slave;

VI. View status

Mysql> Show Slave Status\g
A server (192.168.1.254) status is as follows:

Slave_io_state:waiting for Master to send event
master_host:192.168.1.252
Master_user:mysync
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000008
read_master_log_pos:192
relay_log_file:mysqld-relay-bin.000009
relay_log_pos:337
relay_master_log_file:mysql-bin.000008
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:test
The b server (192.168.1.252) status is as follows:

Slave_io_state:waiting for Master to send event
master_host:192.168.1.254
Master_user:mysync
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000006
read_master_log_pos:106
relay_log_file:mysqld-relay-bin.000014
relay_log_pos:251
relay_master_log_file:mysql-bin.000006
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:test
When you see two yes, that is:
Slave_io_running:yes
Slave_sql_running:yes
The description has been configured successfully

Next look can do the experiment, test whether the synchronization

Ps:
In the process of testing, I also encountered a number of problems, mainly two machines to communicate with each other
Please note that the MySQL ports of both servers must be kept open to each other, otherwise they will not be synchronized successfully.