MaxScale with MariaDB Replication in Docker containers

May 25, 2020 6-minute read

MaxScale • MariaDB • Docker • Google Cloud

Introduction

When you setup a Galera Cluster or a Master Slave replication you will end up with multiple database containers, but for the applications there is only one database and they must be able to connect and work, independently from the database architecture.
To solve this we need MaxScale which provides a single access point to the database and also come up with other benefits like Load Balancing and Failover.
Let’s setup MaxScale in a Docker container and test its Failover functionalities.

Prerequisites

We need a MariaDB Master Slave replication cluster working in Docker containers.
For this we are going to use our Google Cloud Platform installation for MariaDB replication in Docker containers, please refer to MariaDB Master Slave in Docker Cointainers.

MaxScale Container

Build an Image

We are going to create our MaxScale image by using Ubuntu image, updating the OS, download/install the MaxScale software, expose the listeners ports and start the service.
First create a Dockerfile like this:

FROM ubuntu
RUN apt update &&\
    apt upgrade -y &&\
    apt install -y vim nano net-tools iputils-ping wget
RUN wget https://downloads.mariadb.com/MaxScale/2.4.9/ubuntu/dists/bionic/main/binary-amd64/maxscale-2.4.9-1.ubuntu.bionic.x86_64.deb && \
    dpkg -i maxscale-2.4.9-1.ubuntu.bionic.x86_64.deb; exit 0
RUN apt --fix-broken install -y
EXPOSE 4006 4008
CMD service maxscale start & tail -F /var/log/maxscale/maxscale.log

Then build our image on the directory we created the Dockerfile:

pato@patocontainer ~/maxscale $ docker build --tag patomx/patomaxscale .
...
Successfully built 35d1db931d60
Successfully tagged patomx/patomaxscale:latest

pato@patocontainer ~/maxscale $ docker image ls
REPOSITORY            TAG                 IMAGE ID            CREATED             SIZE
patomx/patomaxscale   latest              35d1db931d60        2 minutes ago       351MB
patomx/patomariadb    latest              143534ffe233        5 days ago          453MB

Create the Container

Create the container patomaxmsr using our previously created image patomx/patomaxscale and attach it to our network:

pato@patocontainer ~ $ docker run -d --name patomaxmsr --network pato-net patomx/patomaxscale
9c7cf7ee2093f84d38a653637f3da41a332eac84964e50f1ec94c1616a0ba058
pato@patocontainer ~ $

MaxScale Configuration

Create a Database User for MaxScale

Connect to the Master database and create the MaxScale monitor user maxpato user with the following privileges:

MariaDB [mysql]> GRANT SELECT ON mysql.* TO 'maxpato'@'%' IDENTIFIED BY '********';
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]> GRANT SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, PROCESS, SHOW DATABASES, EVENT ON *.* TO 'maxpato'@'%';
Query OK, 0 rows affected (0.003 sec)

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

Create a Master-Slave Configuration File

On the docker container create a new file with the following parameters:

pato@patocontainer ~/maxscale $ nano maxscale-masterslave.cnf

Define the host and port of the servers in the replication

[Pato-Master]
type=server
address=patomariadb
port=3306
protocol=MariaDBBackend

[Pato-Slave]
type=server
address=patomariarp
port=3306
protocol=MariaDBBackend

Define the MaxScale monitor, using module mariadbmon as this is a Master-Slave replication.
Set the user and password for the monitoring user.
Setup the parameters for automatic Failover and Rejoin

[Pato-Monitor]
type=monitor
module=mariadbmon
servers=Pato-Master,Pato-Slave
user=maxpato
password=xxxxxxxx
monitor_interval=2000
auto_failover=true
auto_rejoin=true

Define the Service for read write split between Master and Slave listening on port 4006.

[RW-Service]
type=service
router=readwritesplit
servers=Pato-Master,Pato-Slave
user=maxpato
password=********

[RW-Listener]
type=listener
service=RW-Service
protocol=MariaDBClient
port=4006

Define the Service for the Command Line Interface maxadmin and maxctrl listening in localhost port 6603

[CLI]
type=service
router=cli

[CLI-Listener]
type=listener
service=CLI
protocol=maxscaled
address=127.0.0.1
port=6603

Load New Configuration

Copy the previous created file into our MaxScale container configuration /etc/maxscale.cnf and restart the container so it can load the new configuration.

pato@patocontainer ~/maxscale $ docker cp maxscale-masterslave.cnf patomaxmsr:/etc/maxscale.cnf
pato@patocontainer ~/maxscale $ docker restart patomaxmsr
patomaxmsr
pato@patocontainer ~/maxscale $

