DEV Community

SHARON SHAJI
SHARON SHAJI

Posted on

Setting Up MySQL Master-Slave Replication with Docker Compose (1 Master + 2 Read Replicas)

#mysql #docker #devops #database

Setting up MySQL replication for the first time trips almost everyone up on the same two things: the containers can't see each other on the network, and the slave gets locked out of its own bootstrap process. This guide walks through a working 1 master + 2 read-replica setup using Docker Compose, GTID-based replication, and covers the exact errors you'll likely hit along the way.

Architecture

Node Container Name Server ID Host Port Role
Master mysql-master 1 3306 Read/Write
Slave 1 mysql-slave-1 2 3307 Read-only
Slave 2 mysql-slave-2 3 3308 Read-only

All three containers share one Docker bridge network so they can resolve each other by hostname, and replication uses GTID auto-positioning — no manual binlog file/offset tracking required.

Prerequisites

  • Docker + Docker Compose installed
  • All three services defined in a single docker-compose.yml — splitting them into separate files/folders puts each on its own isolated network and they won't be able to see each other

1. The docker-compose.yml 

version: "3.8"

networks:
  mysql-net:
    driver: bridge

services:
  mysql-master:
    image: mysql:8.0.20
    container_name: mysql-master
    networks:
      - mysql-net
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: password
    command:
      - --server-id=1
      - --log-bin=mysql-bin
      - --binlog-format=ROW
      - --gtid-mode=ON
      - --enforce-gtid-consistency=ON
      - --default-authentication-plugin=mysql_native_password
    volumes:
      - ./data-1/mysql:/var/lib/mysql
    restart: always

  mysql-slave-1:
    image: mysql:8.0.20
    container_name: mysql-slave-1
    networks:
      - mysql-net
    depends_on:
      - mysql-master
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: password
    command:
      - --server-id=2
      - --relay-log=mysql-relay-bin
      - --gtid-mode=ON
      - --enforce-gtid-consistency=ON
      - --default-authentication-plugin=mysql_native_password
    volumes:
      - ./data-2/mysql:/var/lib/mysql
    restart: always

  mysql-slave-2:
    image: mysql:8.0.20
    container_name: mysql-slave-2
    networks:
      - mysql-net
    depends_on:
      - mysql-master
    ports:
      - "3308:3306"
    environment:
      MYSQL_ROOT_PASSWORD: password
    command:
      - --server-id=3
      - --relay-log=mysql-relay-bin
      - --gtid-mode=ON
      - --enforce-gtid-consistency=ON
      - --default-authentication-plugin=mysql_native_password
    volumes:
      - ./data-3/mysql:/var/lib/mysql
    restart: always
Enter fullscreen mode Exit fullscreen mode

⚠️ Don't add --read-only / --super-read-only to the slave command: block yet. MySQL's entrypoint script spins up a temporary server on first boot to apply MYSQL_ROOT_PASSWORD. If read-only flags are already active at that point, that step silently fails, root ends up with an empty password, and you'll hit Access denied on every login attempt afterward. Read-only gets applied manually in Step 6, once the container has already initialized.

2. Start the cluster 

docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

Give it 15-20 seconds to initialize, then confirm all three nodes are reachable:

docker exec -it mysql-master  mysql -uroot -ppassword -e "SELECT 1;"
docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "SELECT 1;"
docker exec -it mysql-slave-2 mysql -uroot -ppassword -e "SELECT 1;"
Enter fullscreen mode Exit fullscreen mode

If any of these return Access denied, the data directory probably wasn't empty on first boot (leftovers from a previous attempt), so MYSQL_ROOT_PASSWORD never got applied:

docker-compose down
sudo rm -rf ./data-1/mysql ./data-2/mysql ./data-3/mysql
docker-compose up -d
Enter fullscreen mode Exit fullscreen mode

3. Create the replication user on the master 

docker exec -it mysql-master mysql -uroot -ppassword
Enter fullscreen mode Exit fullscreen mode 
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Sanity check:

SELECT user, host, plugin FROM mysql.user WHERE user='repl';
Enter fullscreen mode Exit fullscreen mode

4. Point each slave at the master

slave-1:

docker exec -it mysql-slave-1 mysql -uroot -ppassword
Enter fullscreen mode Exit fullscreen mode 
CHANGE MASTER TO
  MASTER_HOST='mysql-master',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='replpass',
  MASTER_AUTO_POSITION=1;

START SLAVE;
Enter fullscreen mode Exit fullscreen mode

slave-2:

docker exec -it mysql-slave-2 mysql -uroot -ppassword
Enter fullscreen mode Exit fullscreen mode 
CHANGE MASTER TO
  MASTER_HOST='mysql-master',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='replpass',
  MASTER_AUTO_POSITION=1;

START SLAVE;
Enter fullscreen mode Exit fullscreen mode

💡 MySQL 8.0.20 uses the legacy CHANGE MASTER TO / START SLAVE syntax. From 8.0.22 onward it's CHANGE REPLICATION SOURCE TO / START REPLICA.

5. Verify replication is actually running 

SHOW SLAVE STATUS\G
Enter fullscreen mode Exit fullscreen mode

You want to see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Enter fullscreen mode Exit fullscreen mode

Quick one-liner for a health check:

docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "SHOW SLAVE STATUS\G" \
  | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
Enter fullscreen mode Exit fullscreen mode
Status Meaning
Slave_IO_Running: Connecting Can't reach the master — check the network/hostname
Slave_IO_Running: No Check Last_IO_Error (often an auth issue)
Slave_SQL_Running: No Check Last_SQL_Error (usually a data conflict)

