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"'`
MS_CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
MS_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='$MS_CURRENT_LOG',MASTER_LOG_POS=$MS_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'"
sql_master_user='CREATE USER "mydb_slave_user"@"%" IDENTIFIED BY "mydb_slave_pwd"; GRANT REPLICATION SLAVE ON *.* TO "mydb_slave_user"@"%"; FLUSH PRIVILEGES;'
docker exec database_slave sh -c "mysql -u root -pS3cret -e '$sql_master_user'"
SL_STATUS=`docker exec database_slave sh -c 'mysql -u root -pS3cret -e "SHOW MASTER STATUS"'`
SL_CURRENT_LOG=`echo $SL_STATUS | awk '{print $6}'`
SL_CURRENT_POS=`echo $SL_STATUS | awk '{print $7}'`
sql_set_slave="CHANGE MASTER TO MASTER_HOST='database_slave',MASTER_USER='mydb_slave_user',MASTER_PASSWORD='mydb_slave_pwd',MASTER_LOG_FILE='$SL_CURRENT_LOG',MASTER_LOG_POS=$SL_CURRENT_POS; START SLAVE;"
start_master_cmd='mysql -u root -pS3cret -e "'
start_master_cmd+="$sql_set_slave"
start_master_cmd+='"'
docker exec database_master sh -c "$start_master_cmd"
docker exec database_master 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;insert into student values(2,'sangram');
insert into student values(3,'sachin');
select * from student;
Check newly inserted records on either server diplaying on another server
Now after confirming do
docker-compose down
lets recreate the mysql instance from where they left do
docker-compose up -d
then on master & 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