Automated MariaDB Replication using Docker

Implementing a master-master or master-slave MariaDB replication is always a challenge. I Automated this process using docker containers.

Let’s review what is MariaDB replication, and why this is important to have a master-master or master-slave replication, then explain how we can automate it using docker containers.

Firstly, you are able to find below codes and explanation on my GitHub repository, also There is another repository related to MySQL replication using Docker which is like MariaDB with different codes.

Secondly, You can contribute to this project and add more features.

What is MariaDB?!

MariaDB Database Service is a fully managed database service to deploy cloud-native applications. It’s a fork of MySQL.

What is replication?

The terms master and slave have historically been used in replication, but the terms terms primary and replica are now preferred. The old terms are used throughout the documentation, and in MariaDB commands, although MariaDB 10.5 has begun the process of renaming.

Replication is a feature allowing the contents of one or more servers (called masters) to be mirrored on one or more servers (called slaves).

You can exert control over which data to replicate. All databases, one or more databases, or tables within a database can each be selectively replicated.

Replication is used in a number of common scenarios. Uses include:

Scalability: By having one or more slave servers, reads can be spread over multiple servers, reducing the load on the master.

Data analysis: Analyzing data may have too much of an impact on a master server, and this can similarly be handled on a slave server.

Backup assistance: Backups can more easily be run if a server is not actively changing the data. A common scenario is to replicate the data to slave, which is then disconnected from the master with the data in a stable state.

Distribution of data: Instead of being connected to a remote master, it’s possible to replicate the data locally and work from this data instead.

Common Replication Setups

Master-Slave:

MariaDB replication is the process by which a single data set, stored in a MariaDB database, will be live-copied to a second server. This configuration, called “master-slave” replication, is a typical setup.

Master-Master:

MariaDB master-master replication allows data to be copied from either server to the other one. This subtle but important difference allows us to perform MariaDB read or writes from either server. This configuration adds redundancy and increases efficiency when dealing with accessing the data.

Get started with docker-compose:

We need to deploy 2 container for our scenario, so lets create a file named docker-compose-mariadb.yaml, and put these codes to it:

version: '3'services:
mariadb-master:
container_name: $FIRST_DB_NAME
image: mariadb:latest
restart: unless-stopped
volumes:
- 'db_master_data:/var/lib/mysql'
- './first-host.cnf:/etc/mysql/conf.d/mysql.cnf'
environment:
MARIADB_ROOT_PASSWORD: $FIRST_ROOT_PASSWORD
ports:
- "$IP_ADDR:3306:3306"
networks:
- db-stack
mem_limit: 2G
labels:
app.docker.created: "Hossein Yousefi"
app.docker.env: "production"
app.docker.service: "database"
mariadb-slave:
container_name: $SECOND_DB_NAME
image: mariadb:latest
restart: unless-stopped
volumes:
- 'db_slave_data:/var/lib/mysql'
- './second-host.cnf:/etc/mysql/conf.d/mysql.cnf'
environment:
MARIADB_ROOT_PASSWORD: $SECOND_ROOT_PASSWORD
ports:
- "$IP_ADDR:3307:3306"
networks:
- db-stack
mem_limit: 2G
labels:
app.docker.created: "Hossein Yousefi"
app.docker.env: "production"
app.docker.service: "database"
volumes:
db_master_data:
name: db_master_data
driver: local
db_slave_data:
name: db_slave_data
driver: local
networks:
db-stack:
name: db-stack
driver: bridge

We use different variables to facilitate our job. The most important thing is that the master port is published on 3306, and the slave is on 3307.

there are two configuration files for each of them which we need to create them. For master, we create a file called first-host.cnf:

[mysqld]
default_authentication_plugin= mysql_native_password
# these configs are mandatory, Don't change them.
# able to add other configs at the end of this file.
bind-address = 0.0.0.0
log-bin = master-bin
binlog_format = mixed
server-id = 1

our second file is second-host.cnf:

[mysqld]
default_authentication_plugin= mysql_native_password
# these configs are mandatory, Don't change them.
# able to add other configs at the end of this file.
bind-address = 0.0.0.0
log_bin = slave-bin
server-id = 2

