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, costs are charged to MaxScale at the time of writing (not to MariaDB itself). So, therefore, two applications with two own database servers each 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 advise you to contact 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 small, 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 extensive 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 other cases, it is perfectly fine to opt for automatic failover. 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 option. In principle, you do 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.
The installation link differs per OS and version of your OS. Always check the link above for the correct address.
yum -y install https://downloads.mariadb.com/MaxScale/2.2.10/rhel/7/x86_64/maxscale-2.2.10-1.rhel.7.x86_64.rpm
wget https://downloads.mariadb.com/MaxScale/2.2.14/ubuntu/dists/xenial/main/binary-amd64/maxscale-2.2.14-1.ubuntu.xenial.x86_64.deb dpkg -i maxscale-2.2.14-1.ubuntu.xenial.x86_64.deb apt-get install -f
wget https://downloads.mariadb.com/MaxScale/2.2.14/ubuntu/dists/bionic/main/binary-amd64/maxscale-2.2.14-1.ubuntu.bionic.x86_64.deb dpkg -i maxscale-2.2.14-1.ubuntu.bionic.x86_64.deb apt-get install -f
Make sure that MaxScale starts automatically after a reboot:
systemctl enable maxscale
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
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.
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, 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).
Open your MaxScale configuration file:
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 # This service enables the use of the MaxAdmin interface # MaxScale administration guide: # https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-22-maxadmin-admin-interface/ [MaxAdmin-Service] type=service router=cli # 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 [MaxAdmin-Listener] type=listener service=MaxAdmin-Service protocol=maxscaled socket=default
- 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).
- [MaxAdmin Service]: Provides the MaxAdmin interface and allows you to use the MaxAdmin command to start a shell from which you manage MaxScale.
- [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
- [MaxAdmin Listener]: The service that listens to all commands that are executed from the MaxAdmin shell.
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'.
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'.
Start a MySQL shell:
mysql -u root -p
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.
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.
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:
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.
Should you have any questions left regarding this article, do not hesitate to contact our support department. You can reach them via the ‘ContactUs’ button at the bottom of this page.
If you want to discuss this article with other users, please leave a message under 'Comments'.