MySQL's dual master mode

MySQL Primary master replication configuration

Server1 ip:192.168.0.231
Server2 ip:192.168.0.234


Change the MySQL configuration file for both hosts
Vim/etc/my.cnf
Server1 add
Server-id=1
Log-bin=mysqlmaster-bin.log
auto-increment-increment=2
Auto-increment-offset=1


Server2 add
server-id=2
Log-bin=mysqlmaster-bin.log
auto-increment-increment=2
auto-increment-offset=2

Description: The value of Auto-increment-increment is set to the total number of servers in the entire structure, this experiment is two servers, so the value is 2
Auto-increment-offset is used to set the starting point of automatic growth in the database, because the server has set an autogrow value of 2, so their starting point must be different, in order to avoid a primary key conflict between two server data synchronization

You can also set Replicate-do-db=database-name to specify the databases that need to be synchronized

Restarting the MySQL service makes the configuration effective

Log in to MySQL

Add users required to synchronize data

Server1 on

Mysql>grant REPLICATION SLAVE on * * to ' systop1 ' @ ' 192.168.0.% ' identified by ' systop ';

Server2 on

GRANT REPLICATION SLAVE on * * to ' systop2 ' @ ' 192.168.0.% ' identified by ' systop ';


View the master status of two servers

Server1

SHOW MASTER STATUS;
+------------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------------+----------+--------------+------------------+-------------------+
|      mysqlmaster-bin.000002 |              120 |                  |                   | |



Server2

Show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------------+----------+--------------+------------------+-------------------+
|      mysqlmaster-bin.000001 |              336 |                  |                   | |


Description Binary Log path

Execute on Server2

Change MASTER to master_host= ' 192.168.0.231 ', master_user= ' systop1 ', master_password= ' systop ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=120;


Execute on Server1

Change MASTER to master_host= ' 192.168.0.234 ', master_user= ' systop2 ', master_password= ' systop ', master_log_file= ' Mysqlmaster-bin.000001 ', master_log_pos=336;



Turn on the copy function

Both machines are executed.
Start slave;

View Replication Connection Status
Perform
show slave status \g;
View Slave_io_running:yes
Slave_sql_running:yes
All show yes to start normal

Test: Both servers do data update operations to see if the data is updated

MySQL's dual master mode