MYSQL multi-master and multi-slave

MYSQL two masters and two slaves

1. Prepare 4 mysql service port numbers respectively 3307, 3308, 3309, 3310
2. Modify the my.cnf file of the four mysql
I choose 3307, 3309 two databases as the master server, 3308 is the slave node of 3307, 3310 is
Add binary log and service id to my.cnf of 3309 slave node 3307

log-bin=mysql-bin
server-id=3307
auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1

auto_increment_increment=2 //The server's primary key self-increment step size
auto_increment_offset=1 //

Insert picture description here


Add service id to my.cnf of 3308 starting from 1

server-id=3308

Add binary log and service id to my.cnf of 3309

Insert picture description here


auto_increment_increment=2 //The server's primary key self-increment step
auto_increment_offset=2 //start from 1 to
add service id to my.cnf of 3310

server-id=3310

3. Start 4 mysql
startup commands

./mysqld_safe &

Open the connection client of 4 servers

Client No. 1 enters the main server 3307 to view the status and offset of the main server

./mysql -uroot -p123456 -P3307 -h127.0.0.1
 show master status
Insert picture description here


Create a user account for communication between slave and master

GRANT replication slave ON *.* TO 'liu'@'%' IDENTIFIED BY '123456';

Reset offset

reset master
Insert picture description here


Client No. 3 enters the main server 3309 to view the status and offset of the main server

./mysql -uroot -p123456 -P3309 -h127.0.0.1
 show master status
Insert picture description here


Create a user account for communication between slave and master

GRANT replication slave ON *.* TO 'liu'@'%' IDENTIFIED BY '123456';

Reset offset

reset master
Insert picture description here

4. Enter the slave database to set the slave node.
Set 3307 as the 3309 slave node in the No. 1 database

change master to  master_host="47.110.157.82",master_port=3309,
master_user="liu",master_password="123456",
master_log_file="mysql-bin.000001",master_log_pos=154;

Start the slave node and see if the master and slave have established a connection

start slave;
show slave status \G;
Insert picture description here

Set 3309 as a 3307 slave node in database No. 3

change master to  master_host="47.110.157.82",master_port=3307,
master_user="liu",master_password="123456",
master_log_file="mysql-bin.000001",master_log_pos=154;

Start the slave node and see if the master and slave have established a connection

start slave;
show slave status \G;
Insert picture description here

Client No. 2 sets the slave node

./mysql -uroot -p123456 -P3308 -h127.0.0.1

Set up the slave node

change master to  master_host="47.110.157.82",master_port=3307,
master_user="liu",master_password="123456",
master_log_file="mysql-bin.000001",master_log_pos=154;

Start the slave node and see if the master and slave have established a connection

start slave;
show slave status \G;
Insert picture description here

Client No. 4 sets the slave node

./mysql -uroot -p123456 -P3310 -h127.0.0.1

Set up the slave node

change master to  master_host="47.110.157.82",master_port=3309,
master_user="liu",master_password="123456",
master_log_file="mysql-bin.000001",master_log_pos=154;

Start slave node

start slave;
show slave status \G;
Insert picture description here


5. Test results

Insert picture description here


Conclusion: 3307, 3309 self-increment step size is 2, 3307 starts from 1, 3309 starts from 2, but each new primary key is self-incremented on the existing primary key.