How to set up MySQL primary-replica replication

What and why

MySQL replication streams changes from a primary server to one or more replicas using the binary log. Replicas serve read traffic and act as warm standbys. This tutorial sets up classic binlog-position replication; GTID-based replication is a recommended evolution once this works.

Prerequisites

• Two MySQL 8 servers that can reach each other on port 3306.
• Administrative accounts on both.
• A maintenance window or a consistent snapshot method for the initial data copy.

Steps

1. Enable binary logging on the primary

In the primary's my.cnf:

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

Restart MySQL to apply.

2. Create a replication user

CREATE USER 'repl'@'%' IDENTIFIED BY 'strong-secret';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3. Capture the primary position

Lock briefly, record the position, and take a dump:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- note File and Position

In another session: mysqldump --all-databases --single-transaction > dump.sql, then UNLOCK TABLES;. Load the dump into the replica.

4. Configure the replica

In the replica's my.cnf set a unique id:

[mysqld]
server-id = 2
relay_log = relay-bin
read_only = ON

Restart MySQL.

5. Start replication

On the replica, point it at the primary using the captured file and position:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary-host',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='strong-secret',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=157;
START REPLICA;

6. Verify replica status

SHOW REPLICA STATUS\G

Confirm Replica_IO_Running: Yes, Replica_SQL_Running: Yes, and Seconds_Behind_Source near zero.

Verification

Insert a row on the primary and read it from the replica after a moment. Both running threads and low lag indicate healthy replication. Errors appear in Last_Error in the status output.

Next Steps

Migrate to GTID-based replication for easier failover, add monitoring on replication lag, and consider a managed proxy or orchestrator for automated failover.