MariaDB Galera Cluster Configuration Guide

Introduction

This guide walks you towards installing and setting up a MariaDB cluster with galera on Redhat 8 enterprise linux.

Prepare Redhat server

Make sure the Redhat server is registered and subscription is active because some dependencies are installed from the AppStream/BaseOS repos.

The command below prints out the subscriptions consumed by the system.

# subscription-manager list --consumed

Adding Mariadb repos

Use the Script on MariaDB website to configure the repos on all systems where MariaDB and Galera are run:

 curl -LO https://r.mariadb.com/downloads/mariadb_repo_setup && chmod +x mariadb_repo_setup

Run the script with the options below to get version 10.6 of MariaDB:

./mariadb_repo_setup --mariadb-server-version=mariadb-10.6 --skip-maxscale

Make sure to inspect any script you download before running it.

Check MariaDB repo at /etc/yum.repos.d/mariadb.repo

Installing Mariadb and Galera

Run the command below on all nodes. This will install MariaDB and Galera alongside some client and backup tools.

dnf install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common

To check the installed packages:

rpm --query --all  --last | grep Maria

Update Firewall rules

Run the commands below on all nodes:

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

Restart firewalld and check if the open ports are listed:

systemctl restart  firewalld.service
firewall-cmd --list-all

Configure Galera

Add the galera config file below to /etc/my.cnf.d/galera.cnf on all nodes and update it as follows:

Make sure you set the same value for the wsrep_cluster_address property on all nodes . It should be updated with the list of IP addresses of all nodes that are part of the cluster.
On every node, update the node name with the property wsrep_node_name and its IP with the property wsrep_node_address.

[mysqld]
# Mandatory settings
wsrep_on                 = ON
wsrep_cluster_name       = "ERS MariaDB Galera Cluster"
wsrep_cluster_address    = gcomm://198.51.100.201,198.51.100.202,198.51.100.203
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2

# another necessary settings:
wsrep_provider          = /usr/lib64/galera-4/libgalera_smm.so
log_bin_trust_function_creators = ON
wsrep_sst_method        = rsync


# other settings
# Galera Node Configuration
wsrep_node_address      = "198.51.100.201"
wsrep_node_name         = "galera01-rhel8"
# Allow server to accept connections on all interfaces.
bind-address            = 0.0.0.0

log_bin_trust_function_creators=ON

character_set_server=utf8
collation_server=utf8_unicode_ci
init_connect='SET NAMES utf8'

Bootstrapping Galera

Select any machine to be the bootstrapping node. To bootstrap a new Galera cluster, simply call the following command:

galera_new_cluster

You can check the bootstrapping process using journalctl:

journalctl  -n 300 -f -u mariadb.service

Joining nodes to the bootstrapped cluster

After bootstrapping the cluster on one of the nodes, start MariaDB on the rest:

systemctl start mariadb.service

You can check the joining process using journalctl:

journalctl  -n 300 -f -u mariadb.service

Steps to start a Galera cluster after a server crush:

Stop all galera services on all database servers systemctl stop mariadb.service

1- Case where safe_to_bootstrap is set to 1 on one of the galera nodes:

On every server node, check in /var/lib/mysql/grastate.dat, which node has the key safe_to_bootstrap set to 1. Restart the server on that node, then restart the rest nodes.

2- Case where safe_to_bootstrap is set to 0 on all nodes:

If you can’t find any node with safe_to_bootstrap set to 1 and all nodes have the key set to 0, check the logs to see the recovery position. Find a line similar to this:

Nov 15 15:06:00 galera01-rhel8.mtg.de sh[1832]: WSREP: Recovered position e4c4071f-7196-11ee-bc6b-a7525fdd43de:560040

Compare the last number between all the nodes (in the above example it is 560040 ) and use the node that has the highest number to bootstrap.
If they are equal, choose any node.
To bootstrap the cluster, update /var/lib/mysql/grastate.dat with safe_to_bootstrap set to 1 on the node which has the highest recovery number, then restart mariadb.service. You can then restart the rest MariaDB services on other nodes.
If none of the above apply, execute the command galera_new_cluster.

Integration with `keycloak`