Validate MaxScale is Running

Connect to MaxScale container and validate it is monitoring the configured databases by issuing the command maxadmin list servers:

pato@patocontainer ~/maxscale $ docker exec -it patomaxmsr bash
root@9c7cf7ee2093:/# maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
Pato-Master        | patomariadb     |  3306 |           0 | Master, Running
Pato-Slave         | patomariarp     |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

and validate services are running and listening:

root@9c7cf7ee2093:/# maxadmin -pmariadb list listeners
Listeners.
---------------------+---------------------+--------------------+-----------------+-------+--------
Name                 | Service Name        | Protocol Module    | Address         | Port  | State
---------------------+---------------------+--------------------+-----------------+-------+--------
RW-Listener          | RW-Service          | MariaDBClient      | ::              |  4006 | Running
CLI-Listener         | CLI                 | maxscaled          | 127.0.0.1       |  6603 | Running
---------------------+---------------------+--------------------+-----------------+-------+--------

Connect to Database using MaxScale

As we have now our MaxScale running we no longer need to connect directly to the Master container.
For remote connections we need to point our client or application to the MaxScale container in port 4006 and activities balanced between Master and Slave:

$ mysql -u remoto -p -h patomaxmsr -P 4006
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

MariaDB [(none)]>

Automatic Functionalities

In our MaxScale configuration file we setup the options auto_failover and auto_rejoin:

auto_failover=true
auto_rejoin=true

With this options MaxScale will help our MariaDB Cluster to automate failover and rejoin activities whenever one node fails. Let’s test those functionalities.

Failover

We are going to stop the Master database to test the automatic failover:

pato@patocontainer ~/maxscale $ docker stop patomariadb

Master goes down and former Slave is promoted to Master role automatically. Check this by issuing the command maxctrl list servers:

pato@patocontainer ~/maxscale $ docker exec -it --user maxscale patomaxmsr bash
maxscale@9c7cf7ee2093:/$ maxctrl list servers
+----------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID    ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Down            ¦ 0-1-40  ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-11-44 ¦
+-------------+-------------+------+-------------+-----------------+---------+

note that GTID is moving on the running node as all updates are performed in the new Master.

Availability

As we connect to the database through the MaxScale server, even if the instance patomariadb Pato-master went down, the users can still work as the connections will be redirected transparently to the instance patomariarp Pato-Slave:

$ mysql -u remoto -p -h patomaxmsr -P 4006
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5

MariaDB [(none)]> select @@hostname;
+--------------+
| @@hostname   |
+--------------+
| 4287cdea4dab |
+--------------+
1 row in set (0.000 sec)

Rejoin

When the server patomariadb is recovered it will automatically rejoin our replication cluster but this time with the Slave role. Check this by issuing the command maxctrl list servers:

pato@patocontainer ~/maxscale $ docker start patomariadb
patomariadb
pato@patocontainer ~/maxscale $ docker exec -it patomaxmsr maxctrl list servers
+----------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID    ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Slave, Running  ¦ 0-11-46 ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-11-46 ¦
+-------------+-------------+------+-------------+-----------------+---------+

Also we can see the global transaction id GTID is again the same in both instances so they are in sync after Rejoin.

Switchover

If everything is OK with the former Master server, we may need to Switchover the Master-Slave roles to the original ones. For this we have to issue manually the command mariadbmon switchover:

pato@patocontainer ~/maxscale $ docker exec -it --user maxscale patomaxmsr bash
maxscale@9c7cf7ee2093:/$ maxctrl call command mariadbmon switchover Pato-Monitor Pato-Master Pato-Slave -t 600000
OK

maxscale@9c7cf7ee2093:/$ maxctrl list servers
+---------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID   ¦
+-------------+-------------+------+-------------+-----------------+--------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-1-48 ¦
+-------------+-------------+------+-------------+-----------------+--------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Slave, Running  ¦ 0-1-48 ¦
+-------------+-------------+------+-------------+-----------------+--------+

After role Switchover we can see the GTID has changed from 0-11-# to 0-1-# (as patomariadb serverid is 1 and patomariarp serverid is 11).

Conclusion

We successfully installed MaxScale in a Docker container and configure it to monitor and control our Master Slave MariaDB cluster.

By connecting to MariaDB through MaxScale we ensure Availability by automating Failover activities when some node in our cluster fails, and also it help us with Load Balancing when all nodes are available.