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:

<code>[mysqld]
server-id=1  # master configuration
</code>

<code>[mysqld]
server-id=2  # slave configuration
</code>

Step 2 (Optional): Get Master Status

On the master, run:

<code>SHOW MASTER STATUS;</code>

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:

<code>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;
</code>

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:

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

GTID Mode (Recommended)

Enable GTID‑based replication for automatic log positioning.

<code>SHOW GLOBAL VARIABLES LIKE 'gtid_mode';</code>

Configure the master:

<code>[mysqld]
server-id=1
gtid_mode=ON
enforce_gtid_consistency=TRUE
</code>

On the slave, use:

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

GTID eliminates the need to specify

SOURCE_LOG_FILE

and

SOURCE_LOG_POS

.

Common Commands

<code># 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
</code>