MariaDB Master Slave Replication in Docker Containers

May 22, 2020 7-minute read

MariaDB • Master Slave • Docker • Google Cloud

Introduction

One of the main features of MariaDB is the Master Slave Replication. As explained in documentation:

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

We are going to create a basic two-node Master Slave Replication using Docker containers in a Google Cloud Platform virtual machine.
Previously we have installed and configured a MariaDB Master Slave Replication on VM Instances, this time we are going to build it with containers.

Prerequisites

A running Docker host in any OS. We are using a virtual machine instance in Google Cloud Platform with Container Optimized OS as it already includes Docker 19.03.

Create MariaDB Containers

Create Network

In order to ease the reference between the cluster nodes we are going to use our own docker network type bridge previously created so both containers can find the other by name:

pato@patocontainer ~ $ docker network ls
NETWORK ID          NAME                DRIVER              SCOPE
...
522c4b821356        pato-net            bridge              local

Build Containers from Customized image

Let’s create our containers with docker run using the customized image patomx/patomariadb. For more information on this image visit My MariaDB Docker Image and Volume.

We will be using the network previously created --network pato-net and we will assign a particular name for the datadir volume -v patovolgm:/var/lib/mysql.

pato@patocontainer ~ $ docker run -d --name patomariadb --network pato-net -v patovoldb:/var/lib/mysql patomx/patomariadb
9480dd3937c18a034406875f9f2e8b3c39dafff65e677052f8bc39d763df2734

pato@patocontainer ~ $ docker run -d --name patomariarp --network pato-net -v patovolrp:/var/lib/mysql patomx/patomariadb
638f5ce36f85c6a64b3b684c59cd5ddf9b6fcd705389fbc4a7face0f674c0a90

pato@patocontainer ~ $ docker stop patomariadb patomariarp
patovoldb
patovolrp

Once created we replicate the data from our customized database to our new containers volume:

pato@patocontainer ~ $ docker run --rm -i -t -v patovolmariadb:/origen -v patovoldb:/destino alpine sh -c "cp -avr /origen/* /destino"
'/origen/aria_log.00000001' -> '/destino/aria_log.00000001'
'/origen/aria_log_control' -> '/destino/aria_log_control'
...
pato@patocontainer ~ $ docker run --rm -i -t -v patovolmariadb:/origen -v patovolrp:/destino alpine sh -c "cp -avr /origen/* /destino"
...

Start the containers and validate they are running:

pato@patocontainer ~ $ docker start patomariadb patomariarp
patomariadb
patomariarp

pato@patocontainer ~ $ docker container ls
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS              PORTS               NAMES
638f5ce36f85        patomx/patomariadb   "docker-entrypoint.s…"   6 minutes ago       Up 18 seconds       3306/tcp            patomariarp
9480dd3937c1        patomx/patomariadb   "docker-entrypoint.s…"   7 minutes ago       Up 19 seconds       3306/tcp            patomariadb


pato@patocontainer ~ $ docker volume ls
DRIVER              VOLUME NAME
local               patovoldb
local               patovolmariadb
local               patovolrp

Master Slave Replication

Configure Replication on Master

Access the newly created Master container with a shell session docker exec -it patomariadb bash and generate the configuration file, defining the unique server_id and the name and format of the binary log:

pato@patocontainer ~ $ docker exec -it patomariadb bash
root@9480dd3937c1:/# nano /etc/mysql/conf.d/replication.cnf

# MariaDB Replication Configuration
[mariadb]
server_id=1
log_bin=master-bin
binlog-format=mixed
log_slave_updates=on

Then connect to the database and create a user for the replication:

pato@9480dd3937c1:/# mysql

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'replicador'@'%' IDENTIFIED BY '********';
Query OK, 0 rows affected (0.007 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.015 sec)

Finally restart the database container for the new configuration to be loaded:

pato@patocontainer ~ $ docker restart patomariadb

Configure Replication on Slave

Access the newly created Slave container with a shell session docker exec -it patomariarp bash and generate the configuration file, defining the unique server_id and the name and format of the binary log:

pato@patocontainer ~ $ docker exec -it patomariarp bash
root@638f5ce36f85:/# nano /etc/mysql/conf.d/replication.cnf

# MariaDB Replication Configuration
[mariadb]
server_id=11
log_bin=slave-bin
binlog-format=mixed
log_slave_updates=on

Finally restart the database container for the new configuration to be loaded:

pato@patocontainer ~ $ docker restart patomariarp

Generate Test Data and Objects

Connect to Master database and generate a table and insert a row:

pato@patocontainer ~ $ docker exec -it --user pato patomariadb bash
pato@9480dd3937c1:/$ mysql

MariaDB [(none)]> create database taller ;
Query OK, 1 row affected (0.012 sec)

