In the previous part, you have set up the master-slave synchronization. A simple manual or fully automatic failover functionality is not included. In this section, you set up a relatively simple failover functionality by means of MaxScale (and MariaDB Monitor).

• MariaDB MaxScale is a database proxy that simplifies / improves the high availability, scalability, and security of MariaDB. Some features supported by MaxScale: automatic failover, read-write splitting and query blocking (i.e. sort of a database firewall). See this page for a complete overview of the features.

• MariaDB Monitor is part of MaxScale and is responsible for monitoring the status of the cluster, performing either manual failover or fully automatic failover and rejoin when a server comes offline after downtime. In the case of the automatic failover, this means that if a master goes offline, one of the slaves automatically becomes the new master and when the old master comes back, it is added as a slave.

Manual or automatic failover

In step 7 of installing and configuring MaxScale, you will make a choice between automatic or manual failover. The choice you make also has implications for the next part in these tutorial series, in which we explain how to combine your SQL cluster with a PHP application (e.g. a website) and with WordPress. We, therefore, first consider why you would opt for automatic or manual failover.

Why you should not use automatic failover

There is one very important reason why you should not opt ​​for automatic failover, and that is also a particularly good reason: split-brain situations. In short, split-brain situations arise when SQL servers can no longer see each other but are still accessible to the outside world. This creates a situation where both / all SQL servers are promoted to master and only write data to themselves, without knowing so from each other. The databases on the VPSs then have different contents per SQL server. 

It is very difficult (and annoying) to solve the consequences of a split-brain problem. The chances of a split-brain situation arising are howevery very small, especially when you use two or more availability zones, but the possible consequences are enormous. Therefore, make an informed decision before you opt for manual or automatic failover. If you choose manual failover, you are thus opting for database consistency, over the convenience of fully automated failover. 

Split-brain is such an important and complicated subject that we have devoted a separate article to this. Click here for our article on split-brain problems. 

Why you should use automatic failover 

In almost all cases, it is perfectly fine to opt for automatic failover. The chances of a split brain situation are after all very small. Suppose a VPS goes offline because, for example, the CPU of a hypervisor is broken, or a VPS storage server goes offline, then it is very nice to have automatic failover. Are you adjusting the configuration and are you taking a VPS off-line? Then it is also a great solution. You do not have to worry about anything in such a scenario as MaxScale takes care of everything. 

If you use a database that only performs read queries or write queries are only executed by yourself (e.g. a WordPress website where visitors cannot register or make changes), automatic failover is also an excellent and risk free option. In principle, you will in such a use case not run a risk of split-brain situations.

There are more scenarios imaginable than described here. Do you still miss information in this or the split-brain article to make a choice between automatic or manual failover? Let us know in a response to this, or the split-brain article. We will then try to answer your question in this article as soon as possible.

Installing and configuring MaxScale 

We install and configure MaxScale in the following steps. MariaDB Monitor is part of MaxScale and does not have to be installed separately. Follow the steps below on all VPSs in your database cluster.

Step 1

Maxscale uses port 4006 for Round Robin connections (loadbalancing) and port 4008 for Read-Write splitting. Open both ports on all SQL servers in your setup. 

In the commands below, replace 192.168.1.0/24 with the IP range the addresses of your private network belong to. Alternatively, you could whitelist the specific IP addresses one by one instead.

Firewalld (CentOS, AlmaLinux, Rocky Linux)

firewall-cmd --permanent --zone=public --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=4006 protocol=tcp accept'
firewall-cmd --permanent --zone=public --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=4008 protocol=tcp accept' firewall-cmd --reload 
firewall-cmd --reload

UFW (Ubuntu/Debian):

ufw allow from 192.168.1.0/24 to any port 4006
ufw allow from 192.168.1.0/24 to any port 4008

Step 2

Now first install MaxScale; it is not a standard part of MariaDB. At the time of the latest update of this tutorial (2022) 6.2.4 is the current stable version of MaxScale. For CentOS Stream 9, AlmaLinux 9 and Rocky Linux 9 no seperate version is yet available. The latest version can be found here and here.

The installation link differs per OS and version of your OS. Always check the link above for the correct address.

CentOS 7:

