MySQL primary master replication (dual master replication) configuration steps

Previously we introduced is the master-slave replication, here to introduce a double master replication, the following are expected to two of the main server data automatically copied, you can refer to this article.

The MySQL master replication structure differs from the master-slave replication structure. In the primary master replication structure, any one of the two server's data inventory changes will be synchronized to another server, so that the two servers are mainly from each other, and can provide services to the outside.
With the master-slave copy of the previous section, it is easy for the primary master to replicate.

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 of the automatic growth of the database, the two can be set to the server automatically increase the value of 2, so their starting point must be different, so as to avoid the two server data synchronization when the primary key conflict
Replicate-do-db Specify the 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 whole structure, this case uses two servers, so the value is set to 2

Second, synchronous data

This article is an experiment with test to export a copy of the Test.sql file from a 254 server to a 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 [email protected]:/tmp
Third, mutual authorized users (in a server authorized 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 disclosure of bin-log information

On 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 |
+------------------+----------+--------------+--------------------------+
On 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 a server (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 B server (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;
Five. Execute the following command on both servers

mysql> start slave;

Vi. Viewing status

Mysql> Show Slave Status\g
The 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 instructions have been configured successfully

Let's see if we can do the experiment and test the synchronization.

Ps:
In the process of testing, I also encountered a number of problems mainly two machines communicating with each other.
Please note that it is important to keep the MySQL port of both servers open to each other, otherwise it will not be synchronized successfully.

MySQL primary master replication (dual master replication) configuration steps