How to install MariaDB MaxScale on Linux 9

Discover the comprehensive guide to installing MariaDB MaxScale on Linux 9. Learn the step-by-step process to set up this powerful database proxy and scaling solution on your Linux environment effortlessly. Optimize your database performance and scalability with MariaDB MaxScale installation instructions tailored for Linux 9. #centlinux #linux #mysql

What is MariaDB Database Cluster?

A MariaDB database cluster is a distributed database system based on the MariaDB database management system. It is designed to provide high availability, scalability, and fault tolerance by distributing data across multiple nodes in a cluster.

In a MariaDB database cluster:

1. Data Distribution: Data is distributed across multiple nodes in the cluster, allowing for horizontal scaling. Each node in the cluster contains a portion of the dataset, and data is replicated across multiple nodes for redundancy and fault tolerance.
2. High Availability: MariaDB Cluster ensures high availability by replicating data across multiple nodes and providing automatic failover in the event of node failure. This means that even if one or more nodes in the cluster fail, the database remains accessible and operational.
3. Auto-Sharding: MariaDB Cluster uses auto-sharding to distribute data across multiple nodes. Sharding involves splitting the dataset into smaller, more manageable pieces (shards) and distributing these shards across different nodes in the cluster.
4. Real-Time Performance: MariaDB Cluster is optimized for real-time performance and low-latency applications. It supports in-memory data storage and processing, making it well-suited for use cases that require fast response times and high throughput.
5. ACID Compliance: MariaDB Cluster provides ACID (Atomicity, Consistency, Isolation, Durability) compliance, ensuring data consistency and reliability even in distributed environments.

Overall, a MariaDB database cluster offers a robust and scalable solution for managing large volumes of data in distributed environments while ensuring high availability, fault tolerance, and real-time performance. It is commonly used in applications requiring scalable and reliable database solutions, such as e-commerce platforms, online gaming, telecommunications, and financial services.

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.

Read Also: How to install MariaDB MaxScale on CentOS 7

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 MaxScale on Linux:

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

Login to MySQL database server and check size of the Galera cluster.

# 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[maxscale]
threads=auto
admin_host            = 0.0.0.0
admin_port            = 8989
admin_secure_gui=false

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.

1. Port 8989/tcp for MaxGUI (MaxScale Web Interface)
2. 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.

Login as maxadmin user.

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

Video: MariaDB MaxScale Setup:

Final Thoughts

Navigating to install MaxScale on Linux 9 empowers you to enhance your database infrastructure with advanced scalability and performance capabilities. By following this guide, you’ll unlock the potential for efficient database management and streamlined operations on your Linux environment. Embrace the power of MariaDB MaxScale to optimize your database setup and propel your systems to new heights of reliability and efficiency on Linux 9. If you want to learn more about Galera Cluster, we recommend that you should attend online training Galera Cluster and MariaDB MySQL Replication