Detailed explanation of four solutions for MySQL active-active synchronous replication

The core of real-time data synchronization is to implement it based on logs, which can achieve quasi-real-time data synchronization. The log-based implementation does not require the database itself to bring any additional constraints in design and implementation.

Master-Master Synchronization Solution Based on MySQL Native Replication

This is a common solution. Generally speaking, this architecture is the most convenient when it comes to small and medium-sized projects.

The two nodes can adopt a simple dual-master mode and use a dedicated line connection. After the master_A node fails, the application connection is quickly switched to the master_B node, and vice versa. There are several things to note. In the case of split brain, two nodes write the same data and cause a conflict. At the same time, the auto_increment_increment (auto-increment step) and auto_increment_offset (auto-increment starting value) of the two nodes are set to different values. The purpose is to avoid that when the master node unexpectedly crashes, some binlogs may not be copied to the slave in time for application, which will cause the slave's newly written auto-increment ID to conflict with the original master. Therefore, they are staggered from the beginning; of course, if there is a suitable fault-tolerant mechanism to solve the master-slave auto-increment ID conflict, this can also be avoided. Using the newer data version 5.7+, multi-threaded replication can be used to greatly reduce replication delays. At the same time, another alternative solution that is particularly sensitive to replication delays is semi-sync semi-synchronous replication, which basically has no delay, but there will be a considerable loss in transaction concurrency performance, especially in bidirectional writes, which requires a comprehensive evaluation before deciding.

Based on Galera replication solution

Galera is a multi-master data synchronization and replication mechanism provided by Codership. It can realize data synchronization replication and reading and writing between multiple nodes, and can ensure high availability of database services and data consistency. The high availability solutions based on Galera mainly include MariaDB Galera Cluster and Percona XtraDB Cluster (PXC for short).

At present, PXC is more widely used. It has strict data consistency and is especially suitable for e-commerce applications. However, PXC also has its limitations. If the concurrent transaction volume is large, it is recommended to use InfiniBand network to reduce network latency. Because PXC has write expansion and short board effects, the concurrent efficiency will be greatly reduced. Similar to semi-sync semi-synchronous replication, Gelera can only use three nodes in practice, and the performance and stability problems caused by network jitter are habitual.

Based on Group Replication solution

MGR is a high-availability solution officially launched by MySQL, which provides strong consistency guarantee for database cluster node data through the Paxos protocol. It is based on native replication technology and provided as a plug-in. All nodes between clusters can be written, which solves the write performance of a single cluster. All nodes can read and write, solve the brain split problem caused by network partitions, and improve the reliability of replicated data. However, the reality is still a bit cruel. At present, not many people have tried it. At the same time, it only supports InnoDB tables, and each table must have a primary key for write set conflict detection. The GTID feature must be turned on, and the binary log format must be set to ROW for primary election and write set.

COMMIT may cause failure, similar to the failure scenario of snapshot transaction isolation level. Currently, an MGR cluster supports up to 9 nodes, does not support foreign keys and save point features, cannot perform global constraint detection and partial rollback, and binary logs do not support binlog event checksum

Based on canal solution

For real-time synchronization of databases, Alibaba has a special open source project, Otter, to implement synchronous replication of distributed databases. The core idea is still to perform quasi-real-time synchronous replication by obtaining incremental data logs of the database. Therefore, Otter itself relies on another open source project, Canal, which focuses on obtaining incremental database synchronization log information.

Currently, Otter's focus is to achieve database synchronization and replication between MySQL, which basically uses similar technologies to achieve two-way synchronous database replication between two MySQL databases. It should be noted that this bidirectional itself means that it can go from A->B or from B->A, and at a certain time point it is unidirectional.

Master-slave replication is divided into three steps:

The master records the changes in the binary log (these records are called binary log events and can be viewed with show binlog events).

The slave copies the master's binary log events to its relay log;

The slave redoes the events in the relay log, changing the data to reflect its own.

The canal principle is relatively simple:

Canal simulates the interactive protocol of MySQL slave, pretends to be MySQL slave, and sends dump protocol to MySQL master

MySQL master receives the dump request and starts pushing binary log to slave (canal)
Canal parses binary log objects (originally byte streams)

For more information, please visit https://github.com/alibaba/canal

Summarize

The above are the four solutions for MySQL active-active synchronous replication introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!