Back-end Distributed series: Distributed storage-mysql database bidirectional synchronous replication

MySQL replication Problem of the last one, on the two-way synchronous replication architecture design of some design points and constraints.

Problems and constraints

The two-master double-write and bidirectional synchronization scenarios of the database mainly consider data integrity, consistency and conflict avoidance. For the same library, the same table, the same record in the same field of the two changes, will cause data consistency judgment conflict, as far as possible through the business scenario design avoidance. Dual-Master double-write and synchronous replication may cause primary key conflicts, avoiding the use of the database self-increasing class primary key scheme. In addition, bidirectional synchronization may cause the problem of cyclic synchronization, which requires loopback control.

As shown, the Binlog is also generated when the replicator is written, how to identify the binlog generated by the Replicator and filter it out is the key to avoid cyclic replication.

Native Dual Master Scheme

MySQL itself supports dual-master configurations, but does not address data consistency conflicts caused by potential primary keys and double writes. For bidirectional synchronization potential cyclic replication problems, MySQL records the current MySQL Server-id in Binlog. Once you have the value of Server-id, it is easy for MySQL to determine which server the change originated from, so it is easy to avoid a recurring replication. Also, you can configure the Binlog option (–log-slave-update) that does not open the record slave, and MySQL will not record changes in the replication process to Binlog, not to mention the possibility of a recurring replication situation.

From MySQL's own solution can be found in the entry point, that is, if you can mark in the Binlog, there is a way to determine which Binlog is copied and filtered. The MySQL scenario is overly coupled with MySQL configuration and is prone to problems due to MySQL configuration errors in massively deployed online production systems.

Custom Tagged SQL scenarios

In order to decouple from MySQL configuration, consider a common markup SQL scenario. Simply put, insert a special tagged SQL statement to mark this as a change from the replicator when copying the library, and this tag SQL will go into binlog. While the replicator reads, it is filtered by identifying this tag SQL.

Binlog stores SQL statements that affect the data, which make up a segment of the transaction, as shown in:

The Green Zone is a normal transaction generated by the business operation, and the red area is the transaction generated by the replicator write, where the blue block is the tag SQL. Tag SQL updates a predefined markup table that differs from the business table, respectively, before the transaction begins and before the transaction ends. So every time the replicator goes to bulk read Binlog content, there are 5 possible scenarios, as shown in the following:

1. The bulk read range falls entirely within the Green Zone.
2. The bulk read range starts at the Green Zone and ends in the red zone.
3. The bulk read range starts at the red zone and ends in the Green Zone.
4. The bulk read range start and end points are in the green area, but a red area is covered in the middle.
5. The bulk read range falls entirely in the red zone.

As in only the 5th case, a transaction is split into 3 segments to synchronize. In the middle section, because there is no transaction header and tail tag, the replicator will not be able to judge when it reads, causing the loop to synchronize and need to be avoided. The 5th case is avoided by pinning the bulk read range of the replicator to a transaction length range that is at least greater than or equal to the write. When the replicator reads the Binlog log event in bulk, it is filtered by the tag SQL, which avoids the cyclic copying and realizes the loopback control.

Summarize

In this paper, some design points and constraints of bidirectional synchronous replication in MySQL dual-master writing scenario are considered. Based on the native implementation, this paper presents a design point analysis of a custom implementation method. And for the same library same table with the same record field changes, it will inevitably lead to data consistency conflict, in the replication synchronization level can not distinguish which of the update to prevail. It is often considered that the last timestamp will revert to a consistent state, but the time stamp will actually produce errors, and such scenarios are rarely best avoided as much as possible or in the context of the business scenario design.

Reference

[1] MySQL internals Manual. Replication.
[2] MySQL internals Manual. The Binary Log. [3] in355hz. The implementation of the database ACID.
[4] jb51. MySQL handling instructions for Binlog.
[5] Repls. Brief analysis of Innodb_support_xa and Innodb_flush_log_at_trx_commit.
[6] 68IDC. MySQL 5.6 DBA and Developer's Guide.
[7] Csdn. High-performance MySQL master-slave architecture replication principle and configuration detailed.
[8] Agapple. Otter bidirectional loopback control.

Below is my public number "wink", in addition to writing technical articles, as well as products, industry and life thinking, hope to be able to walk with more people on this road to communicate.

Back-end Distributed series: Distributed storage-mysql database bidirectional synchronous replication