4 Solutions for MySQL Active-Active Synchronous Replication

MySQL is an open-source relational database management system that is widely used for managing and storing structured data. It is known for its simplicity, ease of use, and scalability, making it a popular choice for various applications ranging from small-scale websites to large-scale enterprises.

For real-time data synchronization, the core requirement is to implement it based on logs, which enables near real-time data synchronization. This does not impose any additional constraints on the design and implementation of the database itself. The purpose of MySQL active-active synchronous replication is to ensure continuous availability and fault tolerance. Here are 4 methods to achive MySQL active-active synchronous replication.

Master-master replication based on the native replication function of MySQL database

It is typically suitable for small to medium-scale deployments.

In this architecture, two nodes can adopt a simple dual-master mode and use a dedicated connection. In the event of a failure in master_A node, the application connections can quickly switch to master_B node, and vice versa.

To avoid split-brain scenarios, where both nodes write conflicting data, it is important to set different values for the auto_increment_increment and auto_increment_offset on the two nodes. This is because that if the master node unexpectedly crashes or becomes unavailable, there is a possibility that some binlog events are not replicated to the slave node. In such cases, there can be conflicts between the generated auto-increment value on the slave and the original value on the master. However, If there is an appropriate fault-tolerant mechanism to resolve master-slave auto-increment ID conflicts, it is possible to avoid using such method. In the updated versions 5.7+ of MySQL, leveraging multi-threaded replication can significantly reduce replication latency. Additionally, another alternative solution that is particularly sensitive to replication latency is semi-sync replication, which offers virtually no delay. However, it mayresult in a decrease in transaction concurrency performance, especially in bidirectional writing. A comprehensive evaluation is required to make a decision.

Solution based on the Galera replication

Galera is a multi-master data synchronization replication mechanism provided by Codership. It enables data synchronous replication as well as reading and writing operations among multiple nodes, ensuring high availability and data consistency in the database. The main high availability solutions based on Galera are MariaDB Galera Cluster and Percona XtraDB Cluster (PXC).

Currently, PXC is more commonly used and provides strict data consistency, making it particularly suitable for e-commerce. However, PXC also has its limitations. In scenarios with high concurrent transaction volumes, it is recommended to use an InfiniBand network to reduce network latency. This is because PXC can suffer from write amplification and the bottleneck effect, resulting in a significant loss of concurrent efficiency. Similar to semi-sync replication, the Galera replication is typically limited to three nodes. In addition, network jitter can cause performance and stability issues.

Solution based on the Group Replication 

MGR (MySQL Group Replication) is a high availability solution officially introduced by MySQL. It provides strong data consistency guarantees among the nodes in a database cluster through the Paxos protocol. MGR is based on native replication technology and is offered as a plugin. It allows all nodes in the cluster to be writable, addressing the performance limitations of a single cluster, resolving the issue of network partition-induced split-brain problems and improving the reliability of replicated data.

However, the reality is somewhat harsh. Currently, there are not many early adopters of MGR. Additionally, it only supports InnoDB tables and requires each table to have a primary key for write set conflict detection. GTID feature must be enabled, and the binary log format must be set to ROW for leader election and write set. 

COMMIT can potentially fail, similar to failure scenarios in snapshot isolation level. Currently, a MGR (MySQL Group Replication) cluster supports a maximum of 9 nodes. It does not support foreign keys and save point features, preventing global constraint checks and partial rollbacks. The binary log does not support binlog event checksum.

Solution based on Canal

For real-time database synchronization, Alibaba has a dedicated open-source project called Otter, which enables synchronous replication of distributed database. The core idea of Otter is still based on capturing the incremental data logs of databases to achieve near real-time synchronous replication. Otter itself relies on another open-source project called Canal, which focuses on capturing incremental database synchronization log information.

Currently, Otter focuses on achieving synchronous replication between MySQL databases. It utilizes similar technologies to achieve bidirectional synchronous replication between two MySQL databases. It is important to note that bidirectional here means that data can be synchronized from A to B or from B to A, but it may be unidirectional at a specific point in time.

The process of master-slave replication can be divided into three steps:

1. The master records the changes into the binary log, which are known as binary log events. These events can be viewed using the "show binlog events" command.

2. The slave copies the binary log events from the master to its own relay log.

3. The slave will sequentially redo the events recorded in the relay log to apply the changes from the master to its own data.

As for the principle of Canal, it is relatively straightforward:

1. Canal simulates the interaction protocol of a MySQL slave by disguising itself as a MySQL slave and sending a dump request to the MySQL master.

2. Upon receiving the dump request, the MySQL master starts pushing binary logs to the slave (which is Canal).

3. Canal then parses the binary log objects (originally in byte stream format) to extract the relevant information.

Backup MySQL databases with Vinchin Backup & Recovery

To better protect data, it is recommended to backup your databases. Vinchin Backup & Recovery delivers powerful functionality to protect your databases in both virtual machines and physical servers. By cooperating well with VM-level backups, dual insurance is given to the users of virtual environments for their key business data and information systems.

Vinchin Backup & Recovery supports protection of Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB installed on both physical and virtual machines with powerful database backup and restore features. It also provides full backup, differential backup, incremental backup and transaction log backup strategies for you to set your own backup plan on demand.

Vinchin Backup & Recovery supports efficient hot backup without affecting the normal operation of databases and it is easy to create a customized database backup job.

1 Select the target database

2 Select the backup storage

3 Select the backup strategies

4 Submit the job

You can start to use this powerful system with a 60-day full-featured free trial. Just click the button to get the installation package. You can click here to learn more about how to backup MySQL with Vinchin Backup & Recovery.

Conclusion

MySQL active-active synchronous replication is designed to provide high availability and flexibility. It allows multiple MySQL instances to be active simultaneously and synchronize data between each other, enabling bidirectional read and write operations. It ensures continuous availability, load balancing, data consistency, and flexibility. However, proper configuration and management are crucial, and the choice of implementation solutions and tools depends on specific requirements.

To efficiently protect database data, you can choose Vinchin Backup & Recovery to easily backup and recover database. Don’t miss the free trial.