MariaDB Galera Cluster in Docker Containers

May 29, 2020 8-minute read

Galera • MariaDB • Docker • Google Cloud

Introduction

We are going to build a 3 node Galera Cluster, 1 Primary and 2 Nodes, using Docker containers in a Google Cloud Platform virtual machine. Previously we have installed and configured a MariaDB Galera Cluster in 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.

Galera Cluster Containers

Create Docker Network

In order to ease the reference between the cluster nodes we are going to create our own docker network of driver type bridge so each container can find the others by name:

pato@patocontainer ~ $ docker network create --driver bridge pato-net
522c4b821356e3b574f9150653adc325d5cb680b6d4fc7f64121d8fc9aa97530

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

Build Primary Container from Customized image

Let’s create our Primary container 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 patomariagm --network pato-net -v patovolgm:/var/lib/mysql patomx/patomariadb
7cb4db2c49a927404ebd26ed0a7c9cbbdae8aa07374bcc4de222249bbaa288c9

pato@patocontainer ~ $ docker stop patomariagm
patomariagm

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

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

Start the container and validate it’s running:

pato@patocontainer ~ $ docker start patomariagm
patomariagm

pato@patocontainer ~ $ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
7cb4db2c49a9        patomx/patomariadb  "docker-entrypoint.s…"   12 seconds ago      Up 10 seconds       3306/tcp            patomariagm

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

Create Objects and Data

The Primary node is expected to be the source of the data we want to replicate in the cluster so let’s create some objects and data for our testing.

Connect to our new created container with a shell session docker exec -it patomariagm bash.

pato@patocontainer ~ $ docker exec -it patomariagm bash

and then connect to MariaDB and create a database, a table and insert a row identifying the hostname and time:

root@7cb4db2c49a9:/# mysql

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

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> create table tg1 (i1 int auto_increment primary key, c2 varchar(20), d3 datetime) ;
Query OK, 0 rows affected (0.056 sec)

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.005 sec)

Build Secondary Nodes for the Cluster

Create another two containers from the same image and validate they are running and volumes were created:

pato@patocontainer ~ $ docker run -d --name patomariag1 --network pato-net -v patovolg1:/var/lib/mysql patomx/patomariadb
5f9ce8bc16d24f59bc25633faf538d5fe5d4886824b51ced1fdc55b87f0c4c2a

pato@patocontainer ~ $ docker run -d --name patomariag2 --network pato-net -v patovolg2:/var/lib/mysql patomx/patomariadb
18dda3dabdf9d867ee0d0289edc8417518c9171ee4890f19f285aef56cc0dbc6

pato@patocontainer ~ $ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
18dda3dabdf9        patomx/patomariadb  "docker-entrypoint.s…"   12 seconds ago      Up 7 seconds        3306/tcp            patomariag2
5f9ce8bc16d2        patomx/patomariadb  "docker-entrypoint.s…"   22 seconds ago      Up 18 seconds       3306/tcp            patomariag1
7cb4db2c49a9        patomx/patomariadb  "docker-entrypoint.s…"   59 minutes ago      Up 59 minutes       3306/tcp            patomariagm

pato@patocontainer ~ $ docker volume ls
DRIVER              VOLUME NAME
local               patovolg1
local               patovolg2
local               patovolgm

Test Connectivity

Access the Primary container and ping the others using the assigned container name:

pato@patocontainer ~ $ docker exec -it patomariagm bash
root@7cb4db2c49a9:/# ping patomariag1
PING patomariag1 (172.19.0.3) 56(84) bytes of data.
64 bytes from patomariag1.pato-net (172.19.0.3): icmp_seq=1 ttl=64 time=0.052 ms
...
root@7cb4db2c49a9:/# ping patomariag2
PING patomariag2 (172.19.0.4) 56(84) bytes of data.
64 bytes from patomariag2.pato-net (172.19.0.4): icmp_seq=1 ttl=64 time=0.079 ms

Stop the Containers

Issue docker stop to shutdown the databases and validate none is running:

pato@patocontainer ~ $ docker stop patomariagm patomariag1 patomariag2
patomariagm
patomariag1
patomariag2
pato@patocontainer ~ $ docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
pato@patocontainer ~ $

Galera Cluster Configuration

As we have our 3 containers ready we need to configure them for Galera replication.

Create Configuration Files

We need to create a local new Galera configuration file for the Primary:

pato@patocontainer ~/galera $ vi galera-init.cnf

and add the following options:

Turn on replication and setup library

[mariadb]
# Galera Replication Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

Define Cluster name and list of member addresses

# Galera Cluster Name and Members
wsrep_cluster_name="patocluster"
wsrep_cluster_address="gcomm://"
# for primary node the list must be empty to initialize a new cluster

Identify this member of the Cluster

# Galera Cluster Member
wsrep_node_name="patomain"
wsrep_node_address="patomariagm"

Define the method for the initial copy of data

# Galera State Snapshot Transfer Full Data Copy
wsrep_sst_method=rsync

Enable Global Transaction ID

# Galera GTID Configuration
wsrep_gtid_mode=ON
wsrep_gtid_domain_id=1

Binary log and Storage Engine configuration

# MariaDB Configuration
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
log_slave_updates=ON
log_bin=galera-bin

Then create another 2 configuration files for the other nodes, with same options as previous one but changing the identification parameters:

pato@patocontainer ~/galera $ vi galera-node1.cnf

wsrep_cluster_address="gcomm://patomariagm,patomariag1,patomariag2"
# Galera Cluster Member
wsrep_node_name="patonode1"
wsrep_node_address="patomariag1"

pato@patocontainer ~/galera $ vi galera-node2.cnf

