Step‑by‑Step Guide to Setting Up MySQL Master‑Slave Replication with Docker

1. Introduction: Why Learn MySQL Master‑Slave Replication?

For a DBA, mastering high‑availability, scalable, and secure data architectures often starts with understanding how a single MySQL instance (the master) can write data that is automatically synchronized to one or more replica instances (the slaves).

2. Prerequisites

Check the following conditions

Condition

Met?

Password‑less SSH login configured

✅ (already set)

Docker installed on each machine

✅

MySQL image (e.g., mysql:8.0) available on each host

✅

Firewall (firewalld) closed or port 3306 open

⚠️ needs verification

Ability to run shell scripts and basic commands

✅

3. Architecture Overview

We will build the following MySQL master‑slave replication topology:

Master: master

Slaves: node1, node2

Data written to the master is streamed via binary logs (binlog) to the two slaves, enabling real‑time or delayed replication.

4. Hands‑On Setup (Step‑by‑Step)

Step 1: Start the master container and enable binlog

Run the following Docker command on the master host:

docker run -d \
--name mysql-master \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /opt/mysql-master/conf:/etc/mysql/conf.d \
-v /opt/mysql-master/data:/var/lib/mysql \
mysql:8.0

Create the configuration file /opt/mysql-master/conf/my.cnf with the content:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=testdb

Restart the container to apply the configuration:

docker restart mysql-master

Step 2: Create the replication user

Enter the master container:

docker exec -it mysql-master bash
mysql -uroot -p123456

Execute the following SQL statements:

CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

Note the returned File and Position values (e.g., mysql-bin.000001 , 154 ).

Step 3: Deploy slave containers (node1, node2)

For node1 (server‑id = 2) run:

docker run -d \
--name mysql-slave1 \
-p 3307:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /opt/mysql-slave1/conf:/etc/mysql/conf.d \
-v /opt/mysql-slave1/data:/var/lib/mysql \
mysql:8.0

Create /opt/mysql-slave1/conf/my.cnf :

[mysqld]
server-id=2
relay-log=relay-log
read-only=1

Restart the container. Repeat the same steps for node2, using server-id=3 , port 3308 , and the directory /opt/mysql-slave2/ .

Step 4: Configure slaves to connect to the master

Inside each slave container, run:

CHANGE MASTER TO
 MASTER_HOST='YOUR_MASTER_IP',
 MASTER_USER='repl',
 MASTER_PASSWORD='replpass',
 MASTER_LOG_FILE='mysql-bin.000001',
 MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G

Both Slave_IO_Running and Slave_SQL_Running should display Yes when replication is healthy.

Step 5: Verify replication

Create a test database and table on the master:

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE t_user (id INT PRIMARY KEY, name VARCHAR(20));
INSERT INTO t_user VALUES (1, 'ITxianyu');

Query the same table on node1 and node2. If the row ITxianyu appears, the master‑slave setup is successful.

Troubleshooting

Problem

Suggested Check

Slave not syncing

Verify binlog file name and position; ensure network connectivity to the master.

Slave_IO_Running: No

Check that the master IP and replication user password are correct.

Docker containers cannot communicate

Use the host machine IP instead of container names for the master address.

Conclusion

By completing this guide you have deployed a MySQL master‑slave replication environment, a fundamental skill for any aspiring DBA seeking reliable, scalable database architectures.