MaxScale Setup for MariaDB NoSQL Protocol
I have configured this setup to test MariaDB’s NoSQL Protocol Module in action.
The RHEL 8 and CentOS 8 distributions include MariaDB Community Server 10.3. But I will download the latest Generally Available Release 11.4.2. For downloading 11.4.2, first i will use the mariadb_repo_setup script to configure the MariaDB repositories for YUM.
[root@maria01 ~]# wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
--2024-06-02 17:00:17-- https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
Resolving downloads.mariadb.com (downloads.mariadb.com)... 104.18.135.24, 104.17.191.14, 2606:4700::6811:bf0e, ...
Connecting to downloads.mariadb.com (downloads.mariadb.com)|104.18.135.24|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 36592 (36K) [application/octet-stream]
Saving to: ‘mariadb_repo_setup’
mariadb_repo_setup 100%[=============================================================>] 35.73K --.-KB/s in 0s
2024-06-02 17:00:17 (108 MB/s) - ‘mariadb_repo_setup’ saved [36592/36592]
[root@maria01 ~]# chmod +x mariadb_repo_setup
[root@maria01 ~]# ./mariadb_repo_setup
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 11.4 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...
24 files removed I will install dependencies for MariaDB-server.
[root@maria01 ~]# yum install perl-DBI libaio libsepol lsof boost-program-options liburing galera-4
Last metadata expiration check: 0:16:58 ago on Sun 02 Jun 2024 05:02:18 PM +03.
Package perl-DBI-1.641-4.module+el8.6.0+20590+70c0920f.x86_64 is already installed.
Package libaio-0.3.112-1.el8.x86_64 is already installed.
Package libsepol-2.9-3.el8.x86_64 is already installed.
Package lsof-4.93.2-1.el8.x86_64 is already installed.
Package boost-program-options-1.66.0-13.el8.x86_64 is already installed.
Package liburing-1.0.7-3.el8.x86_64 is already installed.
Dependencies resolved.
=========================================================================================================================================
Package Architecture Version Repository Size
=========================================================================================================================================
Installing:
galera-4 x86_64 26.4.18-1.el8 mariadb-main 14 M
Installing dependencies:
socat x86_64 1.7.4.1-1.el8 ol8_appstream 323 k
Transaction Summary
=========================================================================================================================================
Install 2 Packages
Total download size: 14 M
Installed size: 67 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): socat-1.7.4.1-1.el8.x86_64.rpm 554 kB/s | 323 kB 00:00
(2/2): galera-4-26.4.18-1.el8.x86_64.rpm 4.4 MB/s | 14 MB 00:03
-----------------------------------------------------------------------------------------------------------------------------------------
Total 4.5 MB/s | 14 MB 00:03
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : socat-1.7.4.1-1.el8.x86_64 1/2
Running scriptlet: galera-4-26.4.18-1.el8.x86_64 2/2
Installing : galera-4-26.4.18-1.el8.x86_64 2/2
Running scriptlet: galera-4-26.4.18-1.el8.x86_64 2/2
Verifying : galera-4-26.4.18-1.el8.x86_64 1/2
Verifying : socat-1.7.4.1-1.el8.x86_64 2/2
Installed:
galera-4-26.4.18-1.el8.x86_64 socat-1.7.4.1-1.el8.x86_64
Complete!Now i can install MariaDB-server.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [root@maria01 ~]# yum install –repo="mariadb-main" MariaDB-server | |
| Waiting for process with pid 2474 to finish. | |
| Last metadata expiration check: 0:00:04 ago on Sun 02 Jun 2024 05:19:27 PM +03. | |
| Dependencies resolved. | |
| ========================================================================================================================================= | |
| Package Architecture Version Repository Size | |
| ========================================================================================================================================= | |
| Installing: | |
| MariaDB-server x86_64 11.4.2-1.el8 mariadb-main 27 M | |
| Installing dependencies: | |
| MariaDB-client x86_64 11.4.2-1.el8 mariadb-main 17 M | |
| MariaDB-common x86_64 11.4.2-1.el8 mariadb-main 88 k | |
| MariaDB-shared x86_64 11.4.2-1.el8 mariadb-main 130 k | |
| Installing weak dependencies: | |
| MariaDB-client-compat noarch 11.4.2-1.el8 mariadb-main 11 k | |
| MariaDB-server-compat noarch 11.4.2-1.el8 mariadb-main 8.9 k | |
| Transaction Summary | |
| ========================================================================================================================================= | |
| Install 6 Packages | |
| Total download size: 44 M | |
| Installed size: 231 M | |
| Is this ok [y/N]: y | |
| Downloading Packages: | |
| (1/6): MariaDB-client-compat-11.4.2-1.el8.noarch.rpm 13 kB/s | 11 kB 00:00 | |
| (2/6): MariaDB-common-11.4.2-1.el8.x86_64.rpm 76 kB/s | 88 kB 00:01 | |
| (3/6): MariaDB-server-compat-11.4.2-1.el8.noarch.rpm 16 kB/s | 8.9 kB 00:00 | |
| (4/6): MariaDB-shared-11.4.2-1.el8.x86_64.rpm 114 kB/s | 130 kB 00:01 | |
| (5/6): MariaDB-client-11.4.2-1.el8.x86_64.rpm 5.3 MB/s | 17 MB 00:03 | |
| (6/6): MariaDB-server-11.4.2-1.el8.x86_64.rpm 7.2 MB/s | 27 MB 00:03 | |
| —————————————————————————————————————————————– | |
| Total 9.5 MB/s | 44 MB 00:04 | |
| Running transaction check | |
| Transaction check succeeded. | |
| Running transaction test | |
| Transaction test succeeded. | |
| Running transaction | |
| Preparing : 1/1 | |
| Installing : MariaDB-shared-11.4.2-1.el8.x86_64 1/6 | |
| Running scriptlet: MariaDB-shared-11.4.2-1.el8.x86_64 1/6 | |
| Installing : MariaDB-common-11.4.2-1.el8.x86_64 2/6 | |
| Installing : MariaDB-client-compat-11.4.2-1.el8.noarch 3/6 | |
| Installing : MariaDB-client-11.4.2-1.el8.x86_64 4/6 | |
| Installing : MariaDB-server-compat-11.4.2-1.el8.noarch 5/6 | |
| Running scriptlet: MariaDB-server-11.4.2-1.el8.x86_64 6/6 | |
| Installing : MariaDB-server-11.4.2-1.el8.x86_64 6/6 | |
| Running scriptlet: MariaDB-server-11.4.2-1.el8.x86_64 6/6 | |
| Two all-privilege accounts were created. | |
| One is root@localhost, it has no password, but you need to | |
| be system 'root' user to connect. Use, for example, sudo mysql | |
| The second is mysql@localhost, it has no password either, but | |
| you need to be the system 'mysql' user to connect. | |
| After connecting you can set the password, if you would need to be | |
| able to connect as any of these users with a password and without sudo | |
| See the MariaDB Knowledgebase at https://mariadb.com/kb | |
| Please report any problems at https://mariadb.org/jira | |
| The latest information about MariaDB is available at https://mariadb.org/. | |
| Consider joining MariaDB's strong and vibrant community: | |
| https://mariadb.org/get-involved/ | |
| Verifying : MariaDB-client-11.4.2-1.el8.x86_64 1/6 | |
| Verifying : MariaDB-client-compat-11.4.2-1.el8.noarch 2/6 | |
| Verifying : MariaDB-common-11.4.2-1.el8.x86_64 3/6 | |
| Verifying : MariaDB-server-11.4.2-1.el8.x86_64 4/6 | |
| Verifying : MariaDB-server-compat-11.4.2-1.el8.noarch 5/6 | |
| Verifying : MariaDB-shared-11.4.2-1.el8.x86_64 6/6 | |
| Installed: | |
| MariaDB-client-11.4.2-1.el8.x86_64 MariaDB-client-compat-11.4.2-1.el8.noarch MariaDB-common-11.4.2-1.el8.x86_64 | |
| MariaDB-server-11.4.2-1.el8.x86_64 MariaDB-server-compat-11.4.2-1.el8.noarch MariaDB-shared-11.4.2-1.el8.x86_64 | |
| Complete! |
I will start the MariaDB service.
[root@maria01 ~]# systemctl start mariadb.service
[root@maria01 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB 11.4.2 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Sun 2024-06-02 17:22:17 +03; 7s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 33833 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 33811 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; >
Process: 33809 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 33821 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 12 (limit: 30486)
Memory: 78.6M
CGroup: /system.slice/mariadb.service
└─33821 /usr/sbin/mariadbd
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] Plugin 'FEEDBACK' is disabled.
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] Plugin 'wsrep-provider' is disabled.
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_>
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] InnoDB: Buffer pool(s) load completed at 240602 17:22:>
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] Server socket created on IP: '0.0.0.0'.
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] Server socket created on IP: '::'.
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: 2024-06-02 17:22:17 0 [Note] /usr/sbin/mariadbd: ready for connections.
Jun 02 17:22:17 maria01.localdomain mariadbd[33821]: Version: '11.4.2-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB>
Jun 02 17:22:17 maria01.localdomain systemd[1]: Started MariaDB 11.4.2 database server.
lines 1-27/27 (END)Since I will test nosqlprotocol module, I need to install Maxscale, it is a prerequisite for configuring listeners. MaxScale is an advanced database proxy for MariaDB, offering load balancing, query routing, high availability, security, scalability, and real-time monitoring. It ensures optimal performance, continuous availability, and protection for MariaDB and MySQL databases, making it ideal for large-scale, high-demand environments.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [root@maria01 ~]# yum install maxscale | |
| Last metadata expiration check: 0:06:01 ago on Sun 02 Jun 2024 05:19:30 PM +03. | |
| Dependencies resolved. | |
| ========================================================================================================================================= | |
| Package Architecture Version Repository Size | |
| ========================================================================================================================================= | |
| Installing: | |
| maxscale x86_64 24.02.1-1.rhel.8 mariadb-maxscale 92 M | |
| Installing dependencies: | |
| unixODBC x86_64 2.3.7-1.el8 ol8_appstream 458 k | |
| Transaction Summary | |
| ========================================================================================================================================= | |
| Install 2 Packages | |
| Total download size: 93 M | |
| Installed size: 392 M | |
| Is this ok [y/N]: y | |
| Downloading Packages: | |
| (1/2): unixODBC-2.3.7-1.el8.x86_64.rpm 581 kB/s | 458 kB 00:00 | |
| (2/2): maxscale-24.02.1-1.rhel.8.x86_64.rpm 9.2 MB/s | 92 MB 00:10 | |
| —————————————————————————————————————————————– | |
| Total 9.2 MB/s | 93 MB 00:10 | |
| Running transaction check | |
| Transaction check succeeded. | |
| Running transaction test | |
| Transaction test succeeded. | |
| Running transaction | |
| Preparing : 1/1 | |
| Installing : unixODBC-2.3.7-1.el8.x86_64 1/2 | |
| Running scriptlet: unixODBC-2.3.7-1.el8.x86_64 1/2 | |
| Installing : maxscale-24.02.1-1.rhel.8.x86_64 2/2 | |
| Running scriptlet: maxscale-24.02.1-1.rhel.8.x86_64 2/2 | |
| Created symlink /etc/systemd/system/multi-user.target.wants/maxscale.service → /usr/lib/systemd/system/maxscale.service. | |
| Verifying : maxscale-24.02.1-1.rhel.8.x86_64 1/2 | |
| Verifying : unixODBC-2.3.7-1.el8.x86_64 2/2 | |
| Installed: | |
| maxscale-24.02.1-1.rhel.8.x86_64 unixODBC-2.3.7-1.el8.x86_64 | |
| Complete! |
The NoSQL protocol module enables a MariaDB server or cluster to act as the backend for applications using a MongoDB client library.
Since MaxScale handles tasks like authentication and monitoring, you must create a database user with specific privileges. Now i will create it.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| MariaDB [(none)]> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.columns_priv TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.procs_priv TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.proxies_priv TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SLAVE MONITOR on *.* to 'maxscale'@'%'; | |
| MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%'; |
I’ll set it up to operate at 192.168.60.204 and establish a NoSQL-Listener on port 27004, with all adjustments made in /etc/maxscale.cnf. I’ve detailed the sections where modifications diverge from the default configuration.
[root@maria01 ~]# vi /etc/maxscale.cnf
...
[server1]
type=server
address=192.168.60.204
port=3306
...
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1
user=maxscale
password=maxscale_pw
monitor_interval=2s
....
[Read-Only-Service]
type=service
router=readconnroute
servers=server1
user=maxscale
password=maxscale_pw
router_options=slave
...
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1
user=maxscale
password=maxscale_pw
...
[NoSQL-Listener]
type=listener
service=Read-Write-Service
protocol=nosqlprotocol
nosqlprotocol.user=maxscale
nosqlprotocol.password=maxscale_pw
port=27004Now i will start Maxscale, MariaDB Database Proxy is ready for connections.
[root@maria01 ~]# systemctl start maxscale.service
[root@maria01 ~]# systemctl status maxscale.service
● maxscale.service - MariaDB MaxScale Database Proxy
Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2024-06-02 17:41:23 +03; 6s ago
Process: 34754 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
Main PID: 34755 (maxscale)
Tasks: 9 (limit: 24635)
Memory: 3.3M
CGroup: /system.slice/maxscale.service
└─34755 /usr/bin/maxscale
Jun 02 17:41:23 maria01.localdomain systemd[1]: Starting MariaDB MaxScale Database Proxy...
Jun 02 17:41:23 maria01.localdomain maxscale[34755]: /var/lib/maxscale/maxscale.cnf.d does not exist, not reading.
Jun 02 17:41:23 maria01.localdomain maxscale[34755]: Module 'mariadbmon' loaded from '/usr/lib64/maxscale/libmariadbmon.so'.
Jun 02 17:41:23 maria01.localdomain maxscale[34755]: Module 'readconnroute' loaded from '/usr/lib64/maxscale/libreadconnroute.so'.
Jun 02 17:41:23 maria01.localdomain maxscale[34755]: Module 'readwritesplit' loaded from '/usr/lib64/maxscale/libreadwritesplit.so'.
Jun 02 17:41:23 maria01.localdomain maxscale[34755]: Using up to 586.3MiB of memory for query classifier cache
Jun 02 17:41:23 maria01.localdomain systemd[1]: Started MariaDB MaxScale Database Proxy.
Here is the Maxscale services list.
Now we are ready to see NoSQL Protocol Module for MariaDB in action.
[root@maria01 ~]# vi /var/log/maxscale/maxscale.log
...
2024-06-02 18:59:58 notice : (NoSQL-Listener); Listening for connections at [::]:27004
..
[root@maria01 ~]# netstat -an | grep 27004
tcp6 0 0 :::27004 :::* LISTEN Hope it helps.
Osman’s DBlog 
Leave your comment