MariaDB [(none)]> use taller;
Database changed
MariaDB [taller]> create table r1 (i1 int auto_increment primary key, c1 varchar(20), d1 datetime) ;
Query OK, 0 rows affected (0.085 sec)

MariaDB [taller]> insert into r1 (c1,d1) values ('Before Master Slave',now());
Query OK, 1 row affected (0.021 sec)

MariaDB [taller]> commit ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [taller]> select * from r1;
+----+---------------------+---------------------+
| i1 | c1                  | d1                  |
+----+---------------------+---------------------+
|  1 | Before Master Slave | 2020-05-22 17:49:14 |
+----+---------------------+---------------------+
1 row in set (0.000 sec)

Initial Data Copy

Freeze any activity in Master database:

pato@patocontainer ~ $ docker exec -it --user pato patomariadb bash
pato@9480dd3937c1:~$ mysql

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.013 sec)

Then backup the Master database:

pato@9480dd3937c1:~$ mysqldump --all-databases > masterfull.sql
pato@9480dd3937c1:~$ mysql

Get the binary log coordinates for the starting point of replication:

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |     1029 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

Unfreeze activity in Master:

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)

Restore Backup in Slave

Transfer Backup from the Master node to the Slave node:

pato@patocontainer ~ $ docker cp patomariadb:/home/pato/masterfull.sql .

pato@patocontainer ~ $ docker cp masterfull.sql patomariarp:/home/pato

Restore the backup:

pato@patocontainer ~ $ docker exec -it --user pato patomariarp bash
pato@638f5ce36f85:~$ mysql < masterfull.sql

Start Replication

Configure Slave database with the binlog position master-bin.000001 - 1029 from the initial copy and then modify the replication to use GTID position:

pato@638f5ce36f85:~$ mysql

MariaDB [(none)]> CHANGE MASTER TO
    ->    MASTER_HOST='patomariadb',
    ->    MASTER_USER='replicador',
    ->    MASTER_PASSWORD='********',
    ->    MASTER_PORT=3306,
    ->    MASTER_LOG_FILE='master-bin.000001',
    ->    MASTER_LOG_POS=1029,
    ->    MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.117 sec)

MariaDB [(none)]> CHANGE MASTER TO MASTER_USE_GTID = slave_pos ;
Query OK, 0 rows affected (0.011 sec)

Start the Slave and validate the status:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.024 sec)

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: patomariadb
                   Master_User: replicador
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: master-bin.000001
           Read_Master_Log_Pos: 1029
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 1329
         Relay_Master_Log_File: master-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
...
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-4
...
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
...
1 row in set (0.001 sec)

OK our Master Slave replication is set and running!

Test Master Slave Replication

In order to validate that the replication is working we are going to create one more table and data:

pato@patocontainer ~ $ docker exec -it --user pato patomariadb bash
pato@9480dd3937c1:/$ mysql

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> create table r2 (i2 int auto_increment primary key, c2 varchar(20), d2 datetime) ;
Query OK, 0 rows affected (0.153 sec)

MariaDB [taller]> insert into r2 (c2,d2) values ('After Master Slave',now());
Query OK, 1 row affected (0.018 sec)

MariaDB [taller]> commit;
Query OK, 0 rows affected (0.000 sec)

MariaDB [taller]> select * from r2;
+----+--------------------+---------------------+
| i2 | c2                 | d2                  |
+----+--------------------+---------------------+
|  1 | After Master Slave | 2020-05-22 18:00:10 |
+----+--------------------+---------------------+
1 row in set (0.033 sec)

Verify that the data and objects were replicated from the Master to the Slave, the ones from the initial copy table r1 - Before Master Slave and from the replication table r2 - After Master Slave:

pato@patocontainer ~ $ docker exec -it --user pato patomariarp bash
pato@638f5ce36f85:/$ mysql

MariaDB [(none)]> use taller
Database changed

MariaDB [taller]> select * from r1;
+----+---------------------+---------------------+
| i1 | c1                  | d1                  |
+----+---------------------+---------------------+
|  1 | Before Master Slave | 2020-05-22 17:49:14 |
+----+---------------------+---------------------+
1 row in set (0.026 sec)

MariaDB [taller]> select * from r2;
+----+--------------------+---------------------+
| i2 | c2                 | d2                  |
+----+--------------------+---------------------+
|  1 | After Master Slave | 2020-05-22 18:00:10 |
+----+--------------------+---------------------+
1 row in set (0.000 sec)

OK the data and table were replicated from the Master to the Slave!

Conclusion

We successfully generated and configured a Master Slave Replication using Docker containers inside a virtual machine from the Google Cloud Platform and validated it’s working.
As you can see the basic setup is quite straightforward. After this you can try more functionalities like filtering tables, failover and switchover, and multiple slaves.