MariaDB Galera cluster

For some time now I have been trying to get a MySql NDB cluster up and running. I have tried installing it from repositories as well as binaries… and I have failed every single time at one point or another. I usually got the management node up and running, but the data nodes and API (SQL) nodes were a completely different matter.

In the end I was forced to face the fact that I was unable to install it successfully, but it is such a shame to surrender the cluster entirely. There are a number of clustering options out there. I won’t even start to get into the different version, but the cluster I ended up installing successfully was the MariaDB Galera cluster.

I still had to restart the installation multiple times because I made a mistake here and there. You have just got to LOVE virtual computers and their ability to revert to different snapshots.

Setup

I am running the ESXi vSphere hypervisor on a Dell T3500 workstation with 12Gb of ram.

In the ESXi host I had prevously installed 5 machines to install the NDB cluster. Each of these machines was given 2 cores and 2Gb of RAM. Instead of reinstalling the machines from scratch I choose to go back to the first, more or less clean. snapshot of the machine and start the installation process from there.

From my regular computer I am using the MobaXterm (https://mobaxterm.mobatek.net/) client to connect to the different virtual computers through a SSH prompt.

In my Galera cluster I started out using three nodes. Unlike the NDB cluster where you have Management nodes, API nodes and data nodes the Galera cluster sinply has a number of nodes that replicate synchronously.

There are a number of steps where you need to do things to diverse configuration files. Personally I hate the VI editor that comes with Centos7 as a default and choose to install the nano editor instead. You should be able to install it by writing

yum install nano

Initial issues

One of the major issues I have faced while trying to install the cluster was the simple fact that a lot of tutorials online was old and outdated. After trying to use those tutorials I ended up more than once having to revert back to a clean image to start all over again.

In the end I managed to find a very good video tutorial in youtube that was fairly recent. What made it even better was the fact that the guy did EVERY step in the installation so that you could really follow everything from scratch. A lot of other tutorials tend to skip over “easy” stuff that ends up being the thing that comes back and bites you in the a** later on. The tutorial was a series in 7 parts going from initial setup to complete install.

Comparing to my attemt at installing the NDB cluster however the installation of the MariaDB Galera cluster was very straight forward and relativly easy… even for someone like me who is no Linux wiz.

Disclaimer

This tutorial is more in the way of making me remember what I have done as well as getting a quick way to get a MariaDB Galera Cluster up and running in a short amount of time. It may or may not work for you, and it will require a clean install.

This is only a test cluster. There is probably a ton of setting you need to do to get it production worthy, so don’t expect to be able to have a perfect HA cluster after doing the steps in this tutorial, but something that may become a HA cluster with a bit of tweking.

The first part of the tutorial will contains a bit more text with commands in italic and configuration files and printouts as blockquotes. At the end I will redo the commands in basicly a copy paste fassion.

Initial setup

There are a couple of things that needs to be done prior to the installation.

In the tutorials I have read/seen they usually tell you to disable the firewall and selinux. To me that sounds like a bad way of doing things. So far I have been able to get the firewall rules up and running, but there is still work that needs to be done to get the selinux working.

For this tutorial I will set the selinux to be permissive instead of disables, because that way it will keep on logging the things it would normally block if it was enforcing.

Most of this tutorial require that you do each of the steps on each of the nodes in the cluster, but some steps should be done only to one of the cluster nodes. If nothing is written then assume that whatever you do to one you do to the others.

Setup SELinux

First we need to edit the selinux configuration file

nano /etc/selinux/config

# 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=permissive
# SELINUXTYPE= can take one of three two values:
# targeted – Targeted processes are protected,
# minimum – Modification of targeted policy. Only selected processes are protected.
# mls – Multi Level Security protection.
SELINUXTYPE=targeted

After updating the configuration file we need to restart the computer for it to reload the configuration.

shutdown -r now

After rebooting the computer check the se status to see if the change was successful

sestatus

SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 31

Configuring SELinux

This is where I want to do more to be able to set SELinux to be enforcing to increase the security but so far I have only come a short way on the subject so I may or may not post an additional note on how I did this part.

Swap space

For this test system the system swap space will be more than enough, but in a real world application the amount of RAM and also swap space is very important to the Galera cluster. The cluster stores a lot of information in order to provide the synchronization. It will start out using RAM, but if there is not enough RAM the cluster will start to save the information to the swap space. I will not be going through how to set up more swap space but here is a nice link to how it is done.

https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-centos-7

Hosts file

While setting the cluster I will be using host names for the computers in the network. I am currently not sure if this is required, but there are certain parameters in the server configuration file that wants you to enter the node name.

nano /etc/hosts

If this is a fresh install then this file should contain only two lines

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

We need to add our cluster computers to the file

192.168.0.10    gcluster01
192.168.0.11    gcluster02
192.168.0.12    gcluster03

Test the connectivity from each of the nodes using the ping command.

ping gcluster01
ping gcluster02
ping gcluster03

OK, so now we have done the first part of the tutorial to prepare the different nodes for the coming installation and configuration

Installation

Now we need to install MariaDB. From MariaDB version 10.1 the Galera cluster is part of the main installation. Earlier versions had specific Galera packages. The MariaDB installation packages was not part of the yum repository be default so it was nessessary to add the repositories manually.

Adding the MariaDB repository

First we need to add a link to the repository

nano /etc/yum.repos.d/MariaDB.repo

This will open a blank file, copy the blockquote below to the new file.

# MariaDB 10.2 CentOS repository list – created 2018-09-21 05:45 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

A very good resource to find out exactly what this should be is the MariaDB foundation. This is where I got the above repository information. If you use a different linux distro then this may be different. This is the link I used

https://downloads.mariadb.org/mariadb/repositories/#mirror=lund&distro=CentOS&distro_release=centos7-amd64–centos7&version=10.2

Installing MariaDB

Time to do the actual install.

yum install MariaDB-server MariaDB-client MariaDB-common

After the installation completes you can check to see which components that was installed

rpm -qa | grep -i mariadb

MariaDB-compat-10.2.17-1.el7.centos.x86_64
MariaDB-client-10.2.17-1.el7.centos.x86_64
MariaDB-common-10.2.17-1.el7.centos.x86_64
MariaDB-server-10.2.17-1.el7.centos.x86_64

Setting up firewall rules

In most tutorials people just tell you to disable the firewall to make things work. You can do this by entring

systemctl disable firewalld

Personally I think this approach is kind of scary. Sure it may work for a test system, but shutting off the firewall seem like a really bad idea if you ask me.

The Galera cluster use 4 ports for replication. The first one is the standard connection port (default is 3306) the rest of the ports used are

4567 TCP and UDP
4568 TCP
4444 TCP

The Galera cluster also require that you set the service up in the firewall. The commands I used was

firewall-cmd –zone=public –add-service=mysql –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanent
firewall-cmd –zone=public –add-port=4567/tcp –permanent
firewall-cmd –zone=public –add-port=4568/tcp –permanent
firewall-cmd –zone=public –add-port=4444/tcp –permanent
firewall-cmd –zone=public –add-port=4567/udp –permanent
firewall-cmd –reload

The last line will reload the firewall using the new ports. This can be archived by restarting the computer as well I guess, but why do it when there is no need for it.

Basic Configuration

If everything has went according to plan it is now time to do a basic configuration. We need to set MariaDB up as well as configure the cluster.

DO THE REST OF THIS SECTION ON ONE NODE ONLY!

systemctl start mariadb

Run the secure installation of MariaDB.

If this is a fresh install then the root password will be blank. While the command is running choose to remove anonymous access, test database etc. Basicly answer yes to all questions that the secure install asks you.

mysql_secure_installation

Basic Galera Configuration

When you first open the configuration all galera settings will be commented out. In the config file below I have marked the lines that has changed or been added in bold text.

nano /etc/my.cnf.d/server.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://192.168.0.10,192.168.0.11,192.168.0.12″
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

wsrep_cluster_name=”MariaDBCluster”
wsrep_sst_method=rsync
wsrep_node_address=”192.168.0.10″
wsrep_node_name=”gcluster01″

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.2 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don’t understand
[mariadb-10.2]

The setting wsrep_cluster_address should contain all the IPs of the nodes of the cluster. gcomm implies that it is a group communication.

The settings wsrep_node_address and wsrep_node_name is node specific and must be changed to the IP and hostname of each node.

The setting bind-address can be set to listen to a specific IP. With the setting set as 0.0.0.0 it will listen to any IP address on the node.

In order for the replication to work you must make sure that the replication provider in setting wsrep_provider exists.

ls -al /usr/lib64/galera/libgalera_smm.so

-rwxr-xr-x. 1 root root 34691616 13 feb 2018 /usr/lib64/galera/libgalera_smm.so

Starting the cluster

Installation and basic configuration is now done. It is time to start the cluster.

First node

This is the node where you started MariaDB at an earlier stage of the tutorial. After configuring the cluster we now need to stop MariaDB and start it as a cluster instead.

systemctl stop mariadb

Create a new cluster and start MariaDB again by entring

galera_new_cluster

If everything goes as planned and MariaDB starts without any issues then it is time to see if our new cluster is up and running.

First we need to connect to MariaDB

mysql -uroot -p

Enter the password you created during the secure install.

We then wish to see the size of our cluster. At this point in time it should be 1.

show status like ‘wsrep_cluster_size’;

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

Second and third node

If everything is set up the way it should be all you have to do to join the node to the cluster is to start MariaDB

systemctl start mariadb

Connect to MariaDB to check the cluster

mysql -uroot -p

Enter the same password as you used on the first node.

show status like ‘wsrep_cluster_size’;

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.00 sec)

