DB Replication

Replication is keeping multiple copies of data at geographically distributed nodes. It helps achieve availability, scalability, and performance.
If frequent Write operations are in the DB, replication becomes complex, i.e., consistency becomes an issue.

Replication approaches

Two types of replication approaches:

Synchronous

• Synchronous replication refers to a process where the primary node (main) waits for acknowledgments from all secondary nodes (replicas) about updating the data. Once it receives acknowledgment from all secondary nodes, the primary node reports success to the client.
• Consistency > Availability

Asynchronous

• On the other hand, asynchronous replication is a process where the primary node doesn't wait for acknowledgment from the secondary nodes. Instead, it reports success to the client after updating itself.
• Availability > Consistency
• MySQL, by default, does this kind of replication.

^[1]

Replication Models

There are various replication models are also there:

• Single leader or primary-secondary replication
• Multi-leader replication
• Peer-to-peer or leaderless replication

Primary-Secondary replication

• Earlier, it was called a master-slave way of replication.
• One designated node acts as primary, processing writes to stored data and sending writes to secondary nodes to keep them in sync.
• Practical when the workload is read-heavy.

Replication methods

There are many different replication methods in primary-secondary replication:

• Statement-based replication
• Write-ahead log (WAL) shipping
• Logical (row-based) log replication

Statement based replication

• In the statement-based replication approach, the primary node records every statement it executes, such as insert, delete, update, etc. These recorded statements are then sent to the secondary nodes for execution. This type of replication was utilized in MySQL versions before 5.1.
• Any nondeterministic function, such as NOW() or CURRENT_TIMESTAMP, could result in different writes on both the follower and leader.
• Additionally, a specific written statement depends on a previous write, and both end up reaching the follower in the wrong order. In that case, the outcome on the follower node may be uncertain.

Write-ahead log (WAL) shipping

To be continued...