====== MySQL Replication ======

Replication is a mechanism where:

data from a primary (master) database
is copied to one or more replica (slave) databases

Goal:

improve read performance
increase availability
enable backup / disaster recovery
scale database horizontally (read scaling)

===== Basic Architecture =====

<code> Write (INSERT/UPDATE/DELETE) ↓ PRIMARY DB ↓ (replication) ┌───────────┴───────────┐ ↓ ↓ REPLICA 1 REPLICA 2 (read-only) (read-only) </code>

===== How Replication Works =====

===== Step-by-step =====

Application writes to Primary
<code sql> INSERT INTO users VALUES (...); </code>
Primary records change in:
binary log (binlog)
<code> binlog = history of all changes </code>
Replica reads binlog
via IO thread
Replica applies changes
via SQL thread

===== Result =====

Primary = source of truth
Replicas = copies (slightly delayed)

===== Real Example =====

User registers:

<code> Write → Primary DB </code>

Then:

<code> Replica DB receives update after ~10–200ms delay </code>

This delay is called:

<code> Replication Lag </code>

====== Types of Replication ======

===== 1. Asynchronous Replication =====

Most common.

<code> Primary → Replica (no wait) </code>

Meaning:

Primary does NOT wait for replica
fastest write performance
possible small data delay

===== 2. Semi-Synchronous Replication =====

<code> Primary waits for at least 1 replica ACK </code>

Pros:

safer than async

Cons:

slower writes

===== 3. Synchronous Replication =====

<code> Primary waits for ALL replicas </code>

Pros:

strongest consistency

Cons:

slowest performance

(MySQL rarely uses full sync in practice)

====== Why Use Replication? ======

===== 1. Read Scaling =====

Most apps:

<code> 80% reads 20% writes </code>

So:

<code> Primary → writes only Replicas → handle reads </code>

Example:

<code> GET /users → Replica DB POST /users → Primary DB </code>

===== 2. High Availability =====

If Primary fails:

promote a replica to new primary

=> system still works

===== 3. Backup Safety =====

Run backups on replica instead of primary:

reduces load on production DB

====== Common Architecture ======

<code> App / \ WRITE READ ↓ ↓ PRIMARY REPLICA(s) </code>

====== Replication Lag ======

===== What is it? =====

Delay between:

data written in primary
data appearing in replica

===== Example =====

<code> User updates profile → Primary updated immediately → Replica updated after 100ms </code>

===== Problem =====

If user reads from replica immediately:

may see old data

===== Solution =====

Strategies:

read-your-write → always read from primary after write
or accept eventual consistency

====== Common Problems ======

===== 1. Data Inconsistency =====

Replica is not always up-to-date.

===== 2. Lag Under Heavy Load =====

If traffic is high:

replica cannot keep up
lag increases

===== 3. Failover Complexity =====

Need tools like:

ProxySQL
Orchestrator
Kubernetes operators

====== Production Example ======

<code> Primary: 1 DB (writes)

Replicas:
2–5 DBs (reads)
</code>

Traffic split:

<code> 90% reads → replicas 10% writes → primary </code>

====== Summary ======

^ Concept ^ Meaning ^
| Primary DB | Handles writes |
| Replica DB | Copies data from primary |
| Binlog | Change log of primary |
| Replication Lag | Delay between primary and replica |
| Async Replication | Fast but eventual consistency |
| Sync Replication | Strong consistency but slow |
| Use Case | Read scaling + high availability |