Infrastructure Overview

In this deployement, we utilize three Linux instances to form a resilient multi-master MariaDB cluster. This setup ensures data consistency and high availability across all nodes.

Software Installation

Install the MariaDB server along with the Galera extension on all nodes. Using the package manager ensures all necessary dependencies for synchronization are met.

dnf install -y mariadb-server-galera

Cluster Configuration

Modify the MariaDB configuration file (typically /etc/my.cnf or a dedicated file in /etc/my.cnf.d/) on each node. The parameters must be adjusted to identify the cluster members and the local node's identity.

[mysqld]
# Basic Galera requirements
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Cluster Identification
wsrep_cluster_name="production_galera_cluster"
wsrep_cluster_address="gcomm://10.32.161.131,10.32.161.132,10.32.161.133"

# Node-specific Identification (Update these for each node)
wsrep_node_name="galera-01"
wsrep_node_address="10.32.161.131"

# State Transfer Method
wsrep_sst_method=rsync

Cluster Initialization

To start the cluster, the first node must initialize a new cluster instance. Subsequent nodes will then join this existing cluster.

1. On the primary node (galera-01):

galera_new_cluster
systemctl enable mariadb

2. On the remaining nodes (galera-02 and galera-03):

systemctl enable --now mariadb

Verifying Replication Status

Once all services are running, verify that the nodes have successfully joined the cluster. The wsrep_cluster_size should reflect the total number of nodes, and the wsrep_local_state_comment should inidcate "Synced".

# Execute in MariaDB shell
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_local_state_comment';

Expected output for a healthy cluster:

+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_cluster_size        | 3      |
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

Recovering from a Full Cluster Shutdown

If all nodes in the cluster are powered down, the cluster loses its primary component state. To recover, you must identify the node with the most recent data and set it as the bootstrap node.

Check the grastate.dat file located in the MariaDB data directory (usually /var/lib/mysql/):

# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    15e45d57-d9d0-11ed-a6b7-93a39cf0c63a
seqno:   -1
safe_to_bootstrap: 0

If no node has safe_to_bootstrap: 1, manually update the file on the node with the highest seqno (or the last one to shut down) by setting safe_to_bootstrap: 1. Then, run the bootstrap command:

# Manual edit of grastate.dat
sed -i 's/safe_to_bootstrap: 0/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat

# Re-initialize the cluster
galera_new_cluster

# Start other nodes normally
systemctl start mariadb