wsrep_cluster_address="gcomm://patomariagm,patomariag1,patomariag2"
# Galera Cluster Member
wsrep_node_name="patonode2"
wsrep_node_address="patomariag2"

Copy Configuration Files to the Containers

Even when containers are not running we can move files inside them from the Docker host using docker cp command.
We are going to copy the created configuration files to a directory valid for configuration lookup for mysqld in this image, in our case we will use !includedir /etc/mysql/mariadb.conf.d/:

pato@patocontainer ~/galera $ docker cp galera-init.cnf patomariagm:/etc/mysql/mariadb.conf.d/galera.cnf

pato@patocontainer ~/galera $ docker cp galera-node1.cnf patomariag1:/etc/mysql/mariadb.conf.d/galera.cnf

pato@patocontainer ~/galera $ docker cp galera-node2.cnf patomariag2:/etc/mysql/mariadb.conf.d/galera.cnf

Start Containers

In a Galera Cluster the Primary node should be the first one to start in order to initialize the cluster. So we are going to start each node in order and validate how many members are registered in the cluster:

pato@patocontainer ~ $ docker start patomariagm
patomariagm
pato@patocontainer ~ $ docker exec -it patomariagm mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
pato@patocontainer ~ $ docker start patomariag1
patomariag1
pato@patocontainer ~ $ docker exec -it patomariag1 mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
pato@patocontainer ~ $ docker start patomariag2
patomariag2
pato@patocontainer ~ $ docker exec -it patomariag2 mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
pato@patocontainer ~ $

OK our Galera cluster is setup and running and all members has joined!

Modify Configuration for Primary Node

Once the Galera cluster has been initiated we need to modifiy the parameter wsrep_cluster_address from empty (bootstrap) to the list of cluster members, the same as configured in the other nodes:

pato@patocontainer ~/galera $ docker exec -it patomariagm bash
root@7cb4db2c49a9:/# nano /etc/mysql/mariadb.conf.d/galera.cnf

wsrep_cluster_address="gcomm://patomariagm,patomariag1,patomariag2"

Test Replication on Galera Cluster

Now we are going to test that replication is working.

Validate Initial Replication

First we want to validate that the objects and data created before the cluster setup, were replicated to the new nodes:

pato@patocontainer ~ $ docker exec -it patomariag1 bash
root@5f9ce8bc16d2:/# mysql

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> select * from tg1;
+----+--------------+---------------------+
| i1 | c2           | d3                  |
+----+--------------+---------------------+
|  1 | 7cb4db2c49a9 | 2020-05-29 19:44:42 |
+----+--------------+---------------------+
1 row in set (0.000 sec)

pato@patocontainer ~ $ docker exec -it patomariag2 bash
root@18dda3dabdf9:/# mysql

MariaDB [(none)]> use taller
Database changed
MariaDB [taller]> select * from tg1;
+----+--------------+---------------------+
| i1 | c2           | d3                  |
+----+--------------+---------------------+
|  1 | 7cb4db2c49a9 | 2020-05-29 19:44:42 |
+----+--------------+---------------------+
1 row in set (0.088 sec)

Our original data was replicated in every node, then let’s test the future data replication.

Validate Replication Node to Node

As a Galera Cluster allows every node to be updated, we are going to insert a row on each node, identifying the container that issues the insertion with @@hostname and the time of the operation with now():

MariaDB [taller]> insert into tg1 (c2,d3) values (@@hostname,now());
Query OK, 1 row affected (0.398 sec)

and check the result also in every node:

pato@patocontainer ~/galera $ docker exec -it patomariagm mysql -e "select * from taller.tg1"
+----+--------------+---------------------+
| i1 | c2           | d3                  |
+----+--------------+---------------------+
|  1 | 7cb4db2c49a9 | 2020-05-29 19:44:42 |
|  4 | 7cb4db2c49a9 | 2020-05-29 20:35:19 |
|  5 | 5f9ce8bc16d2 | 2020-05-29 20:36:25 |
|  6 | 18dda3dabdf9 | 2020-05-29 20:37:01 |
+----+--------------+---------------------+
pato@patocontainer ~/galera $ docker exec -it patomariag1 mysql -e "select * from taller.tg1"
+----+--------------+---------------------+
| i1 | c2           | d3                  |
+----+--------------+---------------------+
|  1 | 7cb4db2c49a9 | 2020-05-29 19:44:42 |
|  4 | 7cb4db2c49a9 | 2020-05-29 20:35:19 |
|  5 | 5f9ce8bc16d2 | 2020-05-29 20:36:25 |
|  6 | 18dda3dabdf9 | 2020-05-29 20:37:01 |
+----+--------------+---------------------+
pato@patocontainer ~/galera $ docker exec -it patomariag2 mysql -e "select * from taller.tg1"
+----+--------------+---------------------+
| i1 | c2           | d3                  |
+----+--------------+---------------------+
|  1 | 7cb4db2c49a9 | 2020-05-29 19:44:42 |
|  4 | 7cb4db2c49a9 | 2020-05-29 20:35:19 |
|  5 | 5f9ce8bc16d2 | 2020-05-29 20:36:25 |
|  6 | 18dda3dabdf9 | 2020-05-29 20:37:01 |
+----+--------------+---------------------+

As we can see a row was inserted en each node and data was replicated to the other nodes.

Conclusion

We were able to setup a MariaDB Galera Cluster using Docker containers inside a virtual machine from the Google Cloud Platform and validated we can update any table in any node and those changes are replicated to every node.
This is an easy way to build a multinode cluster when you need to test some configuration or replicate an issue without dealing with a more permanent installation.