How to setup MariaDB MaxScale on CentOS 7

Learn how to setup MariaDB MaxScale on CentOS 7 with our comprehensive guide. Follow step-by-step instructions to enhance your database scalability, security, and load balancing capabilities. #centlinux #linux #mysql

What is MaxScale?

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.

Recommended Training for You: Galera Cluster and MariaDB MySQL Replication

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

Linux Server Specification

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

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

Create MySQL User

To setup MariaDB MaxScale communication with Galera Cluster, we need a MySQL database user.

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 on any single 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

Setup MariaDB MaxScale on CentOS 7

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 

Configure Linux Firewall

Allow Service Port in Linux Firewall.

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

Test MariaDB MaxScale configurations

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.

Read Also: MySQL Database Backup and Restore Techniques

Final Thoughts

Setting up MariaDB MaxScale on CentOS 7 can greatly improve your database’s scalability, security, and load balancing capabilities. By following this guide, you should achieve a successful installation and configuration.

If you need further assistance or prefer to have an expert handle the setup for you, I offer professional services for MariaDB MaxScale configuration. Visit my Fiverr profile to learn more and get started: Senior Database Administrator

Enhance your database performance and reliability with expert support today!