MaxScale with MariaDB Replication on VM Instances

May 8, 2020 7-minute read

Introduction

When you setup a MariaDB replication cluster you have two or more database instances that should act as a single unit. 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, a proxy software which provides a single access point to the database and also come up with other benefits like Load Balancing and Failover.
We are going to setup MaxScale and test its Failover functionalities.

Prerequisites

We need a MariaDB Master Slave replication cluster. For this exercise we are going to use our Google Cloud Platform installation for MariaDB Master Slave on virtual machine instances.

MaxScale Installation

MaxScale Instance

Create a new Virtual Machine with the following parameters:

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

Name: patomaxscale
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: patomaxscale.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
patomariadb   us-central1-f  f1-micro                   10.128.0.40  34.69.159.12    RUNNING
patomariarp   us-central1-f  f1-micro                   10.128.0.41  35.192.48.122   RUNNING
patomaxscale  us-central1-f  f1-micro                   10.128.0.42  104.197.32.181  RUNNING

MaxScale Software

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

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

MaxScale Configuration

Create a Database User for MaxScale

Connect to the Master database and create the MaxScale monitor user maxpato with proper privileges according to Maria DB Monitor Documentation:

SUPER, to modify slave connections and set globals such as read_only
REPLICATION CLIENT, to list slave connections
RELOAD, to flush binary logs
PROCESS, to check if the event_scheduler process is running
SHOW DATABASES and EVENT, to list and modify server events

pato@patomariadb:~$ mysql

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

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

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

Create a Master-Slave Configuration File

Create a new configuration file with the following parameters:

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

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

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

Validate MaxScale is Running

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

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

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
---------------------+---------------------+--------------------+-----------------+-------+--------

Connect to Database using MaxScale

As we have now our MaxScale running we no longer need to connect directly to the Master database. For remote connections we need to point our client or application to the MaxScale instance in port 4006 and activities will be 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, rejoin and switchover activities whenever one node fails or recovers:

Failover replaces a failed master with a running slave
Rejoin joins a standalone server to the cluster and start slave replication
Switchover swaps a running master with a running slave

Let’s test those functionalities.

Failover

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

pato@patomariadb:~$ sudo systemctl stop mariadb
pato@patomariadb:~$

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

pato@patomaxscale:~$ maxctrl list servers
+-----------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID     ¦
+-------------+-------------+------+-------------+-----------------+----------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Down            ¦ 0-1-9    ¦
+-------------+-------------+------+-------------+-----------------+----------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-11-130 ¦
+-------------+-------------+------+-------------+-----------------+----------+

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 patomaxscale -P 4006
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6

MariaDB [(none)]> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| patomariarp |
+-------------+
1 row in set (0.001 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@patomariadb:~$ sudo systemctl start mariadb
pato@patomariadb:~$

pato@patomaxscale:~$ maxctrl list servers
+-----------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID     ¦
+-------------+-------------+------+-------------+-----------------+----------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Slave, Running  ¦ 0-11-130 ¦
+-------------+-------------+------+-------------+-----------------+----------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-11-130 ¦
+-------------+-------------+------+-------------+-----------------+----------+

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@patomaxscale:~$ maxctrl call command mariadbmon switchover Pato-Monitor Pato-Master Pato-Slave -t 600000
OK
pato@patomaxscale:~$ maxctrl list servers
+----------------------------------------------------------------------------+
¦ Server      ¦ Address     ¦ Port ¦ Connections ¦ State           ¦ GTID    ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Master ¦ patomariadb ¦ 3306 ¦ 0           ¦ Master, Running ¦ 0-1-131 ¦
+-------------+-------------+------+-------------+-----------------+---------¦
¦ Pato-Slave  ¦ patomariarp ¦ 3306 ¦ 0           ¦ Slave, Running  ¦ 0-1-131 ¦
+-------------+-------------+------+-------------+-----------------+---------+

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 our Google Cloud virtual machine environment 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.