MaxScale with MariaDB Galera Cluster in Docker Containers

June 3, 2020 7-minute read

MaxScale • Galera • MariaDB • Docker

Introduction

MaxScale has a module called galeramon that help us to monitor and interact with a MariaDB Galera Cluster. In a previous note we installed and configured MaxScale with MariaDB Galera Cluster in VM Instances this time we are going to do the same but using Docker Containers instead.

Prerequisites

We need a MariaDB Galera Cluster cluster running in Docker. For this exercise we are going to use our Google Cloud Platform installation for MariaDB Galera Cluster in Docker Containers.

MaxScale Container

We are going to create our container using the MaxScale image we build previously in MaxScale with MariaDB Replication in Docker containers.

Create the Container

Create the container patomaxgalera using our image patomx/patomaxscale and attach it to our network:

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

pato@patocontainer ~ $ docker run -d --name patomaxgalera --network pato-net patomx/patomaxscale
184d5e7bc769ef28d42770d85b2d0f33ffa6a2a9690aa747ad0b6e5f00b512c0
pato@patocontainer ~ $

MaxScale Configuration

Create a Database User for MaxScale

Access any of the Galera nodes, connect to the database and create the MaxScale monitor user maxpato with the following privileges:

pato@patocontainer ~ $ docker exec -it patomariag1 mysql

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

MariaDB [(none)]> GRANT REPLICATION CLIENT, SHOW DATABASES ON *.* TO 'maxpato'@'%';
Query OK, 0 rows affected (0.012 sec)

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

Create a Galera Cluster Configuration File

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

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

Define the host and port of the servers in the replication

[Pato-Node0]
type=server
address=patomariagm
port=3306
protocol=MariaDBBackend

[Pato-Node1]
type=server
address=patomariag1
port=3306
protocol=MariaDBBackend

[Pato-Node2]
type=server
address=patomariag2
port=3306
protocol=MariaDBBackend

Define the MaxScale monitor, using module galeramon as this is a Galera Cluster replication.
Set the user and password for the monitoring user.

[Pato-Monitor]
type=monitor
module=galeramon
servers=Pato-Node0,Pato-Node1,Pato-Node2
user=maxpato
password=********
monitor_interval=2000

Define the Service for read write split between Node0 and Node1 listening on port 4006.

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

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

Define the Service for read only on Node2 with connection on port 4008.

[RO-Service]
type=service
router=readconnroute
servers=Pato-Node2
user=maxpato
password=********
router_options=slave

[RO-Listener]
type=listener
service=RO-Service
protocol=MariaDBClient
port=4008

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 previously 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-galera.cnf patomaxgalera:/etc/maxscale.cnf
pato@patocontainer ~/maxscale $ docker restart patomaxgalera
patomaxgalera
pato@patocontainer ~/maxscale $

Validate MaxScale is Running

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

pato@patocontainer ~/maxscale $ docker exec -it patomaxgalera bash
root@184d5e7bc769:/# maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
Pato-Node0         | patomariagm     |  3306 |           0 | Master, Synced, Running
Pato-Node1         | patomariag1     |  3306 |           0 | Slave, Synced, Running
Pato-Node2         | patomariag2     |  3306 |           0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------

MaxScale Functionalities

Single Access Point and Load Balancing

As we have now our MaxScale running we no longer need to connect directly to the database instances.
For remote connections we need to point our client or application to the MaxScale instance in port 4006 for read write activities balanced between Node0 and Node1:

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

MariaDB [(none)]> select @@hostname ;
+--------------+
| @@hostname   |
+--------------+
| 3f3ffc1505f6 |
+--------------+
1 row in set (0.001 sec)

or connect to port 4008 for readonly connection to Node2 only:

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

MariaDB [(none)]> select @@hostname ;
+--------------+
| @@hostname   |
+--------------+
| 282d806c486f |
+--------------+
1 row in set (0.001 sec)

We can see we are connecting to the MaxScale server and we are redirected to the clusters nodes according our configuration.

Monitoring that Nodes are in Sync

As mentioned before, one particularity of galeramon monitor is the capability to monitor when cluster node are in sync:

