Back-end distributed series: distributed storage-two-way synchronous replication of MySQL Databases

Back-end distributed series: distributed storage-two-way synchronous replication of MySQL Databases

The last article on MySQL replication focuses on the design points and constraints of the two-way synchronous replication architecture.

Problems and restrictions

Database dual-master dual-write and two-way synchronization scenarios, mainly consider data integrity, consistency and avoid conflict. For the same database and table, the change of the same field in the same record will lead to data consistency judgment conflicts and avoid it through business scenario design as much as possible. Primary Key conflicts may occur due to dual-master/Dual-write and synchronous replication. Avoid using the database auto-incrementing primary key scheme. In addition, bidirectional synchronization may cause loop synchronization problems and requires loop control.

As shown in, the replication program also generates binlogs when writing data. It is critical to avoid loop replication to identify and filter the binlogs generated by the replication program.

Native Dual Master Solution

MySQL itself supports dual-master configuration, but it does not solve the Data Consistency conflicts caused by potential primary keys and dual writes. MySQL records the current MySQL server-id in binlog for potential cyclic replication problems of Bidirectional synchronization. Once the value of server-id is available, MySQL can easily determine from which Server a change was originally generated, so it is easy to avoid loop replication. In addition, you can configure the binlog option (-log-slave-update) for logging slave without enabling it. MySQL will not record the changes in the replication process to the binlog, you don't have to worry about the possibility of loop replication.

From the MySQL solution, you can find the starting point. If you can mark the binlog, you can determine which binlogs are generated by replication and filter them out. The MySQL solution is too coupled with the MySQL configuration. It is easy to cause problems in the large-scale deployment of online production systems due to incorrect MySQL configuration.

Custom tag SQL Solution

To decouple the MySQL configuration, you can consider a General Markup SQL solution. To put it simply, a special mark SQL statement is inserted when the database is synchronized for replication to mark the change from the replication program. The Mark SQL will enter the binlog. When the replication program reads data, it filters and judges by identifying the Mark SQL.

Binlog stores SQL statements that affect data changes. These SQL statements constitute a transaction, as shown in:

The green area is the normal transaction generated by the business operation, the red area is the transaction generated by the writing of the replication program, and the blue block is the Mark SQL. Mark SQL: Mark SQL to update a predefined tag table that is different from the business table after the transaction starts and before the transaction ends. Each time the replication program reads binlog content in batches, there may be the following five situations, as shown in:

The batch read range falls into the Green Zone. The start point of the batch read range falls in the green area, and the end point falls in the red area. The start point of the batch read range falls in the red area and the end point falls in the green area. The start and end points of the batch read range are in the green area, but the red area is covered in the middle. The batch read range falls in the red area.

In the preceding 5th cases, a transaction is split into three segments for synchronization. Because there is no transaction header or tail mark in the middle segment, the replica program cannot judge it during reading, resulting in cyclic synchronization, which needs to be avoided. By setting the replication program's batch read range to at least or equal to the write transaction length range, 5th cases are avoided. When the replication program reads binlog events in batches, it filters by marking SQL to avoid loop replication and implement loop control. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4NCjxoMiBpZD0 = "conclusion"> conclusion

This article considers some design points and constraints of Bidirectional synchronous replication in the MySQL dual-master write scenario. Taking native implementation as a reference, the design points of a custom implementation method are presented. Changes in the same database, table, and record of the same field at the same time will inevitably lead to a data consistency conflict. At the replication and synchronization level, the updating of the edge cannot be distinguished. It is usually considered to restore to the consistent state with the last timestamp, but the timestamp actually produces errors. If this type of scenario is rare, it is best to avoid it in the business scenario design as much as possible.