MaxScale with MariaDB Galera Cluster in VM Instances

May 15, 2020 7-minute read

MaxScale • MariaDB • Galera • Google Cloud

Introduction

MaxScale has a module called galeramon that help us to monitor and interact with a MariaDB Galera Cluster. As described in the Galera Monitor documentation main functionalities are:

It detects whether nodes are a part of the cluster and if they are in sync with the rest of the cluster.

It can also assign master and slave roles inside MaxScale

In a previous note we installed and configured MaxScale for MariaDB Master Slave Replication this time we are going to explore the functionalities of MaxScale for Galera Cluster.

Prerequisites

We need a MariaDB Galera Cluster replication environment. For this exercise we are going to use our installation we build for MariaDB Galera Cluster on virtual machine instances in Google Cloud Platform.

MaxScale Installation

MaxScale Instance

Create a new Virtual Machine with the following parameters:

On Google Cloud Platform - Compute Engine - VM Instances - Create Instance

Name: patomaxgalera
Type: f1-micro (1 vCPU, 0.6 GB memory)
Image: Ubuntu 20.04 LTS
- Standard persistent disk: 10GB
Identity and API access
- Access scopes: Set access for each API
  - Compute Engine: Read Write
  - Storage: Full
Networking
- Hostname: patomaxgalera.databases

Validate the new MaxScale instance and the database instances are up and running.

$ gcloud compute instances list
NAME           ZONE           MACHINE_TYPE  PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP     STATUS
patomariag1    us-central1-f  f1-micro                   10.128.0.48  35.223.249.103  RUNNING
patomariag2    us-central1-f  f1-micro                   10.128.0.49  35.226.17.216   RUNNING
patomariagm    us-central1-f  f1-micro                   10.128.0.47  34.66.218.222   RUNNING
patomaxgalera  us-central1-f  f1-micro                   10.128.0.39  34.72.63.92     RUNNING

MaxScale Software

Connect via ssh to your new instance. Download and install MaxScale software.

pato@patomaxgalera:~$ 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

maxscale-2.4.9-1.ubuntu.b 100%[====================================>]  37.63M  16.5MB/s    in 2.3s

pato@patomaxgalera:~$ sudo dpkg -i maxscale-2.4.9-1.ubuntu.bionic.x86_64.deb
Selecting previously unselected package maxscale.
(Reading database ... 93284 files and directories currently installed.)
Preparing to unpack maxscale-2.4.9-1.ubuntu.bionic.x86_64.deb ...
Unpacking maxscale (2.4.9) ...
Setting up maxscale (2.4.9) ...
...
pato@patomaxgalera:~$

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@patomariag1:~$ mysql

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

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

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

Create a Galera Cluster configuration file

Create a new configuration file with the following parameters:

pato@patomaxgalera:~$ sudo mv /etc/maxscale.cnf /etc/maxscale.bkp
pato@patomaxgalera:~$ sudo nano /etc/maxscale.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

Save the file and restart MaxScale for the new configuration to be loaded:

pato@patomaxgalera:~$ sudo systemctl restart maxscale
pato@patomaxgalera:~$

Validate MaxScale is Running

Validate the services are running and listening by issuing command maxadmin -pmariadb list listeners:

pato@patomaxscale:~$ 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
RO-Listener          | RO-Service          | MariaDBClient      | ::              |  4008 | 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  |
+-------------+
| patomariag1 |
+-------------+
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  |
+-------------+
| patomariag2 |
+-------------+
1 row in set (0.001 sec)

We can see we are connecting to the MaxScale server and we are redirected to the cluster 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@patomaxscale:~$ 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@patomaxscale:~$ maxctrl list servers
+---------------------------------------------------------------------------------+
¦ Server     ¦ Address     ¦ Port ¦ Connections ¦ State                   ¦ GTID  ¦
+------------+-------------+------+-------------+-------------------------+-------¦
¦ Pato-Node0 ¦ patomariagm ¦ 3306 ¦ 0           ¦ Master, Synced, Running ¦ 1-1-8 ¦
+------------+-------------+------+-------------+-------------------------+-------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-8 ¦
+------------+-------------+------+-------------+-------------------------+-------+
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-8 ¦
+------------+-------------+------+-------------+-------------------------+-------+

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, as described here:

If a node marked as master inside MaxScale happens to fail and the master status is assigned to another node MaxScale will normally return the master status to the original node after it comes back up.

Stop the designated server patomariagm:

pato@patomariagm:~$ sudo systemctl stop mariadb

and validate the state issuing maxctrl list servers command:

pato@patomaxscale:~$ 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-9 ¦
+------------+-------------+------+-------------+-------------------------+-------¦
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-9 ¦
+------------+-------------+------+-------------+-------------------------+-------+

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@patomaxscale:~$ tail -f /var/log/maxscale/maxscale.log

2020-05-15 20:14:37   notice : Server changed state: Pato-Node0[patomariagm:3306]: master_down. [Master, Synced, Running] -> [Down]
2020-05-15 20:14:37   notice : Server changed state: Pato-Node1[patomariag1:3306]: new_master. [Slave, Synced, Running] -> [Master, Synced, Running]
2020-05-15 20:14:37   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@patomariagm:~$ sudo systemctl star mariadb

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-10 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node1 ¦ patomariag1 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-10 ¦
+------------+-------------+------+-------------+-------------------------+--------¦
¦ Pato-Node2 ¦ patomariag2 ¦ 3306 ¦ 0           ¦ Slave, Synced, Running  ¦ 1-1-10 ¦
+------------+-------------+------+-------------+-------------------------+--------+

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

pato@patomaxscale:~$ tail -f /var/log/maxscale/maxscale.log

2020-05-15 20:35:37   notice : Server changed state: Pato-Node0[patomariagm:3306]: master_up. [Down] -> [Master, Synced, Running]
2020-05-15 20:35:37   notice : Server changed state: Pato-Node1[patomariag1: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 Google Cloud virtual machine environment 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.