Demystifying MySQL Replication Topologies: From Simple Master-Slave to Multi-Source Clusters

Why Replication Topology Decisions Matter More Than You Think

Every MySQL deployment reaches a point where a single server isn't enough. Maybe it's read traffic that's overwhelming the primary, or the need for a warm standby in another region, or a reporting team that wants live data without slowing down transactions. Replication seems like the obvious answer—and it often is—but the topology you choose ripples into every aspect of operations: failover speed, data loss risk, backup strategies, and even how you roll out schema changes.

We've seen teams jump into multi-source replication because it sounds modern, only to discover that conflict resolution is undocumented for their use case. Others stay on simple master-slave for years, paying the price of a single point of write failure. The key is understanding what each topology actually guarantees—and what it doesn't.

This guide is for engineers who already know basic MySQL replication commands but want to make informed architectural decisions. We'll walk through the most common topologies, explain the mechanism behind each, and highlight the pitfalls that aren't obvious from blog posts or vendor docs. By the end, you'll have a clear mental model of when to use which topology—and, just as important, when to avoid one entirely.

Foundations: What Most People Get Wrong About Replication

Before picking a topology, it's worth clearing up some confusion that leads to bad decisions. The most common misunderstanding is conflating replication with clustering. Replication is asynchronous (or semi-synchronous) data copying from one server to another; it does not provide distributed consensus, automatic failover, or strong consistency. Clustering solutions like MySQL InnoDB Cluster or Galera use synchronous replication and quorum-based decisions, which are fundamentally different beasts.

Async vs. Semi-Sync: The Trade-Off You Need to Internalize

Default MySQL replication is asynchronous: the primary commits a transaction, sends the binary log event, and moves on without waiting for the replica to confirm. This gives the lowest latency on the primary, but if the primary crashes before the replica receives the event, that transaction is lost. Semi-synchronous replication (semi-sync) adds a wait: the primary holds the commit until at least one replica acknowledges it received the event. This reduces data loss risk but increases commit latency. Many teams treat semi-sync as a magic bullet, but it's not a guarantee—if the replica crashes, the primary can time out and fall back to async, silently losing the protection.

Statement vs. Row-Based: Not Just a Performance Detail

Another foundational choice is binary log format. Statement-based replication logs the SQL query; row-based logs the changed rows. Statement-based is more compact but can produce different results if non-deterministic functions (like UUID()) are used. Row-based is safer and is the default in modern MySQL, but it can generate enormous binlog events for large updates. Mixing formats in a topology without understanding the implications can cause replication to break silently—a common pain when upgrading from older MySQL versions.

Replication Doesn't Solve Everything

Perhaps the biggest misconception is that replication equals high availability. It doesn't. If the primary fails, you still need to promote a replica manually (or use a tool like Orchestrator or ProxySQL). Replication lag can mean stale reads on replicas, and a poorly designed topology can amplify lag in ways that are hard to debug. Understanding these basics is essential before you start drawing boxes and arrows for your topology.

Patterns That Actually Work: From Simple to Complex

Let's look at the topologies that have proven themselves in production, starting with the simplest and moving toward more sophisticated setups. Each pattern has a specific job to do, and we'll cover when it's the right choice.

Master-Slave (Single Primary with Multiple Replicas)

This is the classic setup: one primary handles writes, one or more replicas handle reads. It's simple to configure, easy to monitor, and works well for read scaling and backup offloading. The primary weakness is the single point of write failure—if it goes down, writes stop until you promote a replica. This topology is ideal when your workload is read-heavy and you can tolerate brief write outages. Many teams start here and stay here for years.

Dual Primary (Active-Passive)

Two servers both configured as primaries, but only one actively accepts writes at a time. The passive primary acts as a warm standby with replication in both directions (or one direction with a failover script). This pattern reduces failover time because the standby already has the data, but it requires careful handling of auto-increment offsets to avoid key conflicts if both accidentally become writable. It's a good step up from simple master-slave when you need faster failover but don't want the complexity of multi-primary.

Multi-Source Replication

In multi-source replication, a single replica receives data from multiple primaries. This is useful for consolidating data from sharded databases or aggregating from different regions. MySQL 5.7 introduced built-in multi-source support via channel-based replication. The catch is that conflict resolution is left to the application—if two primaries insert rows with the same primary key, replication will break. This topology works well when the primaries have disjoint data sets (e.g., different customer ranges) or when you can guarantee no key collisions. It's not suitable for merging data from overlapping sources without application-level deduplication.

