Mysql Master Slave Replication using Docker

 

create three files docker-compose.yml & master.cnf & slave.cnf as follows

docker-compose.yml

version: "3.5"

services:

database_master:

image: mysql:8.0.30

stdin_open: true

tty: true

container_name: "database_master"

restart: unless-stopped

ports:

- 3307: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

stdin_open: true

tty: true

container_name: "database_slave"

restart: unless-stopped

depends_on:

- database_master

ports:

- 3308: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/

after this create setup_db.sh as follows

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'"

after creating all these 4 files in a folder on bash prompt you need to run

sudo docker-compose up -d

this will create two container of mysql in docker

after this run bash script as

bash bash setup_db.sh

after this lets test

we will connect to master mysql instance and run

-- master

mysql -h 127.0.0.1 -P 3307 -u root -pS3cret my_db

use my_db;

create table student(id int,name varchar(50));

insert into student values(1,'sagar');

insert into student values(2,'sangram');

select * from student;

Now lets check if this records and table copied in slave mysql instance

mysql -h 127.0.0.1 -P 3308 -u root -pS3cret my_db

use my_db;

show tables;

select * from student;

Now after confirming do

docker-compose down

lets recreate the mysql instance from where they left do

docker-compose up -d

then on slave mysql run

STOP SLAVE IO_THREAD;

reset slave;

then run bash script

bash bash setup_db.sh

now you can reconnect to master and slave mysql instances and

recheck old data is intact & if you add records to student table

in mysql master instance that get copied in slave instance

if you want to delete all mysql docker container & image & volumes do following

docker rm $(docker ps -aq)

docker rmi $(docker images -q)

docker system prune -a --volumes

References:

https://pierreabreu.medium.com/how-to-create-master-slave-mysql-8-with-docker-compose-yml-c137f45e28c7