MariaDB version 10.6 does not support xa-transactions with Galera replication. Therefore, in /etc/opt/keycloak/application.properties, the property should be set to false using:

transactions-xa-enable = false

Default Character Encoding

The default character set for all MTG databases must be set to UTF8:

ALTER DATABASE <db_name> COLLATE = 'utf8_unicode_ci' CHARACTER SET = 'utf8';

The encoding of the MTG databases must always be UTF8 with collation utf8_unicode_ci. Currently, only the utf8mb3 format of MariaDB is supported. In this format, some languages are not supported.

Healthchecks

Taken from Monitoring a Galera Cluster, we can extract following summary:

Running the following query SHOW GLOBAL STATUS LIKE 'wsrep_cluster_%'; will generate an output like this:

+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight       | 3                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 0                                    |
| wsrep_cluster_size         | 3                                    |
| wsrep_cluster_state_uuid   | 8ed435f4-73ff-11ee-8e14-4e6234339974 |
| wsrep_cluster_status       | Primary                              |
+----------------------------+--------------------------------------+
6 rows in set (0.001 sec)

wsrep_cluster_size should be the total number of your nodes in your cluster.
all values should be the same on every node of your cluster.
By running SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment'; you 'll get an output like this:

+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
1 row in set (0.001 sec)

The above shows that the specific node is synced.
Again, all nodes should display the same.

Backup and Restore

You are provided with two scripts, one that will back up your galera cluster and one that will restore the backup taken. Depending on your backup strategy and existence of a shared storage, there’s two alternatives on how you can run the backup script. They are described below in MySQL crontab and Galera Arbitrator.

Prepare your system (common steps)

Create your backup user in mariadb

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT CONNECTION ADMIN, SLAVE MONITOR ON *.* TO 'mariabackup'@'localhost';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';
FLUSH PRIVILEGES;

Configure the required variables in the script wsrep_sst_backup_mtg (see also Backup Use Case in Appendix):
- MYSQL_USER=<as you created it in mariadb in step 1>
- MYSQL_PASSWORD=<as you created it in mariadb in step 1>
- BACKDIR=/mariadb_backup # Create a shared storage between cluster nodes with mysql user read/write access
- FULLBACKUPCYCLE=86400 # Create a new full backup every X seconds i.e. 86400 (1 day), 604800 (7 days)
- KEEP=3 # We’ll keep backups older than FULLBACKUPCYCLE * (KEEP + 1 ) seconds ago
Configure the location of your backups, as defined in variable BACKDIR above. It must be a network attached storage, mounted to all the nodes of your galera cluster and user mysql must be able to read & write on it.
For every node of your galera cluster (that you want to be able to take a backup) copy the script to /usr/bin and make sure it has the proper permissions, by running:

    sudo chmod 755 /usr/bin/wsrep_sst_backup_mtg

Backup script is triggered from mysql user’s crontab

After you have followed the common steps to prepare your system, as described in Prepare your system (common steps), select which of your galera nodes will take backups and proceed in those servers as follows:

Edit the crontab for mysql user with
```
    crontab -u mysql -e
```

and paste inside

    */20 * * * * timeout -k 90 60 /usr/bin/wsrep_sst_backup_mtg

*/20 the script will run every 20 mins starting from :00

timeout -k 90 60 the timeout command will try to stop the script after 60 secs, and it will kill it after 90 secs if it’s still running.

Backup script is triggered from Galera Arbitrator running on another host

Galera arbitrator will be used to trigger the backup from another host, which is outside the galera cluster.

Make sure backup files are stored in a different location than the production servers. The script can be configured to keep a number of old backups and is able to take incremental backups between the full backup cycles, so you can set the frequency of the schedule in cron depending on your needs.

After you follow the common steps to prepare your system, as described in Prepare your system (common steps), proceed as follows:

On a different system outside your galera cluster, install the galera arbitrator package for your distribution that provides /usr/bin/garbd.
- For Ubuntu 22.04 the package is galera-arbitrator-4 and for Redhat it’s the galera-4 package, so for
  - Ubuntu:

    sudo apt install -y galera-arbitrator-4

