Getting Started with MySQL Replication

Running MySQL on a single box works fine until that box dies or read traffic maxes it out. Replication solves both problems by continuously copying changes from a primary server to one or more replicas. It's the classic first step toward a more resilient, scalable database setup.

What it buys you

• Read/write splitting: send writes to the primary and spread reads across replicas to scale read throughput.
• Hot standby and failover: each replica holds a near-real-time copy, so you can promote one quickly if the primary goes down.
• Offloading heavy work: run reports, backups, and expensive queries on a replica without slowing down production writes.

How it works: the binlog

Replication is built on the binary log (binlog). The primary records every data change to its binlog. Each replica runs an I/O thread that pulls those events across the network into a local relay log, then an SQL thread replays them so the replica catches up. The process is asynchronous, and under normal conditions the lag stays in the millisecond range.

Configure the primary

Edit the primary's config file (on Ubuntu/Debian it's usually /etc/mysql/mysql.conf.d/mysqld.cnf) to turn on the binlog and assign a unique server-id:

[mysqld]
server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
binlog_format   = ROW

Restart the service to apply it:

sudo systemctl restart mysql

Then log in and create a dedicated replication account with the REPLICATION SLAVE privilege:

CREATE USER 'repl'@'%' IDENTIFIED BY 'StrongPass!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Note the current log coordinates — you'll need them when wiring up the replica:

SHOW MASTER STATUS;
-- Record File (e.g. mysql-bin.000003) and Position (e.g. 154)

Configure the replica

Give the replica a different server-id (say, 2) and restart it. On modern MySQL (8.0.23+), prefer the REPLICA/SOURCE syntax; the older CHANGE MASTER TO / START SLAVE still works but is deprecated:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = 'primary-ip',
  SOURCE_USER     = 'repl',
  SOURCE_PASSWORD = 'StrongPass!',
  SOURCE_LOG_FILE = 'mysql-bin.000003',
  SOURCE_LOG_POS  = 154;

START REPLICA;

Verify replication

Check the status on the replica:

SHOW REPLICA STATUS\G

Two lines matter most — both must read Yes:

• ReplicaIORunning: Yes
• ReplicaSQLRunning: Yes

Also watch SecondsBehindSource, which shows how far the replica trails the primary; it should sit at or near 0. If either thread shows No, the LastError field explains why.

Common pitfalls

• Duplicate server-id: every server — the primary and each replica — needs a globally unique server-id. Collisions cause replication errors or mysterious stalls, and this is the single most common mistake.
• Inconsistent starting data: START REPLICA only applies changes after the recorded position. If the primary already holds data, dump it first with mysqldump --single-transaction --source-data=2, load it into the replica, and start from the coordinates in that dump. Skip this and the two sides drift apart permanently.
• Networking and grants: make sure the firewall allows port 3306 and that the replication user's host mask permits connections from the replica's IP.

Summary

MySQL replication comes down to three moves: enable binlog with a unique server-id and create a replication account on the primary, point the replica at it with CHANGE REPLICATION SOURCE and run START REPLICA, then confirm both threads are running with SHOW REPLICA STATUS. Keep server-id unique and seed the replica with a consistent snapshot, and you'll have a solid read-scaling and hot-standby setup running on your own servers.