How to Set Up MySQL Master‑Slave Replication (Step‑by‑Step Guide)

Prerequisites

Prepare 3‑5 virtual machines.

Install a bare‑metal MySQL 8.0 instance.

Master‑Slave Architecture

Two common patterns are "one master, many slaves" and "cascading replication". Both rely on the binary log (binlog) to synchronize changes; the slave re‑executes DDL statements recorded in the binlog.

Step 1: Configure server_id

Set server_id in my.cnf. Use SHOW VARIABLES LIKE 'server_id'; to view the current value. Ensure each server has a unique ID (default is 1).

On Debian, edit /etc/mysql/my.cnf and add the following, then restart MySQL:

[mysqld]
server-id=1  # master configuration

[mysqld]
server-id=2  # slave configuration

Step 2 (Optional): Get Master Status

On the master, run: SHOW MASTER STATUS; This displays the current binlog file name (File) and position (Position), as well as other replication settings.

Step 3: Configure the Slave

Execute the following on the slave to point it to the master:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.3.107',
SOURCE_USER='root',
SOURCE_PASSWORD='1234',
SOURCE_LOG_FILE='binlog.000009',
SOURCE_LOG_POS=0;

Parameters:

SOURCE_HOST : Master address

SOURCE_USER : Username

SOURCE_PASSWORD : Password

SOURCE_LOG_FILE : Current binlog file on the master

SOURCE_LOG_POS : Offset within the binlog file

Check replication status with:

SHOW REPLICA STATUS;  # view status
START REPLICA;        # start syncing
STOP REPLICA;         # pause syncing
RESET REPLICA ALL;    # delete all replication data

GTID Mode (Recommended)

Enable GTID‑based replication for automatic log positioning. SHOW GLOBAL VARIABLES LIKE 'gtid_mode'; Configure the master:

[mysqld]
server-id=1
gtid_mode=ON
enforce_gtid_consistency=TRUE

On the slave, use:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.3.107',
SOURCE_USER='root',
SOURCE_PASSWORD='123',
SOURCE_AUTO_POSITION=1;

GTID eliminates the need to specify SOURCE_LOG_FILE and SOURCE_LOG_POS.

Common Commands

# Master commands
SHOW MASTER STATUS;          # view master status
RESET MASTER;                # reset binlog (starts from 000001)

# Slave commands
SHOW REPLICA STATUS;         # view slave status
START REPLICA;               # begin replication
STOP REPLICA;                # pause replication
RESET REPLICA ALL;           # delete all replication data
SELECT * FROM performance_schema.replication_applier_status_by_worker; # view thread info

# Utility
SHOW VARIABLES LIKE 'server_id';   # check server_id
SHOW GLOBAL VARIABLES LIKE 'gtid_mode'; # check GTID status