Setting up MariaDB Galera and MaxScale

How to Configure MariaDB MaxScale Master-Slave with Galera Cluster?

In this article we will install MariaDB Galera cluster with MaxScale proxy database in Ubuntu 20.04 LTS (Focal) nodes. MaxScale is an intelligent proxy database that can route database statements from cluster to one server. But unlike HAproxy, MaxScale uses asynchronous I/O of the Linux kernel which helps with performance.

Cluster we are going to make will have read write split, meaning that all writes are done in master by MaxScale, and replicates them to all nodes as where they can be read.

In this article we use node-01 as the master and node-02, node-03 as slaves. Node-04 will be our MaxScale core machine.

Galera Master-Slave Architecture

Main Features of MaxScale

a) If any database server fails, connection will be automatically created to another node.

b) Connections can be dynamically added or removed from session.

c) MaxScale will route client request to number of database servers.

Installing the cluster

First thing is to set up your host file with hostnames and private IPs of the all your hosts. This is needed so you could have your nodes communicate over private IPs and avoid the need for encryption of traffic. Here is my hosts file (/etc/hosts) on all 4 servers:

sudo su -
nano /etc/hosts
10.132.84.235 node-01 node-01
10.132.86.222 node-02 node-02
10.132.19.195 node-03 node-03
10.132.53.122 node-04 node-04

Ref : https://library.netapp.com/ecmdocs/ECMP1155586/html/GUID-DBF81E5C-CF3C-4B07-AF01-83A625F2B4BF.html

First three will be for our Galera cluster, and fourth for MaxScale proxy.

Lets add key for MariaDB repository on first 3 servers.

apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

Then we will add repository for x86 and POWER little endian architectures, the package will be installed according your arch.

add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.6/ubuntu focal main'

Update the sources list.

apt get-update

And then install the mariadb

apt install mariadb-server rsync

Configuring and building the cluster

Next we need to edit configuration files and build the cluster. The node-01 will be the node for bootstrapping the cluster and other nodes will join to this one. So lets first edit configuration files on all three nodes.

nano /etc/mysql/my.cnf

There we need to find [galera] section and change these lines:

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="linoxide-cluster"
wsrep_cluster_address="gcomm://node-01,node-02,node-03"

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
wsrep_sst_method=rsync

wsrep_node_address="10.132.84.235"
wsrep_node_name="node-01"

The last two lines need to have address and hostname of current node, so above file is from node-01. On every node those two lines need to be changed accordingly, while other lines can be same.

After this is done, we need to start the cluster. If the database server is running by any chance, stop it on all three nodes.

systemctl stop mysql

On first node run:

galera_new_cluster

On the other two nodes

systemctl start mysql

Back on first node, we need to set the password, so we will run

mysql_secure_installation

After you ran that, you can type this command on any node

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

It should give you output similar to this

Galera Cluster

MaxScale Proxy installation and preparing the cluster

When we have cluster up and running, we can turn to node-04 to install MaxScale on it. MaxScale is only supported on x86_64 architecture for now. Lets download maxscale deb package with wget.

wget https://downloads.mariadb.com/MaxScale/2.5.0/ubuntu/dists/focal/main/binary-amd64/maxscale-2.5.0-1.ubuntu.focal.x86_64.deb

Next we will install dependencies

apt install libcurl3

And then install MaxScale

dpkg -i maxscale-2.5.0-1.ubuntu.focal.x86_64.deb

Maxscale have been installed now we need to again enter the mysql prompt on our Galera cluster in order to make maxscale user to grant him enough privileges to operate.

mysql -u root -p

And in mysql prompt type this line by line

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'you-password-here';
Query OK, 0 rows affected (0.00 sec)

GRANT SELECT ON mysql.db TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON mysql.user TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)

GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
Query OK, 0 rows affected (0.01 sec)

Configuring MaxScale

Lets take a look how the below config file works. First part under [maxscale] will respectively set number of CPU threads to 4, turn off logging to /var/log/syslog, turn on logging to /var/log/maxscale, turn on log warning, log to memory and log notices, and turn of log info and log developer options for debugging the code.

Next important section is [Galera Monitor]. There we need to concentrate on several lines. The line that say servers= need to be filled with names of servers. This is not hostname, this is how MaxScale names the servers in this config file further down. In our case we will set server1 to server3. User is username that we created in previous section, maxscale in our case. Password is whatever you set for your password for maxscale user. Galera Monotor will pick one node as master and others as slave (out of our three nodes). The node with lowest WSREP_LOCAL_INDEX will be selected as master. If cluster configuration changes, the new selection may happen and node with lower index will selected as master. If you don’t want for master to change this way, you can use option disable_master_failback and set it to 1 like in our config file. This way master wont change even if new node with lower index joins the cluster.

Then we move to next important section which is [RW Split Router]. Here we again enter names for three servers and same user and password as the the [Galera Monitor] section.

Lastly, we need to edit those three servers, and enter names by which the MaxScale will see them, and IP addresses which will use to communicate with them. The names will be [server1][server2] and [server3], and for IP we will use private IP addresses to avoid having to encrypt the traffic.

Now on maxscale server, which is node-04, we are going to configure and start the maxscale proxy database. First lets set up the ufw to allow connections on needed ports.

ufw allow 6603
ufw allow 4442

Then backup the config file

mv /etc/maxscale.cnf /etc/maxscale.cnf.bk

After the file have been backed up and moved, lets make new file from scratch.

nano /etc/maxscale.cnf

There, you can use this as skeleton for configuration, except of course you need to change the bold parts:

[maxscale]
threads=4
syslog=0
maxlog=1
log_to_shm=1
log_warning=1
log_notice=1
log_info=0
log_debug=0
[Galera Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale
passwd=you-password-here
monitor_interval=2000
disable_master_failback=1
available_when_donor=1
[qla]
type=filter
module=qlafilter
options=/tmp/QueryLog
[fetch]
type=filter
module=regexfilter
match=fetch
replace=select
[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=you-password-here
max_slave_connections=100%
max_slave_replication_lag=30
[CLI]
type=service
router=cli
[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=3306
[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
address=127.0.0.1
port=6603
[server1]
type=server
address=10.132.84.235
port=3306
protocol=MySQLBackend
[server2]
type=server
address=10.132.86.222
port=3306
protocol=MySQLBackend
[server3]
type=server
address=10.132.19.195
port=3306
protocol=MySQLBackend

After this have been saved, you can start the maxscale service.

systemctl start maxscale.service

And test whether it is working

maxadmin -pmariadb list servers

Master-Salve ServersList

Conclusion

We have successfully installed MaxScale proxy database as the load balancer for our Galera cluster running on 3 Ubuntu 20.04 LTS (Focal) nodes with fourth node for MaxScale. MaxScale is good solution for large clusters. Today we made smallest possible configurations but scaling out from here is possible. I hope this article was useful for introducing yourself with MaxScale configuration.

Have a good day! :)