Replication Topologies

Design master-slave, master-master, and circular replication for specific business needs.

In this lesson, you will learn how to architect MySQL and MariaDB replication topologies to balance data availability, read scaling, and write distribution based on specific business requirements.

Understanding Replication Topologies

Replication topologies define how data flows between your database nodes. Choosing the right one depends on whether your priority is offloading read traffic, providing high availability, or distributing writes.

Master-Slave (Primary-Replica)

This is the most common topology, where one Master handles all write operations, and one or more Slaves replicate that data for read-only purposes.

Best for: Scaling read-heavy applications. By routing SELECT queries to your slaves, you prevent read traffic from slowing down your primary write node.
Business need: A content-heavy website where articles are updated once but read by thousands of users.

Master-Master (Active-Active)

In this setup, two nodes act as masters simultaneously. Every node can perform both reads and writes, and changes are replicated to the other.

Best for: High availability and local write latency. If one node fails, the other can take over the entire workload instantly.
Caution: You must manage potential conflicts (e.g., both masters updating the same row at the same time).
Business need: Global applications requiring 24/7 uptime where writes must be accepted at any time.

Circular Replication

In circular (or ring) replication, Node A replicates to Node B, Node B to Node C, and Node C back to Node A.

Best for: Specific use cases like cross-region data distribution or isolated analytical workloads.
Design Note: If you write to any node, that write travels through the chain. This requires careful management of server_id values to prevent infinite loops where a node continuously re-executes its own relayed updates.

Configuring the Flow

Regardless of the topology, you initiate the relationship using the CHANGE MASTER TO command (or CHANGE REPLICATION SOURCE TO in newer versions):

-- Executed on the Slave/Replica
CHANGE MASTER TO 
MASTER_HOST='192.168.1.5', 
MASTER_USER='repl_user', 
MASTER_PASSWORD='secure_password', 
MASTER_LOG_FILE='binlog.000001', 
MASTER_LOG_POS=4;
START SLAVE;

Key Takeaway: Choose Master-Slave for read scaling, Master-Master for high availability, and Circular Replication for specialized multi-node data distribution.