Understanding MySQL Dual‑Master Architecture and Replication Modes

Dual‑master architecture is a common MySQL pattern where two primary nodes provide services to each other as master and standby; this article explores its various replication modes.

1 Dual‑Master Replication

This mode configures two primary databases, each offering read‑write services and synchronizing writes to the other. It improves write throughput, especially for geographically distributed applications, but can cause synchronization issues.

Data written on M1 must be replicated to M2 and vice‑versa. While this boosts write efficiency, conflicts can arise. Example 1: simultaneous inserts into a table with an auto‑increment primary key may generate identical keys, leading to binlog row‑format conflicts.

MySQL binlog has three formats:

STATEMENT – records the original SQL statement.

ROW – records the actual data changes.

MIXED – combines STATEMENT and ROW, letting MySQL choose based on the statement.

Since MySQL 5.0, setting auto_increment_increment and auto_increment_offset can mitigate auto‑increment conflicts.

Example 2: a statement executed on M1 generates a binlog that M2 replicates back to M1, causing a loop. The fix is to assign distinct server_id values to each node so that a node discards logs originating from itself.

Example 3: concurrent updates on the same row (e.g., user balance) can produce inconsistent data. A common remedy is strict data partitioning and permission control, ensuring that a particular user's data is modified only on one master.

Example 4: if one master fails (e.g., M1), the application may still write to the surviving master (M2), but replication stops. Adding slave nodes and configuring automatic failover resolves this issue.

2 Master‑Slave Replication

In this mode, only one master handles writes at any time while the other acts as a read‑only replica. Advantages include easier master‑to‑slave switching, better fault‑tolerance and recovery, the ability to perform upgrades without affecting the application, and avoidance of loop replication and master‑slave inconsistency. The drawback is that write performance does not increase.

3 Master‑Master Architecture with Backup Nodes

Each primary can also have a backup node, providing redundancy and allowing read traffic to be offloaded to the backups. This improves overall read capacity and protects against single‑point failures, but raises hardware costs.

4 Ring Replication

Ring replication connects multiple masters in a circular topology where each node is the master of the next and the backup of the previous. This setup is generally discouraged because failover is complex; if a node fails, its binlog continues circulating, making it hard to stop replication without manual intervention.

5 Summary

The article introduced several MySQL dual‑master replication architectures, highlighting the need to address loop replication, single‑point failures, and proper data permission segregation to maintain high availability and data consistency.