High-availability parallel MySQL data synchronization and distribution

First talk about MySQL data distribution, the most commonly used is replication (replication) technology. There are many middle architectures based on this technique, and the classification is summed up as follows:

1, tree structure (master,backup-master,slaves) This structure is the most common one used in the Web system at present. The entire system has 1 write/update points, that is, master;master-backup and slaves are replication Master from the library; multi-level slave is the reason for data filtering and saving network resources.

     2, ring structure (master-master,slaves)          The         dual-master structure is proposed to improve write throughput by improving the original MySQL master server to provide write/ The updated single point is raised to 2 points, which can be seen from the framework structure.         dual-master need to solve the problem:         a) ID Conflict issue, that is, the allocation of unique ID resources in the database must be a unified place to issue.             Solutions have MySQL comes with auto_increment_increment and auto_ Increment_offset to implement, through the N master to set the Auto_increment_offset=n, each master on the auto_increment_increment with any value in 1~n, But you can't repeat each other. So you can.            id-server:1            auto_increment_increment=1            auto_increment_offset=2            Id-server:2           auto_increment_increment=2            auto_increment_offset=2            for Id-server1, The IDs are all 1,3,5 like this; Id-server2, the IDs are 2,4,6. It seems so beautiful, but it is not. In the case of concurrency, the same tables on the Id-server1 and the table auto_increment values on the id-server2 are different, but need to be synchronized with each other, that is, if the write distribution is uneven, it is possible that the ID in the table will be 1. 3,5,7,8,9,11,etc such a situation, the ID resource in the middle hole too much.             Another workaround is a separate ID generator. There are many ways to implement the ID generator, in order to make maintenance more pure, we use a MySQL table, master-slave this structure as a number of server. So the process becomes the first to go to the ID of the server to obtain the ID, and then write to Dual-master. More than one intermediate link, and this intermediate link is a single point, there is a risk. But the idea is basically this, you can use other services, based on a certain distributed rules to the parallel generator.         B) update conflict, a range of UPDATE statements are executed by both master servers, and this time the data is inconsistent with the original assumptions.         C) Deadlock problem, this occurs in more than N (n>=3) loop master, an abnormally disconnected server binlog, In extreme cases, the data server in the N-1 loop is resumed after repeated execution.      in order to solve the update conflict and deadlock problem in multi-master, my approach is to multi-point insert, but there is only one point update such structure to solve the problems of B and C above.             above has introduced the two commonly used data distribution architectures of replication, Come down and look at the other one, MySQL Cluster. MySQL cluster in a period of time, I think it is quite a chicken, production environment use it almost no. Because it's a huge resource drain across IDC and data storage. The two core resources that     mysql cluster relies on are network bandwidth and memory (share nothing and high consistency). With IDC network bandwidth is also easy to reach hundred trillion or higher, but across the IDC VPN or dedicated line, to achieve such a speed is still a bit difficult. The data is all in memory, which is not noticeable in small data, but it can be a bottleneck for exploding information growth. Although the new version of cluster supports writing data to the hard disk, the actual speed remains to be seen.     replication is basically able to meet the available requirements, but MySQL's Binlog is a single-threaded one, which creates bottlenecks in data synchronization. The general practice is to improve concurrency by writing a plugin, which is plugin both to meet performance and to be highly available. In the following way, by binding some parallel apps to the MySQL Cluster queue to execute, using MySQL Cluster's own high consistency, highly available features, to store messages.     

This can satisfy both data distribution and parallel data synchronization.

High-availability parallel MySQL data synchronization and distribution