These docker-compose should only be implemented by it’s script, otherwise it doesn’t work because there are several variables which should be specified.

The deploy script is called mariadb-deployment.sh:

#!/bin/bash
# written by Hosein Yousefi <yousefi.hosein.o@gmail.com>
# GITHUB https://github.com/hosein-yousefii
# Automated script to replicate 2 instances of MariaDB
# Default method is master-slave You are able to change
# the method by specifying it on the command line or
# with REPLICATION_METHOD variable.
# FORINSTANCE:
# export REPLICATION_METHOD=master-master
master-slave() {echo
echo starting deploying...
echo
export FIRST_DB_NAME=${MARIADB_FIRST_DB_NAME:-'db-master'}
export SECOND_DB_NAME=${MARIADB_SECOND_DB_NAME:-'db-slave'}
export FIRST_REPL_USER=${MARIADB_FIRST_REPLICATION_USER:-'repl'}export FIRST_REPL_PASSWORD=${MARIADB_FIRST_REPLICATION_PASSWORD:-'qazwsx'}export FIRST_ROOT_PASSWORD=${MARIADB_FIRST_ROOT_PASS:-'qazwsx'}
export SECOND_ROOT_PASSWORD=${MARIADB_SECOND_ROOT_PASS:-'qazwsx'}

export FIRST_HOST=${MARIADB_FIRST_HOST:-'db-master'}
export SECOND_HOST=${MARIADB_SECOND_HOST:-'db-slave'}

export IP_ADDR=${DOCKER0_IP:-$(ip a show dev docker0 |grep inet|awk '{print $2}'|awk -F\/ '{print $1}')}
docker-compose -f docker-compose-mariadb.yaml up -decho
echo waiting 30s for containers to be up and running...
echo Implementing mariadb master slave replication...
sleep 30
echo
# Create user on master database.
docker exec $FIRST_HOST \
mysql -u root --password=$FIRST_ROOT_PASSWORD \
--execute="create user '$FIRST_REPL_USER'@'%' identified by '$FIRST_REPL_PASSWORD';\
grant replication slave on *.* to '$FIRST_REPL_USER'@'%';\
flush privileges;"
# Get the log position and name.
result=$(docker exec $FIRST_HOST mysql -u root --password=$FIRST_ROOT_PASSWORD --execute="show master status;")
log=$(echo $result|awk '{print $5}')
position=$(echo $result|awk '{print $6}')
# Connect slave to master.
docker exec $SECOND_HOST \
mysql -u root --password=$SECOND_ROOT_PASSWORD \
--execute="stop slave;\
reset slave;\
CHANGE MASTER TO MASTER_HOST='$FIRST_HOST', MASTER_USER='$FIRST_REPL_USER', \
MASTER_PASSWORD='$FIRST_REPL_PASSWORD', MASTER_LOG_FILE='$log', MASTER_LOG_POS=$position;\
start slave;\
SHOW SLAVE STATUS\G;"
echo
echo in case of any errors, check if your containers up and running, then rerun this script.
echo
echo The master is running on $IP_ADDR:3306,
echo The slave is running on $IP_ADDR:3307.
echo
}
master-master() {echo
echo starting deploying...
echo
export FIRST_DB_NAME=${MARIADB_FIRST_DB_NAME:-'db-master1'}
export SECOND_DB_NAME=${MARIADB_SECOND_DB_NAME:-'db-master2'}
export SECOND_REPL_USER=${MARIADB_SECOND_REPLICATION_USER:-'repl-master2'}
export FIRST_REPL_USER=${MARIADB_FIRST_REPLICATION_USER:-'repl-master1'}
export FIRST_REPL_PASSWORD=${MARIADB_FIRST_REPLICATION_PASSWORD:-'qazwsx'}
export SECOND_REPL_PASSWORD=${MARIADB_SECOND_REPLICATION_PASSWORD:-'qazwsx'}
export FIRST_ROOT_PASSWORD=${MARIADB_FIRST_ROOT_PASS:-'qazwsx'}
export SECOND_ROOT_PASSWORD=${MARIADB_SECOND_ROOT_PASS:-'qazwsx'}
export FIRST_HOST=${MARIADB_FIRST_HOST:-'db-master1'}
export SECOND_HOST=${MARIADB_SECOND_HOST:-'db-master2'}
export IP_ADDR=${DOCKER0_IP:-$(ip a show dev docker0 |grep inet|awk '{print $2}'|awk -F\/ '{print $1}')}docker-compose -f docker-compose-mariadb.yaml up -decho
echo waiting 30s for containers to be up and running...
echo Implementing mariadb master master replication...
sleep 30
echo
# Create user on master database.
docker exec $FIRST_HOST \
mysql -u root --password=$FIRST_ROOT_PASSWORD \
--execute="create user '$FIRST_REPL_USER'@'%' identified by '$FIRST_REPL_PASSWORD';\
grant replication slave on *.* to '$FIRST_REPL_USER'@'%';\
flush privileges;"
docker exec $SECOND_HOST \
mysql -u root --password=$SECOND_ROOT_PASSWORD \
--execute="create user '$SECOND_REPL_USER'@'%' identified by '$SECOND_REPL_PASSWORD';\
grant replication slave on *.* to '$SECOND_REPL_USER'@'%';\
flush privileges;"
# Get the log position and name.
master1_result=$(docker exec $FIRST_HOST mysql -u root --password=$FIRST_ROOT_PASSWORD --execute="show master status;")
master1_log=$(echo $master1_result|awk '{print $5}')
master1_position=$(echo $master1_result|awk '{print $6}')
master2_result=$(docker exec $SECOND_HOST mysql -u root --password=$SECOND_ROOT_PASSWORD --execute="show master status;")
master2_log=$(echo $master2_result|awk '{print $5}')
master2_position=$(echo $master2_result|awk '{print $6}')
# Connect slave to master.
docker exec $SECOND_HOST \
mysql -u root --password=$SECOND_ROOT_PASSWORD \
--execute="stop slave;\
reset slave;\
CHANGE MASTER TO MASTER_HOST='$FIRST_HOST', MASTER_USER='$FIRST_REPL_USER', \
MASTER_PASSWORD='$FIRST_REPL_PASSWORD', MASTER_LOG_FILE='$master1_log', MASTER_LOG_POS=$master1_position;\
start slave;\
SHOW SLAVE STATUS\G;"
docker exec $FIRST_HOST \
mysql -u root --password=$FIRST_ROOT_PASSWORD \
--execute="stop slave;\
reset slave;\
CHANGE MASTER TO MASTER_HOST='$SECOND_HOST', MASTER_USER='$SECOND_REPL_USER', \
MASTER_PASSWORD='$SECOND_REPL_PASSWORD', MASTER_LOG_FILE='$master2_log', MASTER_LOG_POS=$master2_position;\
start slave;\
SHOW SLAVE STATUS\G;"
sleep 2
echo
echo ################### SECOND status
docker exec $SECOND_HOST \
mysql -u root --password=$SECOND_ROOT_PASSWORD \
--execute="SHOW SLAVE STATUS\G;"
sleep2
echo
echo ################### FIRST status
docker exec $FIRST_HOST \
mysql -u root --password=$FIRST_ROOT_PASSWORD \
--execute="SHOW SLAVE STATUS\G;"
sleep 2
echo
echo in case of any errors, check if your containers up and running, then rerun this script.
echo
echo The master1 is running on $IP_ADDR:3306,
echo The master2 is running on $IP_ADDR:3307.
echo
}METHOD=${REPLICATION_METHOD:-'master-slave'}case ${METHOD} inmaster-master)
master-master
;;
master-slave)
master-slave
;;
*)
echo """
Automated script to replicate 2 instances of MariaDB
Default method is master-slave You are able to change
the method by specifying it on the command line or
with REPLICATION_METHOD variable.
FORINSTANCE:
export REPLICATION_METHOD=master-master
""";;esac

