Mastering MySQL Replication: From Single Master to Dual‑Master Cascades

One Master One Slave

It is the most basic replication structure, used to offload the load from a single database server and enables read/write separation.

One Master Multiple Slaves

When a single slave cannot handle read request load, additional slaves can be added to achieve load balancing and distribute read pressure.

Slaves can also be assigned specific duties to serve different systems; for example, some slaves handle front‑end website reads while others serve back‑end analytics requests.

Because different systems have distinct query requirements, partitioned slaves can have tailored indexes to better serve their target systems.

Dual Master Replication

A master may go offline due to failure or maintenance, requiring a slave to be promoted to master.

When the original master becomes available again, its data may be stale, so it must be added back as a slave rather than a master, which requires rebuilding its replication setup.

Dual‑master architecture solves this by each server acting as the other's master and slave, while externally it still appears as one master and one slave.

When the primary master goes offline, the standby master switches to primary; when the original master comes back online, it resumes replication from the recorded position automatically, eliminating manual intervention and greatly improving efficiency.

Cascading Replication

When too many slaves are directly attached to a master, the master’s slave I/O threads become numerous, imposing significant load on the master.

Cascading architecture reduces the number of slaves directly connected to the master, alleviating master load, distributing replication requests, and improving overall replication efficiency.

Dual‑Master Cascading

Cascading replication addresses the bottleneck caused by many slaves, yet the single‑master switch‑over maintenance issue remains.

To resolve this, the dual‑master structure can be incorporated.

When necessary, slaves can be further tiered.

Choosing a Replication Topology

MySQL offers many replication topologies; the biggest challenge is data latency, so selecting a topology requires evaluating your specific situation and assessing the impact of latency on the system.