October 2, 2017

Getting Started with MariaDB Galera and MariaDB MaxScale on CentOS

Introduction

A good thing with 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 these together this turn slightly more difficult.

This blogpost then aims at showing 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.

Target system

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 3 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
192.168.0.183 MariaDB Galera Cluster node 3


Installing MariaDB Galera Cluster

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.

Linux settings

There are just a few things that we are 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.

Disable SELinux

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.

Disable firewalld

Firewalld is a standard service that is disabled using the systemctl command:

$ sudo systemctl disable firewalld


Set hostname

This is real 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 different :-)

Rebooting and checking the new settings

At this point it is best to reboot to ensure that your setting 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[1]: Stopped firewalld - dynamic firewall daemon.


Make sure that you disable SELinux and firewalld on all the four machines we are using if you are following this example.

Software installation

Before we install the software we need to set up the MariaDB repository on all 4 servers:

$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash


Having run this on the four servers, let us 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.

Setting up 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 needs 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_bin setting controls if 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_increment columns. Using mode 2 here is very important for Galera to work properly. In short, mode 2 cause much less locking during auto_increment generation 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.

Starting MariaDB 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 commandline:

$ 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 bootstraping, so here, on 192.168.0.182, we start MariaDB in the usual fashion:

$ sudo systemctl start mariadb.service


We should now have 2 nodes running in the cluster, let's check it out from the MariaDB commandline 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 2 nodes now, so then on 192.168.0.183, start MariaDB and we have a complete 3-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 bara 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 and 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 [maxscale] section.
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 has 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.

Global settings

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 1 thread is enough.

Servers

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. 

Monitors

A monitor is the 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.

Services

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 use to connect to the servers in the cluster to get authentication data.
We are for now ignoring the management MariaDB MaxScale services.

Listeners

This last type of plugin we define are the listeners, and these are the plugins that implement that 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. A follow up blogpost or two are planned to show some more advanced settings.

Happy SQL'ing
/Karlsson

About Anders Karlsson

Anders Karlsson has spent many years in the database industry, working for companies such as Oracle, Informix and MySQL. He works at MariaDB Corporation as Principal Sales Engineer since 2012.

Read all posts by Anders Karlsson