Galera Cluster (Synchronous Multi-Primary)

Galera (used by MariaDB and Percona XtraDB Cluster) provides synchronous multi-primary replication: any node can accept writes, and all nodes see the same data at the same time (with some caveats around certification-based replication). It offers strong consistency and automatic node provisioning, but it comes with trade-offs: write performance degrades as the cluster grows (due to the certification overhead), and network latency between nodes directly impacts throughput. Galera is excellent for high-availability within a single data center but struggles across geographically distributed sites.

MySQL InnoDB Cluster (Group Replication)

Oracle's official high-availability solution uses Group Replication, a Paxos-based protocol providing strong consistency and automatic failover. It's simpler to set up than Galera with MySQL Shell and Router, but it requires all nodes to be in the same group communication network, and it can be sensitive to network partitions. InnoDB Cluster is a solid choice for teams that want an integrated solution without third-party tools, but it's not as battle-tested in extreme scale scenarios as some other options.

Anti-Patterns and Why Teams Revert

Not every topology that looks good on paper works in practice. Here are the patterns we've seen teams adopt—and later abandon—along with the reasons why.

The Star Topology with a Central Hub

Some designs use a central replica that fans out data to many downstream replicas, creating a star. The idea is to reduce load on the primary, but the central hub becomes a single point of failure and a bottleneck. If the hub goes down, all downstream replicas stop receiving updates. Worse, the hub's replication lag can cascade, making downstream replicas increasingly stale. Teams often revert to a simple tree or direct replica connections to the primary.

Multi-Primary Without Conflict Resolution

Setting up two primaries that both accept writes is tempting for active-active failover, but without careful conflict resolution, it's a recipe for disaster. Even with auto-increment offsets, application-level updates to the same row on different primaries can cause replication to break with duplicate key errors. Most teams that try this end up switching to active-passive or using a distributed sequence generator—which adds complexity that often outweighs the benefits.

Replication Across High-Latency Links Without Tuning

Replicating across data centers with high latency (e.g., >50ms RTT) without adjusting timeouts or using semi-sync can lead to frequent replication failures, lag spikes, and slave retries that consume CPU. Teams sometimes attempt to replicate across continents with default settings, only to find that the replica is days behind. The fix usually involves tuning slave_net_timeout, using compressed protocol, or accepting that cross-region replication will have significant lag—and designing the application accordingly.

Ignoring Replication Lag Monitoring

Many teams set up replicas but don't monitor lag properly. They rely on Seconds_Behind_Master, which can be misleading—it resets to 0 when the replica's SQL thread catches up to the relay log, even if the relay log is far behind the primary. A better approach is to monitor the difference between the primary's binlog position and the replica's applied position, or use heartbeat records. Without proper monitoring, lag can silently grow until a failover attempt uses a stale replica, causing data loss.

Maintenance, Drift, and Long-Term Costs

Replication topologies are not set-and-forget. Over months and years, configuration drift, version upgrades, and schema changes can erode the reliability of your setup. Here's what to watch for.

Configuration Drift

When you add or remove replicas, it's easy to forget to update settings like sync_binlog, innodb_flush_log_at_trx_commit, or relay_log_space_limit on all nodes. A replica with different settings can behave unexpectedly during failover. Regular audits using configuration management tools (Ansible, Puppet, etc.) are essential. We recommend creating a baseline config file for each role (primary, replica, reporting replica) and enforcing it via automation.

Schema Changes and Replication

DDL statements (ALTER TABLE, etc.) can cause replication to break if they are run on the primary but the replica has a different schema version. This is especially common when using online schema change tools like pt-online-schema-change, which may leave temporary tables behind. The safest approach is to run schema changes only on the primary and ensure replicas are up to date before executing. For complex changes, consider using a replication-aware tool that pauses replication during critical phases.

Version Upgrades

MySQL replication is generally backward-compatible, but upgrading a replica before the primary (or vice versa) can introduce subtle bugs. For example, a newer MySQL version might write binlog events in a format that the older replica cannot parse. The recommended path is to upgrade replicas first, test, then upgrade the primary—but this requires careful planning and a rollback plan. Many teams skip testing and end up with replication errors that are hard to diagnose.

