MySQL multi-master synchronization Load Balancing

Http://blog.sina.com.cn/s/blog_56115979010144dy.html

Solve the Problem of duplicate IDs of multiple write servers for Master auto-Growth [Master/Master Synchronization]

Two MySQL servers whose IP addresses are:
A: 192.168.0.97
B: 192.168.0.98
All databases are test_3306
First, I paste my. ini to the 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
The log-slave-Updates parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-Skip-errors skips errors and continues copying
For other parameters, see http://www.tbqu.com/post/15.html

There are some differences between multi-master-slave mutual backup and master-slave replication. Because multiple master nodes can have write permissions on the serverSelf-growth repetition

Problem (Multiple Primary auto-growth IDs are repeated)
1: First, we use the test table structure of A and B.
2: Drop a, insert the data table test (with an auto-increment ID) on B, and return the insert ID as 1.
3: Stop B and insert the data table test (with an auto-increment ID) on Table A. The returned insert ID is also 1.
4: Then we start both A and B, and duplicate primary key IDs will appear.

Solution:
We only need to ensure that the auto-growth data inserted on the two servers is different.
For example, if a queries an odd ID and B inserts an even ID, you can define an algorithm if there are many servers.

Here we add parameters on a and B to implement parity insertion.

A: Add parameters to my. ini.

auto_increment_offset = 1
auto_increment_increment = 2
In this case, the auto_increment field of a produces the following values: 1, 3, 5, 7 ,... And so on.

B: Add parameters to my. ini.

auto_increment_offset = 2
auto_increment_increment = 2
In this way, the value produced by the auto_increment field of B is: 2, 4, 6, 8 ,... An even number of IDs.

As you can see, your auto_increment field will never be repeated among different servers, so there is no problem with the master-master structure. Of course, you can also use 3, 4, or N servers. You only need to ensure auto_increment_increment = N and then set auto_increment_offset to the appropriate initial value, our MySQL can have dozens of Master servers at the same time, without repeated self-growth IDs.

Here we are talking about two MySQL servers. You can also expand to multiple servers. The implementation method is similar.
A-> B-> C-> D->
Such a circular backup structure is formed. Remember to design the self-growth ID (primary key). Otherwise, errors may occur.

-----------------------------------------

Assume that there are three MySQL servers whose IP addresses are:
192.168.1.8
192.168.1.88
192.168.1.188
Find the my. ini file in the installation directory of 192.168.1.8, and add the following at the end of the file:
Server-id = 1
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.188
Master-user = root
# Enter the root password of 192.168.1.188 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all

Find the my. ini file in the installation directory of 192.168.1.88, and add the following at the end of the file:
Server-id = 2
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.8
Master-user = root
# Enter the root password of 192.168.1.8 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all
Find the my. ini file in the installation directory of 192.168.1.188, and add the following at the end of the file:
Server-id = 3
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.88
Master-user = root
# Enter the root password of 192.168.1.88 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all

Create the same database asyntest1 on 192.168.1.8, 192.168.1.88, and 192.168.1.188, restart the three databases, and then perform the update operation on any machine to synchronize them to the other two machines, this is a kind of circular synchronization. If there is any modification on 192.168.1.8, it will first be synchronized to the machine at 192.168.1.88, and then the machine at 88 will synchronize the synchronized data to the machine at 192.168.1.188. Similarly, if there is any update on 192.168.1.88, it will be synchronized to the host of 192.168.1.188 and then synchronized.
To Machines 192.168.1.8, and any updates on machines 192.168.1.188 will be synchronized to machines 192.168.1.8, and then to machines 192.168.1.88. This principle can be used to solve the problem of mutual synchronization between multiple machines.

If any problem occurs, run the Telnet command on each server to check the connectivity of port 3306 on other servers. In addition, check the firewall settings and anti-virus software configurations of each machine. These software can be suspended for testing.

Http://blog.sina.com.cn/s/blog_56115979010144dy.html

Solve the Problem of duplicate IDs of multiple write servers for Master auto-Growth [Master/Master Synchronization]

