Docker: Setup MySQL master-slave

Task: create docker-compose environment to run MySQL master and slave servers.

Implementation:

docker-compose.yml file:

version: '2'
services:

  mysqldb-master:
    image: mysql:5.7
    volumes:
      - ./data/mysql-master:/var/lib/mysql:rw,cached
      - ./config/mysql-master:/etc/mysql/conf.d:rw,cached
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_MASTER_ROOT_PASSWORD}
      - MYSQL_DATABASE=testdb
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testuser
    expose:
      - "3306"
    ports:
      - "3306:3306"
    restart: always

  mysqldb-slave:
    image: mysql:5.7
    volumes:
      - ./data/mysql-slave:/var/lib/mysql:rw,cached
      - ./config/mysql-slave:/etc/mysql/conf.d:rw,cached
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_SLAVE_ROOT_PASSWORD}
      - MYSQL_DATABASE=testdb
      - MYSQL_USER=testuser
      - MYSQL_PASSWORD=testuser
    expose:
      - "3306"
    ports:
      - "3308:3306"
    restart: always
    depends_on:
      - mysqldb-master

  mysql_configure:
    image: mysql:5.7
    environment:
      - MYSQL_MASTER_ROOT_PASSWORD=${MYSQL_MASTER_ROOT_PASSWORD}
      - MYSQL_MASTER_ADDRESS=${MYSQL_MASTER_ADDRESS}

      - MYSQL_SLAVE_ADDRESS=${MYSQL_SLAVE_ADDRESS}
      - MYSQL_SLAVE_ROOT_PASSWORD=${MYSQL_SLAVE_ROOT_PASSWORD}

      - MYSQL_REPLICATION_USER=${MYSQL_REPLICATION_USER}
      - MYSQL_REPLICATION_PASSWORD=${MYSQL_REPLICATION_PASSWORD}
    volumes:
      - ./config/mysql_connector.sh:/tmp/mysql_connector.sh
    command: /bin/bash -x /tmp/mysql_connector.sh
    depends_on:
      - mysqldb-master
      - mysqldb-slave

.env file with parameters:

MYSQL_MASTER_ROOT_PASSWORD=masterpassword
MYSQL_MASTER_ADDRESS=mysqldb-master

MYSQL_SLAVE_ROOT_PASSWORD=slavepassword
MYSQL_SLAVE_ADDRESS=mysqldb-slave

MYSQL_REPLICATION_USER=repluser
MYSQL_REPLICATION_PASSWORD=replpassword

MySQL master.cnf file:

[mysqld]
server-id=1
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql/

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M

skip-host-cache
skip-name-resolve

sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

general_log = on
general_log_file=/var/lib/mysql/sqls.log

MySQL slave.cnf file:

[mysqld]
server-id=2
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M

auto_increment_increment=2
auto_increment_offset=2

read-only=1

slave-skip-errors = 1062
skip-host-cache
skip-name-resolve

sql-mode="ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

general_log = on
general_log_file=/var/lib/mysql/sqls.log

SQL script to configure master-slave pair (mysql_connector.sh):

#!/bin/bash

BASE_PATH=$(dirname $0)

echo "waiting MYSQL.."
sleep 120


echo "stopping slave in SLAVE MYSQL"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "stop slave;";
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "reset slave all;";

echo "creating replication user in MASTER MYSQL"
mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -AN -e "create user '$MYSQL_REPLICATION_USER'@'%';"
mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -AN -e "grant replication slave on *.* to '$MYSQL_REPLICATION_USER'@'%' identified by '$MYSQL_REPLICATION_PASSWORD';"
mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -AN -e "flush privileges;"

echo "getting MASTER MYSQL config"
Master_Position="$(mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -e 'show master status \G' | grep Position | grep -o '[0-9]*')"
Master_File="$(mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -e 'show master status \G' | grep File | sed -n -e 's/^.*: //p')"

echo "set SLAVE to upstream MASTER"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "change master to master_host='$MYSQL_MASTER_ADDRESS',master_user='$MYSQL_REPLICATION_USER',master_password='$MYSQL_REPLICATION_PASSWORD',master_log_file='$Master_File',master_log_pos=$Master_Position;"

echo "start sync: MASTER to SLAVE"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "start slave;"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -e "show slave status \G;"

echo "mysql fine tuning and extra conf"

echo "increasing connection limit"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "set GLOBAL max_connections=2000;"
mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -AN -e "set GLOBAL max_connections=2000;"

echo "disabling sql_mode = ONLY_FULL_GROUP_BY"
mysql --host $MYSQL_SLAVE_ADDRESS -uroot -p$MYSQL_SLAVE_ROOT_PASSWORD -AN -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"
mysql --host $MYSQL_MASTER_ADDRESS -uroot -p$MYSQL_MASTER_ROOT_PASSWORD -AN -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));"

echo "Done..."

To build environment use:

docker-compose up -d --force-recreate --build

As a result you will have master instance (writer) on 3306 port and slave instance (reader) on 3308 port.