Mysql multi-master unidirectional -- & gt; circular synchronization _ MySQL

Mysql multi-master unidirectional -- amp; gt; ring synchronization bitsCN.com

Principle:
MySQL Replication is a master-slave synchronization solution for multiple MySQL databases. it features asynchronization and is widely used in various scenarios that require higher performance and reliability for MySQL. Another technique corresponding to this is the synchronized MySQL Cluster, but because of its complexity, there are fewer users.

MySQL officially provides the scenario for using Replication:

Replication principles

Mysql Replication is an asynchronous Replication process, from a MySQL node (called the Master node) to another MySQL node (called the Slave ). The entire replication process between the Master and Slave is mainly completed by three threads. two threads (SQL thread and I/O thread) are on the Slave side, another thread (I/O thread) is on the Master side.

To implement MySQL Replication, you must first enable the Binary Log on the Master end, the whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself.

It seems that the Replication principle of MySQL is very simple. here is a summary:
* Only one master can be set for each slave.
* After the master executes the SQL statement, it records the binary log file (bin-log ).
* Connect to the primary database, obtain the binlog from the primary database, store it in the local relay-log, and execute the SQL statement from the last remembered position. If an error occurs, the synchronization is stopped.

From the perspective of these Replication principles, we can draw these inferences:
* Databases between the master and slave databases are not synchronized in real time. even if the network connection is normal, the master and slave databases may be instantly inconsistent.
* If the master-slave network is disconnected, the slave will be synchronized in batches after the network is normal.
* If you modify the slave data, it is very dangerous to stop synchronization because an error occurs during the execution of the main bin-log. Therefore, you should be very careful when modifying the data from the top.
* One derivative configuration is dual-master, which is a master-slave configuration. it works well as long as the modifications made by both parties do not conflict with each other.
* If you need multiple masters, you can use the ring configuration so that any node modifications can be synchronized to all nodes.

Master/slave settings

Because the principle is relatively simple, Replication is supported from MySQL 3 and can work on all platforms. multiple MySQL nodes can even be different platforms, versions, and local networks. The Replication configuration includes the user and my. ini (my. cnf in linux.

First, create a user for slave on the master MySQL node:

Grant replication slave, replication client on *. * TO 'Slave '@ '192. 168.1.10' identified by 'Slave ';

In fact, to support master-slave dynamic synchronization or manual switching, this user is generally created on all master-slave nodes. Then the MySQL configuration is completed. you need to modify the my. cnf or my. ini file. Add the following content in the section "mysqld:

Server-id = 1
Auto-increment = 2
Auto-increment-offset = 1
Log-bin
Binlog-do-db = mstest
Binlog_format = mixed

Master-host = 192.168.1.62
Master-user = slave
Master-password = slave
Replicate-do-db = mstest

In the above two settings, the first one is set as the master, and the last one is set as the slave. That is to say, you can add one segment on two MySQL nodes. Binlog-do-db and replicate-do-db are the databases that need to be synchronized, auto-increment and auto-increment-offset are set to support dual-Master (refer to the next section). You can also leave them unspecified when only the master and slave nodes are used.

Dual-master settings

From the original theory, MySQL also supports dual-master settings, that is, two MySQL nodes are mutually active and standby. However, in theory, dual-master can work well as long as data does not conflict with each other, however, in actual situations, data conflicts may occur. for example, before synchronization is completed, both parties modify the same record. Therefore, in practice, it is best not to allow both sides to modify at the same time. That is, the logic still works in the master-slave mode. However, the dual-master setting still makes sense, because after this is done, switching between the master and slave will become very simple. After a fault occurs, if a dual-master is configured, it is easy to directly switch between the master and slave databases.
When setting the dual master node, you only need to copy the preceding settings to write the configuration files of the two MySQL nodes, but you need to modify the corresponding server-id, auto-increment-offset and master-host. Auto-increment-offset is used to ensure that the dual-master node does not conflict with the id when adding a table at the same time. therefore, set auto-increment-offset to different values on the two nodes. Also, do not forget to create users for each other on both nodes. Server load balancer at the application layer this article only introduces the Repilication configuration of MySQL. as shown in the figure above, with Replication, a server load balancer at the application layer (or middleware) is also required, in this way, we can maximize the advantages of MySQL Replication, which will be discussed later.

========================================================== ========================================================== ================================
Ring

Solve the problem of duplicate primary auto-growth IDs of multiple write servers

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
Masters-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
Masters-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 server, so the problem of self-increasing duplication is designed.

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 first be synchronized to the host of 192.168.1.188 and then to the host of 192.168.1.188. However, there is any update on the host of 192.168.1.188, first, it will be synchronized to the host at 192.168.1.8, and then synchronized to the host at 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.

BitsCN.com