How to create Master-Slave MySQL 8 with docker-compose.yml

This article will be very pratical and simple ;). Below you can see docker-compose.yml , master.cnf, slave.cnf, setup_db.sh files. Feel free to copy-and-paste these files into your project and skip explication about them. I've created them to be self-explanatory

docker-compose.yml

version: "3.5"
services:
  database_master:
    image: mysql:8.0.30
    container_name: "database_master"
    restart: unless-stopped
    ports:
      - 3306:3306
    volumes:
      - mysqldata_master:/var/lib/mysql
      - ./master.cnf:/etc/my.cnf
    environment:
      - MYSQL_ROOT_PASSWORD=S3cret
      - MYSQL_USER=my_db_user
      - MYSQL_DATABASE=my_db
      - MYSQL_PASSWORD=S3cret
    networks:
      - mynetwork
  database_slave:
    image: mysql:8.0.30
    container_name: "database_slave"
    restart: unless-stopped
    depends_on:
      - database_master
    ports:
      - 3307:3306
    volumes:
      - mysqldata_slave:/var/lib/mysql
      - ./slave.cnf:/etc/my.cnf
    environment:
      - MYSQL_ROOT_PASSWORD=S3cret
      - MYSQL_USER=my_db_user
      - MYSQL_DATABASE=my_db
      - MYSQL_PASSWORD=S3cret
    networks:
      - mynetwork
networks:
  mynetwork:
volumes:
  mysqldata_master:
  mysqldata_slave:

master.cnf

[mysqld]
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
server-id=1
log_bin= 1
binlog_format=ROW
binlog_do_db=my_db
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/

slave.cnf

[mysqld]
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
server-id=2
log_bin = 1
binlog_do_db=my_db
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/

setup_db.sh

#!/bin/bash
sql_slave_user='CREATE USER "mydb_slave_user"@"%" IDENTIFIED BY "mydb_slave_pwd"; GRANT REPLICATION SLAVE ON *.* TO "mydb_slave_user"@"%"; FLUSH PRIVILEGES;'
docker exec database_master sh -c "mysql -u root -pS3cret -e '$sql_slave_user'"
MS_STATUS=`docker exec database_master sh -c 'mysql -u root -pS3cret -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`
sql_set_master="CHANGE MASTER TO MASTER_HOST='database_master',MASTER_USER='mydb_slave_user',MASTER_PASSWORD='mydb_slave_pwd',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"
start_slave_cmd='mysql -u root -pS3cret -e "'
start_slave_cmd+="$sql_set_master"
start_slave_cmd+='"'
docker exec database_slave sh -c "$start_slave_cmd"
docker exec database_slave sh -c "mysql -u root -pS3cret -e 'SHOW SLAVE STATUS \G'"

Understading files…

You can save all files in the same folder. After that, you can open a terminal session, go to the folder and type command: docker-compose up

When all containers are already started, you have to open another terminal session, go to the folder and type command: bash setup_bd.sh

If everything goes well, you will see something like that:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: database_master
                  Master_User: mydb_slave_user
                  Master_Port: 3306

Now you can create tables, insert data in my_db database and the data will be available on master and slave database.

Piece of cake!!!!

Some important points:

• master.cnf/slave.cnf: both files were created from original my.cnf file in /etc/my.cnf on mysql container. I've only added server-id , log_bin , binlog_do_db , binlog_format . You have to pay attention binlog_format shoud be present only inside master cnf.
• setup_db.sh: this file contains some MySQL commands to connect MySQL Servers in a Master-slave relation.

If you need some help, feel free to leave a comment below and it will be a pleasure to help you.