Two MySQL servers whose IP addresses are:
A: 192.168.0.97
B: 192.168.0.98
All databases are test_3306
First, I paste my. ini to the 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
The log-slave-Updates parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-Skip-errors skips errors and continues copying
For other parameters, see http://www.tbqu.com/post/15.html

There are some differences between multi-master-slave mutual backup and master-slave replication. Because multiple master nodes can have write permissions on the serverSelf-growth repetition

Problem (Multiple Primary auto-growth IDs are repeated)
1: First, we use the test table structure of A and B.
2: Drop a, insert the data table test (with an auto-increment ID) on B, and return the insert ID as 1.
3: Stop B and insert the data table test (with an auto-increment ID) on Table A. The returned insert ID is also 1.
4: Then we start both A and B, and duplicate primary key IDs will appear.

Solution:
We only need to ensure that the auto-growth data inserted on the two servers is different.
For example, if a queries an odd ID and B inserts an even ID, you can define an algorithm if there are many servers.

Here we add parameters on a and B to implement parity insertion.

A: Add parameters to my. ini.

auto_increment_offset = 1
auto_increment_increment = 2
In this case, the auto_increment field of a produces the following values: 1, 3, 5, 7 ,... And so on.

B: Add parameters to my. ini.

auto_increment_offset = 2
auto_increment_increment = 2
In this way, the value produced by the auto_increment field of B is: 2, 4, 6, 8 ,... An even number of IDs.

As you can see, your auto_increment field will never be repeated among different servers, so there is no problem with the master-master structure. Of course, you can also use 3, 4, or N servers. You only need to ensure auto_increment_increment = N and then set auto_increment_offset to the appropriate initial value, our MySQL can have dozens of Master servers at the same time, without repeated self-growth IDs.

Here we are talking about two MySQL servers. You can also expand to multiple servers. The implementation method is similar.
A-> B-> C-> D->
Such a circular backup structure is formed. Remember to design the self-growth ID (primary key). Otherwise, errors may occur.

-----------------------------------------

Assume that there are three MySQL servers whose IP addresses are:
192.168.1.8
192.168.1.88
192.168.1.188
Find the my. ini file in the installation directory of 192.168.1.8, and add the following at the end of the file:
Server-id = 1
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.188
Master-user = root
# Enter the root password of 192.168.1.188 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all

Find the my. ini file in the installation directory of 192.168.1.88, and add the following at the end of the file:
Server-id = 2
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.8
Master-user = root
# Enter the root password of 192.168.1.8 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all
Find the my. ini file in the installation directory of 192.168.1.188, and add the following at the end of the file:
Server-id = 3
Log-bin
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
BINLOG-do-DB = asyntest1
# The following sentence is very important. Only with it can the data synchronized from the previous machine be synchronized to the next machine.
Log-slave-Updates
Master-host = 192.168.1.88
Master-user = root
# Enter the root password of 192.168.1.88 here
Master-Password = XXXXX
# Asyntest1 is the name of the database I used for testing. You need to replace it with your database name.
Replicate-do-DB = asyntest1
Master-connect-retry = 10
# Ignore when an error occurs. If this is not added, the synchronization process will terminate if any error occurs.
Slave-Skip-errors = all

Create the same database asyntest1 on 192.168.1.8, 192.168.1.88, and 192.168.1.188, restart the three databases, and then perform the update operation on any machine to synchronize them to the other two machines, this is a kind of circular synchronization. If there is any modification on 192.168.1.8, it will first be synchronized to the machine at 192.168.1.88, and then the machine at 88 will synchronize the synchronized data to the machine at 192.168.1.188. Similarly, if there is any update on 192.168.1.88, it will be synchronized to the host of 192.168.1.188 and then synchronized.
To Machines 192.168.1.8, and any updates on machines 192.168.1.188 will be synchronized to machines 192.168.1.8, and then to machines 192.168.1.88. This principle can be used to solve the problem of mutual synchronization between multiple machines.

If any problem occurs, run the Telnet command on each server to check the connectivity of port 3306 on other servers. In addition, check the firewall settings and anti-virus software configurations of each machine. These software can be suspended for testing.