Search This Blog

2023/08/22

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

No comments:

Post a Comment