yum -y install https://downloads.mariadb.com/MaxScale/latest/centos/7/x86_64/maxscale-6.2.4-1.rhel.7.x86_64.rpm

CentOS Stream 8 / AlmaLinux 8 / Rocky Linux 8:

dnf -y install https://downloads.mariadb.com/MaxScale/latest/centos/8/x86_64/maxscale-6.2.4-1.rhel.8.x86_64.rpm

Ubuntu 18.04

wget https://dlm.mariadb.com/2344070/MaxScale/6.4.1/packages/ubuntu/bionic/x86_64/maxscale-6.4.1-1.ubuntu.bionic.x86_64.deb
dpkg -i maxscale-2.2.14-1.ubuntu.bionic.x86_64.deb
apt-get install -f

Ubuntu 20.04

wget https://dlm.mariadb.com/2344057/MaxScale/6.4.1/packages/ubuntu/focal/x86_64/maxscale-6.4.1-1.ubuntu.focal.x86_64.deb
dpkg -i maxscale-6.4.1-1.ubuntu.focal.x86_64.deb
apt-get install -f

Ubuntu 22.04

wget https://dlm.mariadb.com/2344079/MaxScale/6.4.1/packages/ubuntu/jammy/x86_64/maxscale-6.4.1-1.ubuntu.jammy.x86_64.deb
dpkg -i maxscale-6.4.1-1.ubuntu.jammy.x86_64.deb
apt-get install -f

Step 3

Make sure that MaxScale starts automatically after a reboot:

systemctl enable maxscale

Step 4

For MaxScale, you need a new database user. This user is used by the services of which MaxScale consists to retrieve user authentication data.

You create the user using the following commands (you are free to change the name maxscale and the password maxscale_pw to your liking):

mysql -u root -p
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
exit

Step 5

Next, you generate an encrypted password. For a number of the MaxScale services, you put a password in the configuration file /etc/maxscale.cnf. For security reasons, it is advisable to encrypt the password.

You encrypt your password using the commands below, replacing maxscale_pw with the password you use for the maxscale user in the previous step.

maxkeys /var/lib/maxscale/
maxpasswd maxscale_pw

You get to see a series of characters like 96F99AA1315BDC3604B006F427DD9484 as the output. This is the encrypted password and you need it for the next step. Make sure you save the generated password.

Step 6

The maxkeys /var/lib/maxscale command creates a set of encryption keys in /var/lib/maxscale/.secrets. The owner of this file automatically becomes the 'root' user or your own user account if you used sudo, but  it's the 'maxscale' user which actually needs access.

Change the owner to maxscale.

chown maxscale /var/lib/maxscale/.secrets

You do not further adjust the rights with chmod, because then maxscale no longer works (the source code specifies that the file owner may only have read permissions, and the group or third parties are not allowed to have any rights at all).

Step 7

Open your MaxScale configuration file: 

nano /etc/maxscale.cnf

Step 8

You already see a default configuration in this file. Edit this file so it looks like the example below (but using your own data). There are several options here that are very important for the way you want to manage your SQL cluster. Therefore, we strongly advise you not to skip the explanation under the configuration. 

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-configuration-usage-scenarios/

[maxscale]
threads=auto

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[server1]
type=server
address=192.168.1.1
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.1.2
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-monitor/

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscale
passwd=17352CBFFB3D22C4625E030246888BA9
monitor_interval=2000
auto_failover=true
auto_rejoin=true

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readconnroute/

[Read-Only-Service]
type=service
router=readconnroute
servers=server1,server2
user=maxscale
passwd=17352CBFFB3D22C4625E030246888BA9
router_options=master,slave

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-readwritesplit/

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
passwd=17352DBFFB3D22C4625F030246888BA9

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Read-Only-Listener]
type=listener
service=Read-Only-Service
protocol=MariaDBClient
port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
#address=192.168.1.100
socket=/tmp/ClusterMaster

Explanation of the MaxScale configuration