6. Lock the slaves down to read-only

Now that replication is confirmed working, enforce read-only on each slave:

SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;
Enter fullscreen mode Exit fullscreen mode

This blocks direct client writes while still letting the replication thread apply changes coming from the master. Note it won't survive a container restart on its own — either re-run it, or bake it into a mounted my.cnf once you're confident replication is stable.

7. Put it through its paces

Basic write → read:

-- on master
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO t1 VALUES (1, 'hello');
Enter fullscreen mode Exit fullscreen mode 
-- on each slave
USE testdb;
SELECT * FROM t1;
Enter fullscreen mode Exit fullscreen mode

Bulk insert + row count comparison:

-- on master
CREATE TABLE t2 (id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t2 (val)
SELECT CONCAT('bulk-', seq)
FROM (
  SELECT a.N + b.N*10 + c.N*100 + 1 AS seq
  FROM (SELECT 0 N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  CROSS JOIN (SELECT 0 N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
  CROSS JOIN (SELECT 0 N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
) t;
Enter fullscreen mode Exit fullscreen mode 
docker exec -it mysql-master  mysql -uroot -ppassword -e "SELECT COUNT(*) FROM testdb.t2;"
docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "SELECT COUNT(*) FROM testdb.t2;"
docker exec -it mysql-slave-2 mysql -uroot -ppassword -e "SELECT COUNT(*) FROM testdb.t2;"
Enter fullscreen mode Exit fullscreen mode

All three counts should match.

Checksum comparison (catches data drift a row count would miss):

docker exec -it mysql-master  mysql -uroot -ppassword -e "CHECKSUM TABLE testdb.t2;"
docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "CHECKSUM TABLE testdb.t2;"
docker exec -it mysql-slave-2 mysql -uroot -ppassword -e "CHECKSUM TABLE testdb.t2;"
Enter fullscreen mode Exit fullscreen mode

UPDATE / DELETE propagation:

-- on master
UPDATE testdb.t2 SET val = 'updated-row' WHERE id = 1;
DELETE FROM testdb.t2 WHERE id = 2;
Enter fullscreen mode Exit fullscreen mode 
-- on slaves
SELECT * FROM testdb.t2 WHERE id IN (1,2);
Enter fullscreen mode Exit fullscreen mode

GTID set comparison (the most rigorous check available):

docker exec -it mysql-master  mysql -uroot -ppassword -e "SELECT @@GLOBAL.GTID_EXECUTED;"
docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "SELECT @@GLOBAL.GTID_EXECUTED;"
docker exec -it mysql-slave-2 mysql -uroot -ppassword -e "SELECT @@GLOBAL.GTID_EXECUTED;"
Enter fullscreen mode Exit fullscreen mode

Replication lag under load:

# terminal 1 — generate load on the master
docker exec -it mysql-master mysql -uroot -ppassword -e "
USE testdb;
$(for i in $(seq 1 500); do echo "INSERT INTO t2 (val) VALUES ('load-test-$i');"; done)
"
Enter fullscreen mode Exit fullscreen mode 
# terminal 2 — watch lag on a slave live
watch -n 1 'docker exec mysql-slave-1 mysql -uroot -ppassword -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "Seconds_Behind_Master|Slave_IO_Running|Slave_SQL_Running"'
Enter fullscreen mode Exit fullscreen mode

Confirm the read-only lock actually holds:

docker exec -it mysql-slave-1 mysql -uroot -ppassword -e "USE testdb; INSERT INTO t2 (val) VALUES ('should-fail');"
Enter fullscreen mode Exit fullscreen mode

Expected:

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
Enter fullscreen mode Exit fullscreen mode

Master restart recovery:

docker stop mysql-master
docker start mysql-master
Enter fullscreen mode Exit fullscreen mode

Insert new data on the master once it's back, then confirm Slave_IO_Running recovers to Yes on its own and the slaves catch up.

Troubleshooting cheat sheet

Symptom Cause Fix
Access denied for user 'root'@'localhost' right after first startup Data dir wasn't empty on first init, so MYSQL_ROOT_PASSWORD never got applied docker-compose down, wipe the affected data-N/mysql, docker-compose up -d again
ERROR 1290: --super-read-only option in docker logs during bootstrap Read-only flags set at startup blocked the entrypoint's temp init server Remove read-only flags from command:; apply SET GLOBAL read_only/super_read_only = ON manually after startup
Slave_IO_Running: Connecting Slave can't reach mysql-master by hostname Make sure every service is on the same Docker network; don't mix network_mode: host with bridge networking
Slave_IO_Running: No, auth error in Last_IO_Error Wrong replication credentials, or caching_sha2_password handshake issue Recreate repl with IDENTIFIED WITH mysql_native_password; set --default-authentication-plugin=mysql_native_password
Nodes in separate compose files can't see each other Each docker-compose up creates its own isolated default network Merge all three services into one docker-compose.yml

A note on security

password and replpass here are throwaway credentials for local testing — swap them for real secrets before this touches anything beyond your laptop. Also scope 'repl'@'%' down to your actual replica subnet in production rather than allowing any host, and consider TLS for replication traffic.

That's a full working replica set: one master accepting writes, two read replicas staying in sync via GTID, and a battery of checks to prove it's actually consistent rather than just "looks running." Happy to follow up with a piece on failover / promoting a replica to master if there's interest.

Top comments (0)