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)

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

Application writes to Primary

 INSERT INTO users VALUES (...); 

Primary records change in: binary log (binlog)

 binlog = history of all changes 

Replica reads binlog via IO thread Replica applies changes via SQL thread

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

User registers:

 Write → Primary DB 

Then:

 Replica DB receives update after ~10–200ms delay 

This delay is called:

 Replication Lag 

Most common.

 Primary → Replica (no wait) 

Meaning:

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

 Primary waits for at least 1 replica ACK 

Pros:

safer than async

Cons:

slower writes

 Primary waits for ALL replicas 

Pros:

strongest consistency

Cons:

slowest performance

(MySQL rarely uses full sync in practice)

Most apps:

 80% reads 20% writes 

So:

 Primary → writes only Replicas → handle reads 

Example:

 GET /users → Replica DB POST /users → Primary DB 

If Primary fails:

promote a replica to new primary

⇒ system still works

Run backups on replica instead of primary:

reduces load on production DB

 App / \ WRITE READ ↓ ↓ PRIMARY REPLICA(s) 

Delay between:

data written in primary data appearing in replica

 User updates profile → Primary updated immediately → Replica updated after 100ms 

If user reads from replica immediately:

may see old data

Strategies:

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

Replica is not always up-to-date.

If traffic is high:

replica cannot keep up lag increases

Need tools like:

ProxySQL Orchestrator Kubernetes operators

 Primary: 1 DB (writes)

Replicas:
2–5 DBs (reads)

Traffic split:

 90% reads → replicas 10% writes → primary