This time the cluster size should be 2. Go back to the first node and rerun the query. The result should show you that the cluster size is 2 on the first node as well.

For the third node simply replicate the same steps as in node 2.

Checking replication

The first sign that replication is up and running and working is the fact that you could log into all nodes even if you only ran the secure installation on the first node. The user and password has replicated to the other nodes, but to be able to check the actual table replication you could do something simple like this

Log into mysql on one of the nodes

mysql -uroot -p

CREATE DATABASE db1;
USE db1;
CREATE TABLE testtable (c1 INT);
INSERT INTO testtable (c1) VALUES (1);

Now log into the server on the other nodes and check to see if the new database and table has been replicated successfully.

mysql -uroot -p

USE db1;
SELECT * FROM testtable;

+------+
| c1   |
+------+
| 1    |
+------+

That is it. The cluster should now be up and running!

The quick copy paste way

This is more a copy paste of the above tutorial. This will not do specific checks to see if the cluster is up and running nor do any replication checks. It is only there to speed the installation up.

Initial setup

nano /etc/selinux/config

# 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=permissive
# SELINUXTYPE= can take one of three two values:
# targeted – Targeted processes are protected,
# minimum – Modification of targeted policy. Only selected processes are protected.
# mls – Multi Level Security protection.
SELINUXTYPE=targeted