pato@patocontainer ~/maxscale $ docker exec -it patomaxgalera bash
root@184d5e7bc769:/# maxadmin -pmariadb list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
Pato-Node0         | patomariagm     |  3306 |           0 | Master, Synced, Running
Pato-Node1         | patomariag1     |  3306 |           0 | Slave, Synced, Running
Pato-Node2         | patomariag2     |  3306 |           0 | Slave, Synced, Running
-------------------+-----------------+-------+-------------+--------------------

We can see the Galera nodes are not only marked as Running but also Synced.

Assign Master Slave Roles

The other functionality is to assign Master and Slave roles inside MaxScale so we can tell which is the bootstrap/donor node.

By default, the Galera Monitor will choose the node with the lowest wsrep_local_index value as the master.

Let’s issue maxctrl list servers command:

pato@patocontainer ~ $ docker exec -it patomaxgalera maxctrl list servers
+----------------------------------------------------------------------------------+
¦ Server     ¦ Address     ¦ Port ¦ Connections ¦ State                   ¦ GTID   ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node0 ¦ patomariagm ¦ 3306 ¦ 0           ¦ Master, Synced, Running ¦ 1-1-13 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-13 ¦
+------------+-------------+------+-------------+-------------------------+--------+
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-13 ¦
+------------+-------------+------+-------------+-------------------------+--------+

As we can see the State column shows the roles assigned and it’s also shown the shared GTID of the cluster.

Master FailBack

Now we are going to test the roles behavior when the Master nodes fails and returns.

Stop the designated container patomariagm:

pato@patocontainer ~ $ docker stop patomariagm
patomariagm

and validate the state issuing maxctrl list servers command:

pato@patocontainer ~ $ docker exec -it patomaxgalera maxctrl list servers
+----------------------------------------------------------------------------------+
¦ Server     ¦ Address     ¦ Port ¦ Connections ¦ State                   ¦ GTID   ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node0 ¦ patomariagm ¦ 3306 ¦ 0           ¦ Down                    ¦        ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0           ¦ Master, Synced, Running ¦ 1-1-15 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-15 ¦
+------------+-------------+------+-------------+-------------------------+--------+

our server is marked as Down and the next server is promoted to Master. We can see the change and switch detected in the MaxScale log:

pato@patocontainer ~ $ docker logs patomaxgalera | tail

2020-06-03 21:52:42   notice : Server changed state: Pato-Node0[patomariagm:3306]: master_down. [Master, Synced, Running] -> [Down]
2020-06-03 21:52:42   notice : Server changed state: Pato-Node1[patomariag1:3306]: new_master. [Slave, Synced, Running] -> [Master, Synced, Running]
2020-06-03 21:52:42   notice : Master switch detected: lost a master and gained a new one

Then if we start the patomariagm server it will recover its Master status:

pato@patocontainer ~ $ docker start patomariagm
patomariagm

and validate issuing maxctrl list servers command:

pato@patomaxscale:~$ maxctrl list servers
+----------------------------------------------------------------------------------+
¦ Server     ¦ Address     ¦ Port ¦ Connections ¦ State                   ¦ GTID   ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node0 ¦ patomariagm ¦ 3306 ¦ 0           ¦ Master, Synced, Running ¦ 1-1-16 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-16 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-16 ¦
+------------+-------------+------+-------------+-------------------------+--------+

We can see the change is detected in the MaxScale log:

pato@patocontainer ~ $ docker logs patomaxgalera | tail

2020-06-03 22:03:11   notice : Server changed state: Pato-Node0[patomariag1:3306]: master_up. [Down] -> [Master, Synced, Running]
2020-06-03 22:03:11   notice : Server changed state: Pato-Node1[patomariagm:3306]: new_slave. [Master, Synced, Running] -> [Slave, Synced, Running]

We validated the Master role was reassigned to the original node

Conclusion

We successfully installed MaxScale in our Docker environment running in a Google Cloud virtual machine and configure it to monitor our MariaDB Galera cluster. And we were able to validate the described functionalities like Sync detection, Role assignment and Master Failback.