Let’s discuss master-slave configuration:

There are several variables which is your replication configuration, and all of them have a default value, so if you don’t specify any variable it will work correctly but, it would be a good idea to change some of them, for instance your root password or replication user and password. To do that you can export specified below variables:

(For test environment it’s not necessary to set any variables, it works with default values.)

# First DB container name.
export MARIADB_FIRST_DB_NAME='db-master'

# Second DB container name.
export MARIADB_SECOND_DB_NAME='db-slave'

# User on master container for repliation.
export MARIADB_FIRST_REPLICATION_USER='repl'

# Password for replica user on master container.
export MARIADB_FIRST_REPLICATION_PASSWORD='qazwsx'

# Root password for master DB.
export MARIADB_FIRST_ROOT_PASS='qazwsx'

# Root password for slave DB.
export MARIADB_SECOND_ROOT_PASS='qazwsx'

# Master container address (it depends on the container name #"MARIADB_FIRST_DB_NAME" and should be same. You can specify IP addr #instead "NOT RECOMMENDED").
export MARIADB_FIRST_HOST='db-master'

# Slave container address (it depends on the container name #"MARIADB_SECOND_DB_NAME" and should be same. You can specify IP #addr instead "NOT RECOMMENDED").
export MARIADB_SECOND_HOST='db-slave'

