Install MariaDB MaxScale Database Proxy on CentOS 7

MaxScale is an intelligent database proxy developed by MariaDB Corporation. MaxScale is free and open source under Business Source License (BSL). MaxScale extends the high availability, load-balancing, scalability and security of MariaDB server and it simplifies the application development by decoupling it from underlying database infrastructure.

In our previous post “Install MariaDB Galera Cluster on CentOS 7”, we have configured a two node MariaDB cluster on CentOS 7 server using Galera. In this article, we will install and configure MariaDB MaxScale database proxy for our Galera cluster.

Read Also: How to install MariaDB MaxScale on Rocky Linux 9

System Specification:

We will use the same MariaDB Galera cluster that we have configured in our previous post. Besides Galera cluster, we have also provisioned a virtual machine with following specification. This new virtual machine is used as the MaxScale database proxy for our MariaDB Galera cluster.

• Hostname - maxscale.example.com
• IP Address - 192.168.116.80/24
• CPU - 2.4 Ghz (2 cores)
• Memory - 1 GB
• Operating System - CentOS 7.6

Install MariaDB MaxScale on CentOS 7:

Connect to arbitrary node maxscale.example.com using ssh.

Install MariaDB and MaxScale yum repositories.

# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo.
[info] Adding trusted package signing keys...
[info] Succeessfully added trusted package signing keys.

Build yum cache.

# yum makecache fast
Loaded plugins: fastestmirror
Determining fastest mirrors
 * base: mirrors.ges.net.pk
 * extras: mirrors.ges.net.pk
 * updates: mirrors.ges.net.pk
base                                                     | 3.6 kB     00:00
extras                                                   | 3.4 kB     00:00
mariadb-main                                             | 2.9 kB     00:00
mariadb-maxscale                                         | 2.4 kB     00:00
mariadb-tools                                            | 2.9 kB     00:00
updates                                                  | 3.4 kB     00:00
(1/5): extras/7/x86_64/primary_db                          | 156 kB   00:01
(2/5): mariadb-tools/7/x86_64/primary_db                   |  11 kB   00:02
(3/5): mariadb-maxscale/7/x86_64/primary_db                | 6.7 kB   00:02
(4/5): mariadb-main/7/x86_64/primary_db                    |  50 kB   00:03
(5/5): updates/7/x86_64/primary_db                         | 1.4 MB   00:04
Metadata Cache Created

Install MariaDB MaxScale using yum command.

# yum install -y maxscale

Now connect to an instance of MariaDB Galera Cluster and create a user for monitoring and authentication by MaxScale. (Since, our nodes have formed a cluster, therefore, we only need to execute the following commands once on any node).

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Execute following commands to create a MaxScale user with necessary privileges to perform it's function properly.

> create user 'maxscale'@'192.168.116.80' identified by '123';
Query OK, 0 rows affected (0.005 sec)

> grant select on mysql.user to 'maxscale'@'192.168.116.80';
Query OK, 0 rows affected (0.041 sec)

> grant select on mysql.db to 'maxscale'@'192.168.116.80';
Query OK, 0 rows affected (0.002 sec)

> grant select on mysql.tables_priv to 'maxscale'@'192.168.116.80';
Query OK, 0 rows affected (0.043 sec)

> grant show databases on *.* to 'maxscale'@'192.168.116.80';
Query OK, 0 rows affected (0.003 sec)

> flush privileges;
Query OK, 0 rows affected (0.004 sec)

Create a user with privileges to remotely connect from any machine. This user will be used by our application to connect to MariaDB Galera cluster.

> create user ahmer@'%' identified by '123';
Query OK, 0 rows affected (0.005 sec)

> grant show databases on *.* to ahmer@'%';
Query OK, 0 rows affected (0.003 sec)

> flush privileges;
Query OK, 0 rows affected (0.004 sec)

> exit
Bye

Now, connect to maxscale.example.com and configure MaxScale database proxy.

Edit MaxScale configurations.

# mv /etc/maxscale.cnf /etc/maxscale.cnf.old
# vi /etc/maxscale.cnf

and add following directives. (Please refer to MaxScale Documentation for more information about MaxScale configuration parameters)

#Global MaxScale Settings
[maxscale]
threads=auto

#Define Server Nodes
[mariadb-01]
type=server
address=192.168.116.81
port=3306
protocol=MariaDBBackend

[mariadb-02]
type=server
address=192.168.116.82
port=3306
protocol=MariaDBBackend

#Define Monitoring Service
[Galera-Monitor]
type=monitor
module=galeramon
servers=mariadb-01,mariadb-02
user=maxscale
password=123
monitor_interval=1000

#Define Galera Service
[Galera-Service]
type=service
router=readconnroute
router_options=synced
servers=mariadb-01,mariadb-02
user=maxscale
password=123

#Define Galera Listener
[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
port=4306

#Define Administration Service
[MaxAdmin-Service]
type=service
router=cli

#Define Administration Listener
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

Allow Service Port in Linux Firewall.

# firewall-cmd --permanent --add-port=4306/tcp
success
# firewall-cmd --reload
success

Start and enable MaxScale service.

# systemctl start maxscale.service
# systemctl enable maxscale.service

Make some connections from clients to Galera Cluster using ahmer user via MaxScale database proxy.

# mysql -h maxscale.example.com -P 4306 -u ahmer -p

Connect to maxscale.example.com using ssh, and use maxadmin command to see status of connections to MariaDB Galera Cluster.

# maxadmin
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status             
-------------------+-----------------+-------+-------------+--------------------
mariadb-01         | 192.168.116.81  |  3306 |           2 | Slave, Synced, Running
mariadb-02         | 192.168.116.82  |  3306 |           3 | Master, Synced, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale>

The above command shows the status of nodes in MariaDB Galera cluster including the active connections and replication status. You can use help command to get help on a maxadmin command or refer to maxadmin documentation for complete reference.

We have successfully install MariaDB MaxScale database proxy on CentOS 7 server.