Replication and read replica concepts. One MySQL server eventually becomes the bottleneck for read-heavy apps. Replication copies data from a primary to one or more replicas so you can spread read load and survive hardware failure – with trade-offs you need to understand.
Primary and replica basics
In MySQL’s async replication model, the primary accepts writes and records them in the binary log (binlog). Replicas pull those events and apply them to their own copy of the data. Reads can go to replicas; writes still go to the primary.
-- On primary: check binlog is enabled
SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;
-- On replica: typical status checks
SHOW REPLICA STATUSG
-- (older versions: SHOW SLAVE STATUSG)Replication lag is the gap between a commit on the primary and the same row appearing on a replica. Under load, lag of a few seconds is common. Your app must tolerate slightly stale reads on replicas or route critical reads to the primary.
When you need replicas
- Read scaling – reporting, dashboards, search indexing, API reads that outnumber writes
- High availability – promote a replica if the primary dies (with tooling and runbooks, not wishful thinking)
- Geography – read-local replica near users, primary in one region
- Backup isolation – mysqldump or logical backup from a replica avoids load on primary
When you do not need them yet
A single well-tuned MySQL instance handles more than teams expect. Replicas add operational complexity: lag monitoring, split read/write routing, schema changes on multiple nodes, and failure modes where a replica falls behind silently until someone notices.
Fix slow queries and missing indexes first. Then consider replicas if CPU or I/O on the primary is genuinely saturated by reads.
Read routing in application code
Your app needs two connection targets – or a proxy that routes automatically:
- Writes, transactions, and reads-after-write go to the primary
- Idempotent reporting and list pages can use a replica
- After creating a record, reading it back on a replica may fail until replication catches up – route that read to the primary or retry
-- Session hint: run next SELECT on primary after write (app-level pattern)
-- Pseudocode flow:
-- 1. INSERT INTO orders ... ON primary
-- 2. SELECT * FROM orders WHERE id = LAST_INSERT_ID() ON primary
-- 3. List orders for dashboard ON replica (stale OK)GTID and failover
Global Transaction Identifiers (GTID) tag each transaction so replicas know exactly what they have applied. They simplify failover: promote the most caught-up replica, point apps at the new primary, repoint other replicas. Still not automatic without orchestration tools (Orchestrator, managed cloud failover).
SHOW VARIABLES LIKE 'gtid_mode';
SELECT @@GLOBAL.gtid_executed;Replication gotchas
- Non-deterministic statements – LIMIT without ORDER BY, UUID() in replicated statements can diverge replicas (statement-based replication); row-based binlog format is safer
- Schema drift – ALTER on primary must apply to replicas; long-running DDL causes lag spikes
- Replica writes – do not write to replicas unless you know exactly why; breaks consistency
- Semi-sync – waits for one replica to acknowledge before commit; reduces data loss risk, adds latency
The next tutorial covers locking, deadlocks, and concurrency bugs – what happens when many sessions touch the same rows.