Understanding Database Replication: Master-Slave vs. Master-Master

Database replication is the process of copying data from one database server to one or more others to ensure high availability, load balancing, and disaster recovery. Choosing the right architecture depends on your application's read/write ratio and consistency requirements.

1. Master-Slave Replication (Primary-Replica)

In this hierarchical model, there is a clear distinction between the roles of the servers. One "Master" node handles all data modifications, while "Slave" nodes serve as read-only copies.

How It Works

1. Write Action: The application sends an INSERT, UPDATE, or DELETE request to the Master.
2. Logging: The Master records this change in its Binary Log (binlog).
3. Synchronization: Slave nodes use an I/O thread to pull these log updates and a SQL thread to apply them to their local data.
4. Read Distribution: The application sends heavy read queries to the Slave nodes to reduce the Master's load.

Key Characteristics

• Consistency: Generally offers strong consistency (though slight "replication lag" can occur in asynchronous setups).
• Workload: Best for read-heavy applications (e.g., blogs, e-commerce, news sites).
• Failover: If the Master fails, a Slave must be promoted to be the new Master, which often requires manual intervention or a failover script.

Pros & Cons

2. Master-Master Replication (Multi-Master)

In this bidirectional model, two or more nodes act as Masters simultaneously. Every node can accept both read and write operations.

How It Works

1. Bidirectional Sync: Changes made on Node A are sent to Node B, and vice versa. Each node acts as a slave to the other.
2. Conflict Detection: Since both can modify the same row at once, the system must use conflict resolution strategies (e.g., "Last Update Wins" or Timestamp-based ordering).
3. Write Scaling: Applications can distribute write requests across multiple geographic locations.

Key Characteristics

• High Availability: If one Master goes down, the application simply routes all traffic to the other Master without downtime.
• Consistency: Typically follows eventual consistency; it takes time for all nodes to agree on the final state.
• Complexity: Much harder to set up and maintain due to potential "split-brain" scenarios during network partitions.

Pros & Cons

Comparison Summary

Which One Should You Choose?

• Choose Master-Slave if your application is simple, read-heavy, and you need a cost-effective way to ensure data safety and read speed.
• Choose Master-Master if your application requires 24/7 write availability, high throughput for writes, or if you need users in different parts of the world to write to a local server for lower latency.