A perk of using Galera as a high availability solution for MariaDB is that it is rather easy to set up and use, as far as high availability can be easy. Also, both MariaDB and Galera are well-documented. To use MariaDB MaxScale for failover with a MariaDB Galera Cluster is also rather straightforward and well-documented. The issue is that there are three technologies at play here, and although they are all well-documented and reasonably easy to use, when it comes to using all of them together, things get slightly more difficult.
This post then aims to show all these three technologies at play together, starting from scratch and creating a MariaDB Galera Cluster based on MariaDB Galera 10.2.7 and MaxScale 2.1. The operating system we are going to use for this example is CentOS version 7.2. Before we get started, I also want to mention that we are here looking at a minimal initial installation; we will not look at many fancy features in either product.
Also, before we start, let's have a look at what we aim in terms of a completed system. We will build a MariaDB Galera Cluster with three nodes and a fourth node for MaxScale. The cluster is built on three separate virtual machines in this case, and the IP addresses of all the machines in this set up is:
|IP address||Node use|
|192.168.0.180||MariaDB MaxScale node|
|192.168.0.181||MariaDB Galera Cluster node 1|
|192.168.0.182||MariaDB Galera Cluster node 2|
MariaDB Galera Cluster node 3
In this section, we will set up the MariaDB Galera Cluster from start to finish. As said above, we will, for the most part, look at only the basic settings for the cluster to get started, no advanced settings will be used and for a production environment, you want to fine-tune this.
There are just a few things that we need to adjust in the standard Linux installation before we commence, and this is to disable SELinux and the Linux firewall (which is firewalld in CentOS and RedHat 7.0 and up, and not iptables) and also set the hostname.
For all intents and purposes, in a production environment running with SELinux enabled is often a good idea. For the purposes of testing as we are doing here, though, we do not want SELinux around at all. For this, make sure that your SELinux configuration in the file
/etc/selinux/config looks something like this:
# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted
The change here is the SELINUX setting, of course.
firewalld is a standard service that is disabled using the
$ sudo systemctl disable firewalld
This is really simple, and we do this to be able to tell from the MariaDB command prompt which server I am connecting to when we use MariaDB MaxScale. On each node, run something like this:
$ sudo hostname node181
And be sure to name all the nodes appropriately and differently!
Rebooting and Checking the New Settings
At this point, it is best to reboot to ensure that your settings are enabled. So reboot now and then check the status of SELinux and firewalld:
$ sestatus SELinux status: disabled $ systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Aug 10 12:24:09 localhost.localdomain systemd: Stopped firewalld - dynamic firewall daemon.
Make sure that you disable SELinux and firewalld on all four machines that we are using, if you are following this example.
Before we install the software, we need to set up the MariaDB repository on all four servers:
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
Having run this on the four servers, let's now go on with installing MariaDB Galera server on the three nodes where this is appropriate. In the case here, we are looking at nodes 192.168.0.181, 192.168.182, and 192.1678.0.183. On these three nodes, run this this:
$ sudo yum -y install MariaDB-server
When this is completed, we should have MariaDB Server installed. The next thing to do, then, is to install MariaDB MaxScale on the 192.168.0.180 box:
$ sudo yum -y install maxscale
Now, only one thing remains to install — which, strictly speaking, is optional, but it is used when we test what we are setting up here — and that is to install the MariaDB client programs on the machine we run MariaDB MaxScale on, so on 192.168.0.180 run:
$ sudo yum -y install MariaDB-client
With that in place, we are ready to get to the next step, which is to configure a MariaDB Galera Cluster.
Before we start up MariaDB, we need to configure the cluster. This is not complicated, but there are a few settings that need to be in place. Again, note that what we are setting up here is the bare minimum required to get started. In real life, there are a bunch of more parameters you would want to set up. Also, beyond Galera, I am more or less leaving MariaDB as it is, again with the exception of a few things that Galera requires. All in all, we are not configuring any InnoDB cache or metadata cache or defining a non-default name of the cluster.
We have to edit the file
/etc/my.cnf.d/server.cnf and we are to adjust the Galera-specific settings on the nodes 192.168.0.181, 192.168.182, and 192.1678.0.183. Edit the
[galera] section to look like this on all three nodes:
[galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.0.181,192.168.0.182,192.168.0.183 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2
Let's have a look at these settings now and see what they do:
wsrep_on: This is a session-level flag to indicate of the operations we are performing will be replicated, much like how the
sql_log_binsetting controls whether statements are written to the binlog when using MariaDB Replication. The default of this is ON, but we set it anyway, to be safe.
wsrep_provider: This points to the location of the Galera library. Although MariaDB is set up to use Galera from scratch, you still have to point to the Galera library. This is installed as part of the MariaDB-Server installation above.
wsrep_cluster_address: This is where we define the nodes in the cluster. In general, you don't need to list all nodes, and new nodes can be added later to a running cluster. But in this case, we know what the cluster looks like, so we set it up here.
binlog_format: Although the binlog, in terms of the actual binlog files, isn't used by Galera, the binlog facility is. And for Galera to work, you have to run with row format in the binlog.
default_storage_engine: Again, this is the default value, but just to be safe, let's set up MariaDB Server to explicitly use the InnoDB Storage Engine, which is the only engine supported by Galera.
innodb_autoinc_lock_mode: This setting defines how the InnoDB Storage Engine generates values for
auto_incrementcolumns. Using mode 2 here is very important for Galera to work properly. In short, mode 2 cause much less locking during
auto_incrementgeneration and hence doesn't interfere with other locking. Values other than 2 can cause deadlocking and other issues with Galera.
With these settings in place, we are ready to start the cluster.
The way starting a Cluster from scratch works is that we run a process called a bootstrap, and the reason this is a bit different from the usual MariaDB startup is that for HA reasons, a node in a cluster attaches to one or more other nodes in the cluster, but for the first node, this is not possible. This is not complicated, though. There is a script that is included with MariaDB Server that manages this — but note that this script is only to be used when the first node in a Cluster is started with no existing nodes in it. In this case, on 192.168.0.181, run:
$ sudo galera_new_cluster
With this in place, we should have a MariaDB server running. Let's have a look:
$ ps -f -u mysql | more UID PID PPID C STIME TTY TIME CMD mysql 3472 1 0 14:42 ? 00:00:00 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
As you can see, the
galera_new_cluster script has started MariaDB, but with some additional parameters — notably,
--wsrep_new_cluster. Before we continue, let's also look at the status of the cluster from the command line:
$ mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.2.7-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 1 | +--------------------+-------+ 1 row in set (0.00 sec)
This shows that we have a running cluster (if not, the value of
wsrep_cluster_size would have been 0), but there is just one node. Let's now start another node, and note that although we are starting a cluster from scratch, only the first node needs bootstrapping. So here, on 192.168.0.182, we start MariaDB in the usual fashion:
$ sudo systemctl start mariadb.service
We should now have two nodes running in the cluster. Let's check it out from the MariaDB command line on 192.168.0.181:
MariaDB [(none)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 2 | +--------------------+-------+ 1 row in set (0.00 sec)
Yes, we have two nodes now, so on 192.168.0.183, start MariaDB — and we have a complete three-node cluster running.
$ sudo systemctl start mariadb.service
And on 192.168.0.181, let's verify that we are done. So far:
MariaDB [(none)]> show global status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec)
Getting Started With MariaDB MaxScale
MariaDB MaxScale will, in this scenario, act as a router to the cluster, ensuring that traffic is directed to the appropriate server. There are a few means of dealing with this, and MaxScale also has a lot of options, but again, we will be dealing with setting up the bare minimum for the use case we have in mind.
Before we get started, though, we need to set up the MariaDB servers to work with MariaDB MaxScale. There are a few reasons for this is. One reason is that MaxScale monitors the Cluster out-of-band, which means that the cluster is constantly monitored, even if there are no user connections. Another reason is that when there is a connection from a client through MaxScale to the MariaDB Cluster, it is MaxScale that does the user authentication and authentication data is picked up from the MariaDB cluster.
Setting Up MariaDB for MariaDB MaxScale
First, we need to set up a user that MariaDB MaxScale use to attach to the cluster to get authentication data. On 192.168.0.181, using the MariaDB command line as the database root user:
$ mysql -u root Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.2.7-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> create user 'myuser'@'192.168.0.180' identified by 'mypwd'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant select on mysql.user to 'myuser'@'192.168.0.180'; Query OK, 0 rows affected (0.01 sec)
Following this, we need some extra privileges for table- and database-level grants:
MariaDB [(none)]> grant select on mysql.db to 'myuser'@'192.168.0.180'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> grant select on mysql.tables_priv to 'myuser'@'192.168.0.180'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant show databases on *.* to 'myuser'@'192.168.0.180'; Query OK, 0 rows affected (0.00 sec)
Note that the above commands need only be run on one of the servers in the cluster (say, 192.168.0.181), as these grants are replicated to all servers by virtue of Galera.
With this in place, we are ready to configure MariaDB MaxScale.
Configuring MariaDB MaxScale
The configuration for MariaDB MaxScale is in the file
/etc/maxscale.cnf and in this case, we will create a new configuration from scratch instead of amending the existing one. I will explain the important aspects of this, but before that, this is what it looks like (and again, no fancy configuration, just the basics to get going):
# Globals [maxscale] threads=1 # Servers [server1] type=server address=192.168.0.181 port=3306 protocol=MySQLBackend [server2] type=server address=192.168.0.182 port=3306 protocol=MySQLBackend [server3] type=server address=192.168.0.183 port=3306 protocol=MySQLBackend # Monitoring for the servers [Galera Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=myuser passwd=mypwd monitor_interval=1000 # Galera router service [Galera Service] type=service router=readwritesplit servers=server1,server2,server3 user=myuser passwd=mypwd # MaxAdmin Service [MaxAdmin Service] type=service router=cli # Galera cluster listener [Galera Listener] type=listener service=Galera Service protocol=MySQLClient port=3306 # MaxAdmin listener [MaxAdmin Listener] type=listener service=MaxAdmin Service protocol=maxscaled socket=default
MariaDB MaxScale Configuration File Format
The format of the MariaDB MaxScale configuration file is, as you see above, similar to the one used by MariaDB Server. There are a few differences, though. One is that as MariaDB MaxScale does more or less everything through plugins, and this is reflected in the configuration file. Each instance of a plugin has a separate section and the name of the section is used when referenced from some other plugin — so the section names are not fixed, but rather are used to name an instance of a service. This is true except in the case of global settings, which are not related to any particular plugin and are placed in the
A related setting is the type setting, which defines what type of plugin this section related to, which is currently one of filter, listener, monitor, server, or service. In addition, all plugins have a setting that defines the name of the plugin to load or the name of the shared object file that MariaDB MaxScale will be loaded.
In this case, I have only one global setting, which is to set the number of MariaDB MaxScale threads that we have running. In this case, it is set to 1, which is the default. There might be many reasons to have a higher setting here, but for this simple example, one thread is enough.
The settings should be fairly obvious, with the protocol setting defining what protocol plugin is being used here, and MySQLBackend is the only option so far.
A monitor is a plugin that checks the status of servers and the important setting here is the module, which is set to galeramon in this case. There are a few different monitors available for different type of setup of the backend servers, but in this case, we are using Galera so galeramon is what we want to use.
The user and password settings define how the monitor connects to the backend servers to get the current status, and
monitor_interval defines how often, in milliseconds, that we connect to the servers and check status.
In this, we define two services. One which is our main service, and then we define an administrative service. The interesting service here is the Galera Service one, and the first thing we need to look at which router we will use, a router here being the actual implementation of the router plugin and there are several to choose from. Another thing we have to define is which servers make up the cluster that we are working with here. Finally, we set a user and password that MariaDB MaxScale uses to connect to the servers in the cluster to get authentication data.
We are for now ignoring the management MariaDB MaxScale services.
This last type of plugins we define are the listeners, and these are the plugins that implement the actual protocol that listens for client connections. The interesting listener here is Galera Listener, which listens on the MariaDB client protocol connections. Again, we are ignoring the MariaDB MaxScale management listener.
An important aspect of defining a listener is to use the correct service parameter, and this is set to the service which this listen connects to when there is a new connection. In this case, we connect to the Galera Service we define above.
Note that we have the listener running on the MariaDB Server default port of 3306 and that this is deliberate and works fine as MariaDB MaxScale runs on a separate server from the ones where MariaDB Server runs.
Starting and Testing MariaDB MaxScale
With the configuration described above in place, we are ready to start MaxScale on 192.168.0.180. To do this, just run:
$ sudo systemctl start maxscale.service
And before I leave you, let's test a few things. First, let's connect to the cluster through MariaDB MaxScale:
$ mysql -h 192.168.0.180 -u myuser -pmypwd Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4668 Server version: 10.0.0 2.1.5-maxscale MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]>
You can see that we are connected to MariaDB MaxScale now, but which server in the MariaDB Galera Cluster? Let's check it up!
MySQL [(none)]> show variables like 'hostname'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | node181 | +---------------+---------+ 1 row in set (0.00 sec)
It's the 181 server, it seems. Let's then try one more thing before we are done for today — let's stop MariaDB server on 192.168.0.181 and see what happens. On 192.168.0.181, run:
$ sudo systemctl stop mariadb.service
And then, we go back to our command prompt on 192.168.0.180 and see what happens when we access MariaDB Server from there.
$ mysql -h 192.168.0.180 -u myuser -pmypwd Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4668 Server version: 10.0.0 2.1.5-maxscale MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show variables like 'hostname'; +---------------+---------+ | Variable_name | Value | +---------------+---------+ | hostname | node182 | +---------------+---------+ 1 row in set (0.00 sec)
With that, we are done for now!