# Your docker bridge IP addr on host.
export DOCKER0_IP='172.17.0.1'

After setting some of these variables or none, you can deploy your MariaDB master-slave replication by executing:

./mariadb-deployment.sh

Let’s go for master-master configuration:

First of all You should set a variable to benefit from master-master replication “REPLICATION_METHOD=master-master”.

export REPLICATION_METHOD=master-master

The default value of “REPLICATION_METHOD” is master-slave.

Like master-slave replication, several variables exist for master-master replication too. all of them have a default value, so if you don’t specify any variable it will work correctly but, it would be a good idea to change some of them for instance, your root password or replication user and password. To do that you can export specified below variables:

(For test environment it’s not necessary to set any variables, it works with default values.)

# First DB container name.
export MARIADB_FIRST_DB_NAME='db-master1'

# Second DB container name.
export MARIADB_SECOND_DB_NAME='db-master2'

# User on master1 container for repliation.
export MARIADB_FIRST_REPLICATION_USER='repl-master1'

# User on master2 container for repliation.
export MARIADB_SECOND_REPLICATION_USER='repl-master2'

# Password for replica user on master1 container.
export MARIADB_FIRST_REPLICATION_PASSWORD='qazwsx'

# Password for replica user on master2 container.
export MARIADB_SECOND_REPLICATION_PASSWORD='qazwsx'

# Root password for master1 DB.
export MARIADB_FIRST_ROOT_PASS='qazwsx'

# Root password for master2 DB.
export MARIADB_SECOND_ROOT_PASS='qazwsx'

# Master1 container address (it depends on the container name #"MARIADB_FIRST_DB_NAME" and should be same. You can specify IP addr #instead "NOT RECOMMENDED").
export MARIADB_FIRST_HOST='db-master1'

# Master2 container address (it depends on the container name #"MARIADB_SECOND_DB_NAME" and should be same. You can specify IP #addr instead "NOT RECOMMENDED").
export MARIADB_SECOND_HOST='db-master2'

# Your docker bridge IP addr on host.
export DOCKER0_IP='172.17.0.1'

After setting some of these variables or none, you can deploy your MariaDB master-slave replication by executing:

./mariadb-deployment.sh

Then after minutes you would have your replicated MariaDB.
All these codes are in my GitHub, so you can clone and enjoy!

All contributions are welcomed. If you find this article useful, Stargaze the project GitHub repository. Good luck.

--

--

--

I’m a DevOps engineer who like sharing knowledge, I believe we shouldn’t experience something that other people have experienced it before.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Stratio software journey

Google Images best alternative — Naver Images API

Growing WordPress In The Federal Government

Golang Tutorial #2 : Installing Golang on Linux, Windows and Mac OS

DomeCTF Writeup : The Matrix

Simple TDD in Laravel with 11 steps

Sam learned to code at Sabio and got the career he always dreamed of

EveryLog: the app to effectively manage your projects

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Hosein Yousefi

Hosein Yousefi

I’m a DevOps engineer who like sharing knowledge, I believe we shouldn’t experience something that other people have experienced it before.

More from Medium

What Is Argo CD?

Provision a Single-node Kubernetes Cluster using Ansible on Ubuntu 20.04

Continuous integration with jenkins and other tools(AWS vproject)

GitOps with NSX Advanced Load Balancer and Jenkins