whereas for RHEL:

    sudo yum in -y galera-4

You should stop and disable the service installed, we are going to run it on demand. In Redhat EL8 the service is called garb. It is disabled by default, however you should confirm.
- In RHEL run:

    sudo systemctl stop garb ; sudo systemctl disable garb

In Ubuntu 22.04 run:

    sudo systemctl stop garbd ; sudo systemctl disable garbd

On the same system copy the Galera Arbitrator configuration file garbd.cnf to /etc and configure it properly:
- in group option place the name of your cluster as it is in galera configuration option wsrep_cluster_name.
- in address option place the ip addresses of cluster nodes as they are in galera configuration option wsrep_cluster_address.

Trigger a backup with the Galera Arbitrator

If you have configured everything properly, from the server that you have installed the galera arbitrator on, run:

garbd -c /etc/garbd.cnf

It should take a couple of seconds to finish and 2 folders will appear under the BACKDIR you have configured in step 2 above. Under the base folder you’ll have your first full backup.

Below you may see the last 6 lines of the output of the command after successful execution:

2023-10-27 12:23:36.607  INFO: RECV thread exiting 0: Success
2023-10-27 12:23:36.607  INFO: recv_thread() joined.
2023-10-27 12:23:36.607  INFO: Closing replication queue.
2023-10-27 12:23:36.607  INFO: Closing slave action queue.
2023-10-27 12:23:36.608  WARN: Attempt to close a closed connection
2023-10-27 12:23:36.608  INFO: Exiting main loop

Automate your backups by using a scheduling tool like cron and adding the following crontab entry for root:

*/20 * * * * timeout -k 90 60 garbd -c /etc/garbd.cnf

Restore script

You should place the restore.sh script inside the BACKDIR folder before running it. Running the script sudo ./restore.sh, the last full backup and the last incremental (if one exists) will be prepared and restored back to /var/lib/mysql, moving the old content in /var/lib/mysql_timedate.

Restoring your galera cluster has some requirements. You should have stopped mariadb service on all nodes except the one you’re running the restore script on. This also means you’ll have to have stopped all ERS services accessing the database (mtg-keycloak, mtg-cara-ws-server, mtg-clm-server, mtg-acme-server).

Finally, to start the cluster again, you should run sudo galera_new_cluster on the node you did run the restore.sh script. Check MariaDB logs and after confirming that it has started properly, you may start mariadb service on all other nodes.

Appendix

Backup Use Case

As you may have seen so far during configuring the backup script, on one hand you have the script’s variables to configure:

FULLBACKUPCYCLE=86400 # Create a new full backup every X seconds i.e. 86400 (1 day), 604800 (7 days)
KEEP=3 # We’ll keep backups older than FULLBACKUPCYCLE * (KEEP + 1 ) seconds ago

and on the other hand there is the schedule frequency (i.e. in cron) of the triggering command.

The script’s variables above define how often the script will create a full backup(FULLBACKUPCYCLE) and how many of them to always keep(KEEP).

The cron schedule interval then, will have to be smaller than the FULLBACKUPCYCLE in order to be sure a full backup will be taken every time the FULLBACKUPCYCLE interval passes. If cron triggers another backup before that interval has passed, then the script will take an incremental backup.

The cron interval will define how many incremental backups you’ll have for every full.

Let’s assume an example case using FULLBACKUPCYCLE=86400 (1 day) and KEEP=14, while cron is configured to trigger a backup every couple of hours (* */2 * * *):

The backup taken for the first time will be a full one.
Next time (after 2 hours) the script will check if there’s been more than a FULLBACKUPCYCLE interval. If not, it will take an incremental backup against our last full (possible over-explanation; that means the differences in our database since our last full backup).
It’s easy to see that cron will take incremental backups during the day until it finds that a day has passed since our last full backup, when it will take another full backup.
Days are passing by, and now we have 14 full backups, many incremental ones corresponding to each full, that day passes too and the script takes the 15th full backup of our schedule. It’s the moment where it calculates that it has a backup that’s older than 15 days (our first full), and it will now delete it.
From then on, the process continues and before finishing, it will delete the older than 15 days backups (that’s the turn of our first incremental ones).