MySQL Master-Slave synchronization

Resolve multiple Write Server Master self-growth ID duplication problem

Two MySQL servers, their IP addresses are:
a:192.168.0.97
b:192.168.0.98
The databases are all test_3306.
First, I'll post the My.ini for two servers.
A:my.ini

server-id=2
master-host=192.168.0.98
master-user=mydb
master-password=123
master-port=3306
master-connect-retry=1
replicate-do-db=tbqu
log-bin=
log-slave-updates
binlog-ignore-db=mysql
slave-skip-errors=all

B:my.ini

server-id=1
master-host=192.168.0.97
master-user=mydb
master-password=123
master-port=3306
master-connect-retry=1
replicate-do-db=tbqu
log-bin=
log-slave-updates
binlog-ignore-db=mysql
slave-skip-errors=all

Here, the configuration file and the master-slave configuration method are basically the same
Log-slave-updates This parameter must be added, otherwise it will not give updated records into the binary file
Slave-skip-errors is skipping the error and continuing with the copy operation
For other parameters, please refer to http://www.tbqu.com/post/15.html

There are some differences between multi-master and master-slave replication, because the server can have write permissions in multiple hosts, so design to self-growing duplication issues

Problems that occur (multiple master self-growth ID duplicates)
1: First we pass a A, a, B, test table structure
2: Drop A, insert operation on data table test (existing self-growth ID) on B, return insert ID 1
3: After stopping B, the insert operation is performed on a data table test (existing self-growing ID) on a, and the insertion ID returned is also 1
4: The primary key ID is duplicated when we start A, b

Workaround:
We just need to make sure that the self-growth data inserted on both servers is different.
such as: A Chachi number id,b interpolation even ID, of course, if the server is more, you can define the algorithm, as long as the difference can be

Here we add parameters to the A, B to implement the odd-even insertion

Adding parameters to the A:my.ini

auto_increment_offset = 1
auto_increment_increment = 2
So the Auto_increment field of a has the following values: 1, 3, 5, 7, ... Wait for the odd ID.

Adding parameters to the B:my.ini

auto_increment_offset = 2
auto_increment_increment = 2
The value of the Auto_increment field of B is: 2, 4, 6, 8, ... Equal even ID.

As you can see, your auto_increment field will never be duplicated between different servers, so there is no problem with the master-master structure. Of course, you can also use 3, 4, or n servers, just ensure that auto_increment_increment = N and then set the Auto_increment_offset as the appropriate initial value, then, Our MySQL can have dozens of master servers at the same time without the duplication of self-growth IDs.

Here we are talking about 2 MySQL servers, you can also extend to more than one, the implementation method is similar
C-> D->a, A-B
Such a circular backup structure is formed, and finally you can remember that the self-growth ID (primary key) should be designed well, otherwise it will be wrong.

———————————————————————————————————————— –

Assuming there are three MySQL servers, their IP addresses are:
192.168.1.8
192.168.1.88
192.168.1.188
Locate the My.ini file in the 192.168.1.8 installation directory, and at the end of the file, add:
Server-id=1
Log-bin
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Binlog-do-db=asyntest1
#下面这句话很重要, only with it, the data synced from the previous machine can be synchronized to the next machine.
Log-slave-updates
master-host=192.168.1.188
Master-user=root
#在此处填入192.168.1.188 root Password
Master-password=xxxxx
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略, if you do not add this, any errors occur, the synchronization process terminates
Slave-skip-errors=all

Locate the My.ini file in the 192.168.1.88 installation directory, and at the end of the file, add:
server-id=2
Log-bin
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Binlog-do-db=asyntest1
#下面这句话很重要, only with it, the data synced from the previous machine can be synchronized to the next machine.
Log-slave-updates
master-host=192.168.1.8
Master-user=root
#在此处填入192.168.1.8 root Password
Master-password=xxxxx
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略, if you do not add this, any errors occur, the synchronization process terminates
Slave-skip-errors=all
Locate the My.ini file in the 192.168.1.188 installation directory, and at the end of the file, add:
Server-id=3
Log-bin
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Binlog-do-db=asyntest1
#下面这句话很重要, only with it, the data synced from the previous machine can be synchronized to the next machine.
Log-slave-updates
master-host=192.168.1.88
Master-user=root
#在此处填入192.168.1.88 root Password
Master-password=xxxxx
#asyntest1是我用来试验的数据库名称, it's time to change your database name.
Replicate-do-db=asyntest1
master-connect-retry=10
#出现错误后忽略, if you do not add this, any errors occur, the synchronization process terminates
Slave-skip-errors=all

In 192.168.1.8, 192.168.1.88, 192.168.1.188 on the establishment of the exact same database Asyntest1, restart the three databases, and then on any one machine on the update operation, will be synchronized to the other two machines, this is a ring synchronization, Any changes in the 192.168.1.8 will first be synchronized to the 192.168.1.88 machine, and 88 of the machines will synchronize the synchronized data to the 192.168.1.188 machine. Similarly, if there is any update on the 192.168.1.88, it will first be synced to the 192.168.1.188 machine before synchronizing To the 192.168.1.8 machine, and any updates on the 192.168.1.188 machine, first synchronized to the 192.168.1.8 machine, and then to the 192.168.1.88 machine. This principle can be used to solve the synchronization problem of any number of machines.

If a problem occurs, first check the connectivity of other machine 3306 ports with the command line on each server by using the Telnet command. Also, check the firewall settings of each machine and the configuration of the antivirus software. These software can be suspended for experimentation.

MySQL Master-Slave synchronization