Storage and Disk Costs

Each replica stores a full copy of the data. If you have 10 replicas, you're paying for 10x storage. This can be a significant cost in cloud environments. Some teams reduce costs by using delayed replicas (with MASTER_DELAY) for point-in-time recovery, but that doesn't reduce storage. Consider using fewer replicas with higher performance, or offloading archival data to a different system.

When Not to Use Replication

Replication is a powerful tool, but it's not the right answer for every problem. Here are scenarios where you should consider alternatives.

You Need Strong Consistency for Writes

If your application requires that a write is immediately visible on all nodes and that concurrent writes never conflict, replication (async or semi-sync) is not sufficient. Use a synchronous solution like MySQL Group Replication, Galera, or an external distributed database like CockroachDB. Replication can give you eventual consistency at best, and the lag window can be unpredictable.

You Have a Write-Heavy Workload

Replication offloads reads, but it doesn't help with write scaling—all writes still go to the primary (in a typical topology). If your write throughput exceeds what a single server can handle, you need sharding or a distributed database. Multi-primary replication can spread writes, but the conflict resolution overhead often cancels out the gains. In practice, write-heavy workloads are better served by partitioning the data or using a NoSQL solution designed for horizontal write scaling.

You Need Automatic Failover with Guarantees

Replication alone does not provide automatic failover. You need an external orchestrator (like Orchestrator, MHA, or ProxySQL) to monitor the primary and promote a replica. Even then, failover is not instantaneous—there's a window where writes are lost or the application receives errors. If your RTO is measured in seconds, consider using a clustering solution with built-in failover, or a managed database service that handles this for you.

Your Network Is Unreliable or High Latency

Replication over a flaky network leads to constant reconnections, relay log corruption, and lag. While MySQL has improved network resilience, the overhead of retrying failed transactions can degrade performance. If your network has frequent outages or high latency (>100ms), consider using a message queue or a change data capture (CDC) tool like Debezium, which can buffer events and provide exactly-once delivery semantics.

Open Questions and FAQ

Even after choosing a topology, practical questions remain. Here are the ones we hear most often.

How do I handle failover without losing data?

There's no perfect answer, but the best practice is to use semi-sync replication and ensure that the replica you promote has the most recent binlog position. Tools like Orchestrator can check this automatically. You can also configure sync_binlog=1 and innodb_flush_log_at_trx_commit=1 on the primary to minimize the window of potential loss. For zero data loss, consider using Galera or Group Replication with a quorum-based commit.

Can I mix storage engines in replication?

Technically yes, but it's risky. If the primary uses InnoDB and the replica uses MyISAM, DDL operations may succeed on one but fail on the other. Also, transactional consistency is lost if the replica's engine doesn't support transactions. Stick with the same engine on all nodes unless you have a very specific reason not to.

What's the best way to monitor replication health?

Beyond Seconds_Behind_Master, monitor the actual binlog position difference using SHOW SLAVE STATUS and compare it to the primary's SHOW MASTER STATUS. Use tools like Prometheus with the mysqld_exporter to track lag over time. Set alerts for lag exceeding your tolerance (e.g., 30 seconds) and for replication errors like Slave_IO_Running: No or Slave_SQL_Running: No.

How do I reseed a replica without locking the primary?

Use a tool like XtraBackup or MySQL Enterprise Backup to take a hot backup from a replica (to reduce load on the primary), then restore it to the new replica and set up replication from the position recorded in the backup. Avoid using mysqldump for large datasets because it locks tables and is slow.

Should I use GTID or file-based replication?

GTID (Global Transaction Identifiers) simplifies failover and replication management because you don't need to track binlog file names and positions. It's recommended for all new deployments. The only downside is slightly larger binlog events and some edge cases with non-deterministic statements, but those are rare. If you're still on file-based replication, migrating to GTID is straightforward with MySQL 8.0's support for online GTID enablement.

Now that you have a clearer picture of the trade-offs, the next step is to map your own requirements—read vs. write ratio, latency tolerance, failover time, and operational maturity—to the topology that fits. Start with the simplest setup that meets your needs, and only add complexity when you have a concrete reason. Replication is a tool, not a goal; the best topology is the one you can operate reliably over years.