Database Replication

Database Replication is the process of copying data from one database server (the Master/Primary) to one or more other database servers (the Slaves/Replicas). This is fundamental for achieving both high availability and improved read performance.

1. Master-Slave Replication

The most common replication architecture. One server is designated as the Master (or Primary), and one or more servers are designated as Slaves (or Replicas).

• All Write operations (INSERT, UPDATE, DELETE) go exclusively to the Master.
• All Read operations (SELECT) are distributed across the Slaves.
• The Master asynchronously streams its changes (via a Write-Ahead Log or Binary Log) to all Slaves, keeping them in sync.

Advantages:

• Read Scalability: If you have 1 million reads/sec and only 10,000 writes/sec, you can add 10 read replicas. Each handles 100,000 reads/sec, while the single master easily handles the 10,000 writes.
• High Availability: If the Master crashes, one of the Slaves can be promoted to become the new Master (failover). The application experiences a brief interruption but does not suffer permanent data loss.

Disadvantages:

• Write Bottleneck: All writes still go to a single Master. If your write volume exceeds the Master's capacity, you need Database Sharding.
• Replication Lag: Because replication is asynchronous, there is a brief delay where Slaves might have slightly stale data. A user writes a comment and immediately reads the page from a Slave—the comment might not appear for a few hundred milliseconds.

2. Master-Master Replication

In this architecture, multiple servers can all accept both reads AND writes. Each master replicates its changes to the other masters.

Advantages:

• Write Scalability: Writes can be distributed across multiple nodes.
• Geographic Distribution: A master in the US handles US traffic, while a master in Europe handles European traffic, both staying in sync.

Disadvantages:

• Write Conflicts: If two masters simultaneously update the same row, a conflict occurs. Conflict resolution is extremely complex.
• Increased Complexity: Maintaining consistency is much harder than Master-Slave.

3. Synchronous vs Asynchronous Replication

• Synchronous: A write is not confirmed to the client until ALL replicas have received and written the data. Guarantees zero data loss but dramatically increases write latency.
• Asynchronous: A write is confirmed as soon as the Master writes it locally. Replicas receive the data later. Fast, but risks losing the most recent writes if the Master crashes before replication completes.
• Semi-Synchronous: A write is confirmed after at least ONE replica acknowledges it. A common middle ground.