• MaxScale] threads: MaxScale uses all the CPU cores of your VPS with the 'auto' option. This is preferred for a dedicated SQL server.
• [server1] & [server2]: Define all the servers that you use. Never change the type and the protocol. The only thing you adjust here is the IP and the port to that of your SQL servers. If you use more than two servers, you add another section [server3], [server4], etcetera, depending on how many you use.
• [MariaDB Monitor]: There are a few fields here that need to be modified.
  • After 'servers', you include the names of all servers that you have set up, for example, server1, server2.
  • After 'user', add the user you created for MaxScale (step 3). This user is used by MariaDB Monitor for monitoring the slave status.
  • For 'passwd', use the encrypted password which you created in step 7.
  • The 'monitor interval' is displayed in milliseconds.
  • Auto_failover ensures that the slave is promoted to master if your master becomes unreachable. If you prefer to keep control and manually perform failovers after you have first looked at what is going on with your SQL cluster, set this option to false and use the manual switchover command discussed at the bottom of this article.
  • After a failover, Auto_rejoin ensures that the old master is automatically added back to your cluster as a slave once it is reachable again. Regardless of whether you use manual or automatic failover, you usually want to leave this option on true.
• [Read-Only-Service]: Provides automatic (lightweight) load balancing. Add the MaxScale user and the encrypted password that you created earlier. Router options are set to master, slave so that the load is divided between masters and slaves.
• [Read-Write-Service]: This service splits your read & write queries. We use this feature in these tutorial series to link the write-queries to a virtual IP (more about this in the next section).
• [Read-Only-Listener]: The service that listens to actions on port 4008 on which the Read-Only-Service performs actions.
• [Read-Write Listener]: This service listens to all write queries. The address determines where these queries go. In principle, the read-write listener automatically uses the current master, but in order to make it easier for you to use your database in the configuration of your website or application, we will link a virtual IP address to the read-write-listener in the next section. Comment the address line for now off as in the example (in the next part, you first choose an IP address).
• The socket is the name of the protocol module that provides communication between the VPS and MaxScale. This is read from the file /tmp/ClusterMaster

Save the changes and exit /etc/maxscale.cnf (ctrl + w> y> enter).

SSL falls outside the scope of this tutorial series. If you would like to use SSL, you can enable this in the configuration of MaxScale (/etc/maxscale.cnf). More information about this can be found on this page under 'Server and SSL'.

MariaDB Monitor

The user you set up above for the MariaDB Monitor needs MySQL super- or replication client privileges. MariaDB Monitor will not work without these rights and MaxScale (and therefore MariaDB Monitor) will not start.

Therefore, we are going to give these privileges to the MariaDB Monitor user which you set up in the previous section 'MariaDB replication'.

Step 1

Start a MySQL shell:

mysql -u root -p

Step 2

Give the 'maxscale' user all privileges. MaxScale needs these to use the MariaDB Monitor, among other things. For your master, use the following commands:

GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

Use the following commands on your slave:

GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

In both cases, replace "maxscale" by the name of the account that you have set under step 7 of "Installing and configuring MaxScale" for your MariaDB Monitor and "password" by the corresponding (unencrypted) password.

Step 3

Finally, complete your changes by restarting MaxScale on your VPSs:

systemctl restart maxscale

During the configuration of MaxScale, you have already taken care of the further configuration of MariaDB Monitor. Therefore, no further action for the configuration of MariaDB Monitor is required.

Manual Switchover

If you want to perform a failover manually or, for some other reason, move the master role from one server to another server, you use a switchover command. The syntax for this command is as follows:

maxctrl call command mariadbmon switchover MariaDB-Monitor server1 server2

• call command: indicates that a module is being addressed
• mariadbmon: the name of the called module
• switchover: the command you call
• MariaDB-Monitor: the name of your MariaDB-Monitor in /etc/maxscale.cnf (MariaDB-Monitor is the default value)
• server1: the name of the server that you want to make the new master
• server2: the name of the current master

The switchover command is also a great way to test your database cluster. Try it now to test the functioning of your cluster.

Solving MaxScale problems

If you encounter a problem, MaxScale usually logs the cause very clearly in the journalctl-log. You can find it with the command:

journalctl -xe -u maxscale

Also check for possible error messages in your system logs:

nano /var/log/messages

Check the logs on both your master and slave! Sometimes one won't log a clear cause of a problem, but the other will.

You now have a nice SQL cluster, but ... how do you link this to your services, such as your website? We will discuss this in the next part of this tutorial: using your database cluster.