MySQL Replication Basics

Introduction¶

MySQL replication copies writes from a primary to one or more replicas by shipping the binary log (a record of every change) and replaying it. It underpins read scaling, backups, and failover.

How it works¶

1. The primary writes every data change to its binary log.
2. Each replica's IO thread connects to the primary and streams new binlog events into a local relay log.
3. The replica's SQL thread (or multiple worker threads) applies the relay log to the replica's data.

This is asynchronous by default: the primary commits and moves on without waiting for any replica. A replica can therefore lag, and a primary crash can lose the last few transactions that hadn't reached a replica.

Semi-synchronous replication makes the primary wait for at least one replica to acknowledge receipt (not apply) before returning commit to the client — bounded data loss, at a latency cost.

GTID vs file-and-position¶

• Binlog file + position — the old way. A replica tracks "I'm at mysql-bin.000042 offset 1934". Fragile across failover: positions differ per server.
• GTID (Global Transaction ID) — each transaction gets a cluster-unique ID (server_uuid:sequence). A replica tracks the set of transactions it has applied. Failover is far simpler: point a replica at a new primary and it works out what it's missing.

Use GTID for anything new:

[mysqld]
server_id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
log_replica_updates = ON
read_only = ON            # replicas: prevent accidental writes
super_read_only = ON

Setting up a replica (GTID)¶

-- On the primary: create a replication user
CREATE USER 'repl'@'10.0.%' IDENTIFIED BY 'strongpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.%';

# Seed the replica from a consistent backup that records the GTID set
mysqldump --all-databases --single-transaction --source-data=2 --set-gtid-purged=ON \
  -h primary > dump.sql
mysql -h replica < dump.sql
# (for large DBs use a physical backup — see the xtrabackup post)

-- On the replica
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.1',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='strongpass',
  SOURCE_AUTO_POSITION=1;      -- GTID auto-positioning
START REPLICA;

Monitoring¶

SHOW REPLICA STATUS\G

Key fields:

• Replica_IO_Running / Replica_SQL_Running — both must be Yes.
• Seconds_Behind_Source — lag in seconds. Crude (it's "how old is the event I'm applying"), but a rising trend is real.
• Last_IO_Error / Last_SQL_Error — why it stopped.
• Retrieved_Gtid_Set vs Executed_Gtid_Set — how much is fetched but not yet applied.

Better lag measurement: a heartbeat table the primary updates every second; compare its timestamp on the replica (pt-heartbeat does this).

Verification and troubleshooting¶

• Seconds_Behind_Source climbing — the SQL thread can't keep up. Causes: a single big transaction, a long-running ALTER, missing index on the replica making a statement slow, or single-threaded apply. Enable parallel replication: replica_parallel_workers = 4, replica_parallel_type = LOGICAL_CLOCK.
• Replica_SQL_Running: No, duplicate-key or "row not found" error — the replica diverged (someone wrote to it, or a non-deterministic statement). Options: fix the row by hand and START REPLICA, skip the transaction (SET GTID_NEXT=...; BEGIN; COMMIT; to inject an empty one — dangerous, you're hiding a difference), or re-clone the replica. Set super_read_only to prevent recurrence.
• Replica_IO_Running: Connecting — network/firewall to port 3306, wrong credentials, or the primary purged binlogs the replica still needed (Got fatal error 1236). Re-seed from a fresh backup.
• Got fatal error 1236 ... the first event ... master has purged binary logs — replica was offline too long; binlog_expire_logs_seconds on the primary is shorter than your worst-case replica outage. Re-clone; raise retention.
• After failover, old primary comes back and has extra transactions — it committed writes that never replicated. With GTID you'll see an errant transaction. It must be reconciled or wiped before rejoining.
• Replica CPU high, primary fine — parallel apply contention or the replica hardware is weaker. Replicas should be sized like the primary.

Related tools and reading¶

• On-site: Port Checker (3306).
• Related posts: Backups with Percona XtraBackup, Safe online schema changes in MySQL.