[Docker container] Using Master Slave Approach by MySQL 5.7

Before we jump in Lets first introduce about database replication.

What is database replication

There are many replication process out there like

  • Master-slave replication (we will discuss this today)
  • Master-master replication
  • Group replication
  • Multi-master cluster

What is the advantage of it?

Scale able: When you think your app performing slow for fetching data you can instantly increase another replication instantly.

Easy distribution: You can use in your local database by remote connection without permanent connection to the server database.

Security: If you remember RAID 01 algorithm this almost working like same. by doing replication process losing data possibility will be less.

How does it work?

So master is the main database and whenever any writing (insert, update, delete) happened it create a binary log file and all the Slave database will A sync with master log file and update there records also all the slave database always monitor binary log file. And if any changes happen it will update the instantly.

Which database we will use?

MySQL 5.7

Why MySQL?

Lets Start Learning

We will use MySQL 5.7 image as database

Don’t worry about docker file we will use docker-compose.yml

In the host machine create a folder called masterSlave.

shell > sudo mkdir masterSlave

Go inside that folder

shell > cd masterSlave

Create a file called docker-compose.yml.

shell > sudo nano docker-compose.yml

Copy this entire code inside docker-compose.yml file.

docker-compose.yml

If you noticed here I have registered two MySQL container.
1. mysql_db_master
2. mysql_db_slave

for time shortage I am going to use one replication from master which is mysql_db_slave.

mysql_db_master assigned port is 3306

mysql_db_slave assigned port is 3307

NOTE: we mount the folder with mysql_slave in both container. which will need in a moment later.

Make an .env file same folder.

shell > sudo nano .env

Copy this variables inside .env file

MYSQL_ROOT_PASSWORD=admin
MYSQL_USER=admin
MYSQL_PASSWORD=123
MYSQL_SLAVE_ONE_ROOT_PASSWORD=admin
MYSQL_SLAVE_ONE_USER=slave
MYSQL_SLAVE_ONE_PASSWORD=mysqlpwd
DOCKER_HOST_IP=192.168.0.107

WARNING: Change this credential info when you deploy in production.

We will use same user name password for both container.

Now Let the game begin

shell > docker-composer up -d

After finishing the installation we need to configure the database for communication each other. So that we have to create a binary log file.

For this we have to configure mysql_db_master first.

shell > docker-compose exec mysql_db_master /bin/bash

At first we have to edit my.cnf file.

shell > nano /etc/mysql/my.cnf

In here you have to make a server id. Because for replication every server needs to have unique id.

So now add this code inside my.cnf.

[mysqld] 
log-bin=mysql-bin
server-id=1

Save and exit the file.

Restart the mysql_db_master container.

After successfully restart go inside mysql_db_master again by

shell > docker-compose exec mysql_db_master /bin/bash

Go inside /var/slavedb

shell > cd /var/slavedb

Login MySQL by

shell >mysql -u root -p admin

After you logged in create user for slave account by using this slave will communicate with master

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY 'password'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

Check master status

mysql > SHOW MASTER STATUS;# output+------------------+----------+--------------+------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB
| +------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql
| +------------------+----------+--------------+------------------+

Copy the output and save it any file.

Exit the MySQL

mysql > exit;

Dump the binary file. make sure you are inside /var/slavedb

shell > mysqldump -u root -p admin --all-databases --master-data > dbdump.db

You will see a dbdump.db file inside /var/slavedb folder

Remember! we mount both container folder the /var/slavedb to ./mysql_slave/ in host directory. Which also available inside mysql_db_slave container.

Now Lets Start Configuring Slave

shell > docker-compose exec mysql_db_slave /bin/bash
shell > nano /etc/mysql/my.cnf

Add this code inside my.cnf

[mysqld] 
server-id=2

NOTE: No need to use log-bin here, because for slave database its not required.

Restart the mysql_db_slave container.

Go inside container again and go inside slavedb folder

shell > docker-compose exec mysql_db_slave /bin/bash
shell > cd /var/slavedb

Login myql

shell > mysql -u root -p admin

Copy this code inside MySQL

CHANGE MASTER TO    
MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;

recorded_log_position is the position number you get from by running
SHOW MASTER STATUS; inside mysql_db_master.

mysql > CHANGE MASTER TO
-> MASTER_HOST='192.168.0.104',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='slave',
-> MASTER_LOG_FILE='dbdump.db',
-> MASTER_LOG_POS=73;
mysql > START SLAVE;mysql> SHOW SLAVE STATUS\G# Output
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.104
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dbdump.db
Read_Master_Log_Pos: 73
Relay_Log_File: fdb94d9d565d-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: dbdump.db
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate .........................
# stop slave module for a moement
mysql > STOP SLAVE;

You are done. Exit the MySQL now.

Import the dbdump.db file inside slave database.

mysql > mysql -u root -p admin < dbdump.db# Login mysql server again
shell > mysql -u root -p admin
mysql > START SLAVE;

Great! you are done now. Slave database now auto sync whenever any new write added inside Master database.

Master on left side Slave on right side

Happy DevOpsing! 😁

Love to work with new technologies, explore new challenges and watch Movies & Anime