This is the fourth part of our Tutorial Series 'Setting up a redundant VPS environment'. If you are setting up a new redundant VPS environment, we recommend starting with part 1 and to not skip any parts.
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.
- Execute all steps in this article using a user with root privileges, unless otherwise specified.
- Perform the steps below on all VPSs in your cluster. If values differ per VPS, this is indicated.
- If you use three or more database servers for a single application, MaxScale charges costs at the time of writing (not to MariaDB itself). So, therefore, two applications with two database servers each using MaxScale is no problem, no matter how large the database servers are individual. If you use more than two database servers for a single database, we recommend contacting MariaDB about licensing MaxScale.
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.debdpkg -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.
The generated password differs per server. Therefore, you cannot use this encrypted password on another VPS, but you must generate a unique encrypted password on each VPS.
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.
- In the options covered below is where you choose for either automatic or manual failover. Please see the explanation below the code for more information.
- As of MaxScale 2.5 MaxAdmin has been removed and succeeded by maxctrl. The [MaxAdmin-Service] and [MaxAdmin-Listener] have therefore been removed from this instruction.
# 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
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.