Installing Galera Cluster with MariaDB

Galera give you some nice cluster features. Like true Multi-Master configuration, synchronous replication, conflict detection and impromes the maintenance of your MariaDB servers with automatic provisioning, node isolation and rolling upgrades.

You need a Linux or Unix Operating System. I used Debian for this tutorial. Multiple nodes with an odd number of nodes. Three nodes are minimum for Galera. The nodes need to be equal configurated and with the identical software.

Firewall and Ports

Galera uses the following ports:

* 3306 TCP (Default MariaDB Traffic)
* 4567 TCP / UDP (Galera Cluster Communications)
* 4444 TCP (Incremental State Transfers)
* 4568 TCP (State Snapshot Transfers)

Make sure that your cluster nodes can communicate over this ports. I used a seperate internal network so i can just open the ports.
Here is an example for „ufw“ firewall:

sudo ufw enable
sudo ufw allow 3306/tcp
sudo ufw allow 4444/tcp
sudo ufw allow 4567/tcp
sudo ufw allow 4568/tcp
sudo ufw allow 4567/udp

Or use iptables. Here is an example to allow only the other cluster nodes:

iptables -A INPUT -p tcp -s 172.16.201.139 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

This allows the node with ip „172.16.201.139“ to connect to port 3306 via TCP.
Or allow a complete subnet (like your internal network):

iptables -A INPUT -p tcp -s 172.16.201.0/24 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

This allows all nodes with an ip of the subnet „172.16.201.0/24“ to connect to port 3306 via TCP.

SELinux

If you have SELinux (Security-Enhanced Linux) enabled on your server, it may block some operations of mysqld.

Disable SELinux for mysqld:

semanage permissive -a mysqld_t

Or open ports for galera:

semanage port -a -t mysqld_port_t -p tcp 3306
semanage port -a -t mysqld_port_t -p tcp 4444
semanage port -a -t mysqld_port_t -p tcp 4567
semanage port -a -t mysqld_port_t -p udp 4567
semanage port -a -t mysqld_port_t -p tcp 4568
semanage permissive -a mysqld_t

AppArmor

Some servers useing AppArmor by default. (Like Ubuntu) AppArmor can prevent mysqld from opening additional ports or running scripts. You need to disable AppArmor for mysqld.

ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/usr.sbin.mysqld
service apparmor restart

My setup

Three Debian Buster Servers. db01, db02 and db03. Every node has identical hardware and software.
The nodes have the following IP adresses: 172.16.201.139, 172.16.201.140 and 172.16.201.141

Installation

First install MariaDB on all nodes:

apt -y install mariadb-server mariadb-client

Secure each installation with the configuration script by running:

mysql_secure_installation

Set root password? [Y/n] Y
New password: 
Re-enter new password: 
Password updated successfully!
Remove anonymous users? [Y/n] Y
 ... Success!
Disallow root login remotely? [Y/n] Y
 ... Success!
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reload privilege tables now? [Y/n] Y
 ... Success!
Cleaning up...
All done!

By default mysql listens only on the local 172.0.0.1 interface. Open „/etc/mysql/mariadb.conf.d/50-server.cnf“ and change the „bind_address“ attribute:

#bind-address = 127.0.0.1

Now mysql listens on all interfaces. Or change it to one ore more specific addresses. It is important that the other nodes can reach the each other.

bind-address = 172.16.201.139

First node

On your first node open „/etc/mysql/mariadb.conf.d/50-server.cnf“ again and add the following lines:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="172.16.201.139"

Now initialize Galera cluster:

systemctl stop mariadb 
galera_new_cluster
systemctl start mariadb 

Second node

Open „/etc/mysql/mariadb.conf.d/50-server.cnf“ again and add the following lines:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Specify cluster nodes
wsrep_cluster_address="gcomm://172.16.201.139,172.16.201.140,172.16.201.141"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="172.16.201.140"

Restart MariaDB

systemctl restart mariadb

Note: This may take a little bit longer than usual!

Third node

Open „/etc/mysql/mariadb.conf.d/50-server.cnf“ again and add the following lines:

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Specify cluster nodes
wsrep_cluster_address="gcomm://172.16.201.139,172.16.201.140,172.16.201.141"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="172.16.201.141"

Restart MariaDB

systemctl restart mariadb

Reconfigure first node

Open „/etc/mysql/mariadb.conf.d/50-server.cnf“ again and change this line:

wsrep_cluster_address="gcomm://172.16.201.139,172.16.201.140,172.16.201.141"

Controll MariaDB Galera Cluster Settings

Login into DB console as the cluster root user:

mysql -u root -p

Confirm if Cluster settings are OK.

show status like 'wsrep_%'; 

You should now see your cluster confiuguration. Check „wsrep_cluster_size“ which should be „3“.

Now create a database on the first node and confirm the status on the other nodes.

mysql -u root -p
CREATE DATABASE test_db;

Now login into DB console on the other nodes and check if the database is available:

mysql -u root -p
SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.001 sec)

Now stop database on the first node:

systemctl stop mariadb

Login into the second node and check the cluster with „show status like ‚wsrep_%‘;“

wsrep_cluster_size 2

Now create a second database:

create database test_db2;

On the first node start the database again:

systemctl start mariadb

After the database running again. Login as root and check if the second database is also available:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
| test_db2           |
+--------------------+
5 rows in set (0.001 sec)

Now your Galera Cluster is ready. You may want to use Load Balancing for your new cluster. In the next post i will describe how to create an Loadbalancer with HAProxy for your Galera Cluster.

And remember that replication is not a replacement for backup!

Upgrade

Want to test an upgrade?
Debian Buster currently delivers version 10.3 of MariaDB and Galera-3.

# mysql -V
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

You can install it from the mariadb repo, too:

apt install software-properties-common dirmngr
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
add-apt-repository 'deb [arch=amd64] http://mariadb.petarmaric.com/repo/10.4/debian buster main'
apt update
apt install mariadb-client mariadb-server

Now you should patched from 10.3 to 10.4:

mysql -V
mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for debian-linux-gnu (x86_64) using readline 5.

Repeat it on all nodes and you finished your first rolling upgrade.

More Informations to upgrading Galera Cluster: https://galeracluster.com/library/documentation/upgrading.html

More Links

• https://galeracluster.com/library/documentation/
• https://mariadb.com/kb/en/configuring-mariadb-galera-cluster/
• https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/
• https://galeracluster.com/library-media/pdf/galera-mariadb-installing.pdf