Master MySQL Master‑Slave Replication: Principles and Step‑by‑Step Setup

1. Overview

As data volume and business load grow, a single MySQL instance can become a bottleneck. Deploying a master‑slave architecture enables read/write separation, improves availability, and provides backup and disaster‑recovery capabilities.

2. Replication Principle

MySQL supplies native support for replication. The master records every data change (DDL and DML) in the binary log (binlog). Slave servers read the binlog and replay the same operations, ensuring data consistency.

3. Replication Workflow

Client issues a write request; the master updates data and writes the change to the binlog.

The slave runs CHANGE MASTER TO to specify the master’s IP, port, user, password, and the starting binlog file and position; then START SLAVE launches two threads: io_thread (receives binlog from the master) and sql_thread (executes the events).

The master’s binlog‑dump thread streams the binlog to the slave’s io_thread.

The slave’s io_thread writes the received binlog into a relay log.

The slave’s sql_thread reads the relay log, parses each event, and re‑executes the original DML/DDL on the slave, achieving data restoration.

4. Building a One‑Master‑Two‑Slave Cluster with Docker

Three machines are prepared:

master  10.10.0.10
slave1  10.10.0.14
slave2  10.10.0.22

Each MySQL instance is launched in Docker using the 5.7 image:

docker run -p 3306:3306 --name mysql \
  -v /mydata/mysql/log:/var/log/mysql \
  -v /mydata/mysql/data:/var/lib/mysql \
  -v /mydata/mysql/conf:/etc/mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -d mysql:5.7

The configuration file my.cnf (mounted at /mydata/mysql/conf) enables the binlog in ROW format and sets a unique server_id for each node:

[mysql]
default-character-set=utf8
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
default-time_zone = '+8:00'
log-bin=mysql-bin
binlog-format=ROW
server_id=1

After editing, restart the container ( docker restart mysql) or edit the host file directly before the first start.

5. Creating the Replication User

CREATE USER 'slave-user'@'%' IDENTIFIED BY '126289';
GRANT REPLICATION SLAVE ON *.* TO 'slave-user'@'%';
FLUSH PRIVILEGES;

6. Configuring the First Slave

On the master, obtain the current binlog file and position:

SHOW MASTER STATUS;

On slave1, run:

CHANGE MASTER TO
  MASTER_HOST='10.10.0.10',
  MASTER_USER='slave-user',
  MASTER_PASSWORD='126289',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=751;

START SLAVE;

Check the replication status:

SHOW SLAVE STATUS;

Key fields to verify are Slave_IO_Running and Slave_SQL_Running (both should be Yes). Seconds_Behind_Master indicates replication lag.

7. Adding a Second Slave

Deploy slave2 with server_id=2 and enable a relay log:

# enable relay log
relay-log=mysql-relay

Because the master has been running for a while, its binlog may have advanced. To fully synchronize, slave2 must start from the earliest binlog file and position, not the values shown by SHOW MASTER STATUS. Use the appropriate MASTER_LOG_FILE and MASTER_LOG_POS values when issuing CHANGE MASTER TO. Otherwise Seconds_Behind_Master will be non‑zero.

8. Summary

MySQL master‑slave replication provides a reliable way to achieve read/write separation, data backup, and failover. By configuring binlog, setting unique server IDs, creating a replication user, and correctly issuing CHANGE MASTER TO and START SLAVE, a stable high‑availability cluster can be built.