MariaDB Galera Cluster Configuration Guide

Introduction

This page 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

# To avoid issues with sequences disable auto_increment control of Galera
# and set the auto_increment_increment to the number of galera nodes
# and auto_increment_offset to the node number
wsrep_auto_increment_control = OFF
auto_increment_increment = 3        # configure this to the number of nodes in the cluster. It can be set to a greater number than the current number of nodes to allow for future scaling.
auto_increment_offset = 1           # 1 for node 1, 2 for node 2, 3 for node 3

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.