shutdown -r now

sestatus

SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: permissive
Mode from config file: permissive
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 31

nano /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.10    gcluster01
192.168.0.11    gcluster02
192.168.0.12    gcluster03

ping gcluster01
ping gcluster02
ping gcluster03

Installing MariaDB

nano /etc/yum.repos.d/MariaDB.repo

# MariaDB 10.2 CentOS repository list – created 2018-09-21 05:45 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

yum install MariaDB-server MariaDB-client MariaDB-common

rpm -qa | grep -i mariadb

MariaDB-compat-10.2.17-1.el7.centos.x86_64
MariaDB-client-10.2.17-1.el7.centos.x86_64
MariaDB-common-10.2.17-1.el7.centos.x86_64
MariaDB-server-10.2.17-1.el7.centos.x86_64

Firewall setup

firewall-cmd –zone=public –add-service=mysql –permanent
firewall-cmd –zone=public –add-port=3306/tcp –permanent
firewall-cmd –zone=public –add-port=4567/tcp –permanent
firewall-cmd –zone=public –add-port=4568/tcp –permanent
firewall-cmd –zone=public –add-port=4444/tcp –permanent
firewall-cmd –zone=public –add-port=4567/udp –permanent
firewall-cmd –reload

Basic configuration

ONLY DO THIS TO ONE OF THE NODES!

systemctl start mariadb
mysql_secure_installation

Galera configuration

nano /etc/my.cnf.d/server.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://192.168.0.10,192.168.0.11,192.168.0.12″
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

wsrep_cluster_name=”MariaDBCluster”
wsrep_sst_method=rsync
wsrep_node_address=”192.168.0.10″
wsrep_node_name=”gcluster01″

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.2 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don’t understand
[mariadb-10.2]

Check that the galera provider file exists
ls -al /usr/lib64/galera/libgalera_smm.so

-rwxr-xr-x. 1 root root 34691616 13 feb 2018 /usr/lib64/galera/libgalera_smm.so

Starting the cluster

First node (Same node as the one in Basic configuration)

systemctl stop mariadb
galera_new_cluster

Second node

systemctl start mariadb

Third node

systemctl start mariadb

Done!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s