How to install MariaDB MaxScale on Rocky Linux 9

In this Linux tutorial, you will learn how to install MariaDB MaxScale on Rocky Linux 9 or other Red Hat/CentOS based Linux distributions.

What is MariaDB MaxScale?
Environment Specification
Prepare your Rocky Linux Server
Setup Local DNS Resolver
Install MariaDB Yum Repository
Install MariaDB MaxScale
Create MaxScale Database Users
Configure MaxScale Load Balancer
Configure Linux Firewall
Accessing Galera Cluster
Accessing MaxGUI Web Interface
Conclusion

What is MariaDB MaxScale?:

MariaDB MaxScale is a database proxy server and query router designed to work with MariaDB, a popular open-source relational database management system and MySQL-compatible database server. MaxScale is developed by the MariaDB Corporation and acts as an intermediary between client applications and the MariaDB database servers.

The primary purpose of MariaDB MaxScale is to provide advanced database management features, improve scalability, and enhance high availability in MariaDB database environments. It allows administrators to optimize the performance and security of their database systems by implementing various functionalities, such as:

Load Balancing: MaxScale can distribute incoming database connections and queries across multiple MariaDB database servers, ensuring better utilization of resources and preventing any single server from becoming overloaded.
Query Routing: It intelligently routes queries to specific MariaDB servers based on factors like query type, user, or database, optimizing query performance and minimizing response times.
Read-Write Splitting: MaxScale can split read and write operations, directing read-heavy queries to replica servers and write operations to the primary database server. This improves read scalability while maintaining data consistency.
Connection Throttling: It can limit the number of concurrent connections from client applications to prevent overloading the database servers during peak usage.
Failover and High Availability: MaxScale supports automatic failover, ensuring that if one database server becomes unavailable, the traffic is redirected to a functioning server, ensuring high availability.
Security Features: MaxScale includes various security mechanisms, such as firewall filters, to protect the database servers from unauthorized access or malicious queries.

Overall, MariaDB MaxScale is a powerful tool for optimizing, scaling, and managing MariaDB database clusters, making it a valuable addition for organizations dealing with large-scale database deployments and demanding performance requirements.

Environment Specification:

We are using a minimal Rocky Linux 9 virtual machine with following specifications.

CPU - 3.4 Ghz (2 cores)
Memory - 4 GB
Storage - 40 GB
Operating System - Rocky Linux release 9.2 (Blue Onyx)
Hostname - maxscale-01.centlinux.com
IP Address - 192.168.18.91/24

We are using a 3 node Galera MySQL cluster. You are also required to install MariaDB Galera Cluster on Rocky Linux 9 before reading this tutorial.

Prepare your Rocky Linux Server:

By using a ssh client, login to your Linux server as root user.

By using nmcli command (Network Manager CLI) set a static IP Address, Gateway and DNS server for your MaxScale server.

You must restart your network interface to apply changes.

# nmcli c m enp0s3 ipv4.method manual ipv4.addresses 192.168.18.91/24 ipv4.gateway 192.168.18.1 ipv4.dns 192.168.18.1
# nmcli c down enp0s3; nmcli c up enp0s3

Set the Hostname of your Database Load Balancer.

# hostnamectl set-hostname maxscale-01.centlinux.com

Execute following command at Linux bash prompt to update software packages in your Linux operating system.

# dnf update -y

You may need to reboot your Rocky Linux server, if the above command updates your Linux Kernel.

# reboot

After reboot, check the version of your Linux OS and Kernel.

# cat /etc/rocky-release
Rocky Linux release 9.2 (Blue Onyx)

# uname -r
5.14.0-284.18.1.el9_2.x86_64

Setup Local DNS Resolver:

If you are using a Network DNS server such as BIND or Unbound, then you can skip is step.

Otherwise, you can follow below steps to setup Local DNS resolver for your MariaDB database cluster.

Open /etc/hosts file in vim text editor.

# vi /etc/hosts

Add following lines in this file.

192.168.18.88 mariadb-01 mariadb-01.centlinux.com
192.168.18.89 mariadb-02 mariadb-02.centlinux.com
192.168.18.90 mariadb-03 mariadb-03.centlinux.com
192.168.18.91 maxscale-01 maxscale-01.centlinux.com

Install MariaDB Yum Repository:

MaxScale Load Balancer software is available via MariaDB yum repositories. Therefore, you need to install MariaDB Official yum repository by using following Linux command.

# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.11 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg ~
~
# [info] Successfully added trusted package signing keys
# [info] Cleaning package cache...
25 files removed

Build your yum cache for newly installed yum repositories.

# dnf makecache
MariaDB Server                                   33 kB/s | 690 kB     00:20
MariaDB MaxScale                                469  B/s | 7.1 kB     00:15
MariaDB Tools                                   3.8 kB/s |  25 kB     00:06
Rocky Linux 9 - BaseOS                           65 kB/s | 1.9 MB     00:29
Rocky Linux 9 - AppStream                       117 kB/s | 7.1 MB     01:01
Rocky Linux 9 - Extras                          427  B/s |  10 kB     00:23
Metadata cache created.

Install MariaDB MaxScale:

If you have successfully setup MariaDB Official yum repositories, then you can install MaxScale Load Balancer software with the help of a single dnf command.

# dnf install -y maxscale

Create MaxScale Database Users:

By using ssh command, login as root user on first node (mariadb-01) of your Galera cluster.

# ssh root@mariadb-01

# mysql -u root -p
MariaDB [(none)]> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.002 sec)

Create adatabase user 'maxscale' and grant it necessary privileges.

MariaDB [(none)]> create user 'maxscale'@'%' identified by '123';
Query OK, 0 rows affected (0.017 sec)

MariaDB [(none)]> grant select on mysql.* to 'maxscale'@'%';
Query OK, 0 rows affected (0.017 sec)

MariaDB [(none)]> GRANT SHOW DATABASES, BINLOG ADMIN, READ ONLY ADMIN, RELOAD,
    -> REPLICATION MASTER ADMIN, REPLICATION SLAVE ADMIN,
    -> REPLICATION SLAVE, SLAVE MONITOR ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.017 sec)

Create a database user 'ahmer' for testing purpose.

MariaDB [(none)]> create user ahmer@'%' identified by '123';
Query OK, 0 rows affected (0.016 sec)

MariaDB [(none)]> grant show databases on *.* to ahmer@'%';
Query OK, 0 rows affected (0.016 sec)

Reload privileges Tables and exit from MySQL Shell.

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

MariaDB [(none)]> quit
Bye

Configure MaxScale Load Balancer:

Connect to your MaxScale server as root use by means of a ssh client.

# ssh root@maxscale-01

Rename old configuration file and create a new one by using vim text editor.

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

Add following lines in this file.

[maxscale]
threads=auto
admin_host            = 0.0.0.0
admin_port            = 8989
admin_secure_gui=false
 
[mariadb-01]
type=server
address=192.168.18.88
port=3306
protocol=MariaDBBackend

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

[mariadb-03]
type=server
address=192.168.18.90
port=3306
protocol=MariaDBBackend
 
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=mariadb-01,mariadb-02,mariadb-03
user=maxscale
password=123

[Galera-Service]
type=service
router=readwritesplit
servers=mariadb-01,mariadb-02,mariadb-03
user=maxscale
password=123

[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
port=3306

Save and exit from vim text editor by pressing 'wq' keys.

Enable and Start MaxScale service.

# systemctl enable --now maxscale

Configure Linux Firewall:

In above MaxScale configuration, you are using two service ports.

Port 8989/tcp for MaxGUI (MaxScale Web Interface)
Port 3306/tcp for Galera Listener.

You need to allow both ports in Linux Firewall for proper functionality.

# firewall-cmd --permanent --add-port={8989,3306}/tcp
success

# firewall-cmd --reload
success

Accessing Galera Cluster:

You can now test your Load Balancer configuration, by connecting with your Galera Cluster via MaxScale service.

Execute following command on a MySQL client to connect with your Galera Cluster.

# mysql -h maxscale-01.centlinux.com -P 3306 -u ahmer -p

Accessing MaxGUI Web Interface:

An Admin user is required for access the MaxGUI web interface. You can use maxctrl command with following options to create the required user.

# maxctrl create user "maxadmin" "123" --type=admin
OK

Open URL http://maxscale-01.centlinux.com:8989 in a web browser.

You are at the Dashboard of MaxGUI web interface. Explore the available options by clicking the menu on the left sidebar.

Conclusion:

In this Linux tutorial, you have learned how to install MariaDB MaxScale on Rocky Linux 9 or other Red Hat/CentOS based Linux distributions. If you want to learn more about Galera Cluster, we recommend that you should attend online training Galera Cluster and MariaDB MySQL Replication

Thursday, July 27, 2023