Deploy a Three-Node MariaDB Galera Cluster for Synchronous Multi-Master Replication

Desired Outcome

A three-node MariaDB Galera Cluster providing synchronous multi-master replication with automatic node provisioning, conflict detection, and high availability for read-write workloads. All three nodes accept writes, replicate changes synchronously, and maintain data consistency through quorum-based consensus.

Prerequisites

Hardware and Network Requirements

• Three servers (physical or virtual) with identical MariaDB version and Galera library
• Low-latency network between nodes (<5ms round-trip, stable)
• Each node: minimum 2 GB RAM, 2 CPU cores, 20 GB disk for data
• Firewall must allow ports: 3306 (client), 4567 (Galera replication), 4568 (IST), 4444 (SST)
• SELinux/AppArmor configured or disabled for inter-node communication

Software Requirements

• MariaDB 10.6+ with Galera 4 (wsrep_provider library)
• Identical OS distribution and kernel version across nodes
• Time synchronization via chrony or NTP on all nodes

Configuration Prerequisites

Each node needs a unique wsrep_node_address (its IP) and wsrep_node_name (hostname). All nodes share the same wsrep_cluster_address listing all three IPs. Binary logging must be enabled with row format.

Procedure

1. Install MariaDB and Galera on All Nodes

On each node, install the MariaDB server package with Galera support. On RHEL/CentOS/Rocky/AlmaLinux:

sudo dnf install -y MariaDB-server MariaDB-backup galera-4

On Ubuntu/Debian:

sudo apt update && sudo apt install -y mariadb-server mariadb-backup galera-4

Verify the Galera library path (typically /usr/lib64/galera-4/libgalera_smm.so on RHEL, /usr/lib/galera-4/libgalera_smm.so on Debian).

2. Configure the First Node (Bootstrap Node)

Edit /etc/my.cnf.d/galera.cnf (or /etc/mysql/mariadb.conf.d/60-galera.cnf on Debian) on node1:

[mysqld]
# Galera provider
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Cluster connection
wsrep_cluster_address=gcomm://10.0.1.10,10.0.1.11,10.0.1.12
wsrep_cluster_name=galera_cluster

# This node
wsrep_node_address=10.0.1.10
wsrep_node_name=galera-node1

# Replication settings
wsrep_sst_method=mariabackup
wsrep_sst_auth=sstuser:StrongPassword123

# Binary logging (required for Galera)
log_bin=binlog
binlog_format=ROW
log_slave_updates=ON

# InnoDB settings
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2

# Performance
wsrep_slave_threads=8
wsrep_certify_nonPK=ON

Create the SST user on node1 before bootstrap:

sudo systemctl start mariadb
mysql -u root -p -e "CREATE USER 'sstuser'@'%' IDENTIFIED BY 'StrongPassword123'; GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%'; FLUSH PRIVILEGES;"

Bootstrap the cluster from node1:

sudo systemctl stop mariadb
sudo galera_new_cluster

This starts MariaDB with --wsrep-new-cluster, initializing the cluster as the primary component.

3. Configure and Start Remaining Nodes

On node2, use the same configuration but change node-specific values:

wsrep_node_address=10.0.1.11
wsrep_node_name=galera-node2

Start MariaDB normally (it will join the existing cluster):

sudo systemctl start mariadb

Repeat for node3 with wsrep_node_address=10.0.1.12 and wsrep_node_name=galera-node3.

4. Verify Cluster Formation

On any node, run:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"

Confirm all nodes report wsrep_ready=ON and wsrep_local_state_comment=Synced:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'wsrep_ready'; SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"

Expected Checks

Cluster Health Indicators

Status Variable	Expected Value	Meaning
wsrep_cluster_size	3	All three nodes participating
wsrep_ready	ON	Node ready to accept queries
wsrep_local_state_comment	Synced	Node synchronized with cluster
wsrep_flow_control_paused	0.000000	No flow control throttling
wsrep_local_recv_queue_avg	< 0.1	Minimal replication lag

Functional Test

Insert a row on node1 and verify immediate replication:

# On node1
mysql -u root -p -e "CREATE DATABASE testdb; CREATE TABLE testdb.t1 (id INT PRIMARY KEY, val VARCHAR(20)); INSERT INTO testdb.t1 VALUES (1, 'from-node1');"

# On node2 and node3
mysql -u root -p -e "SELECT * FROM testdb.t1;"

Result should appear on all nodes within milliseconds.

Failure Simulation

Stop MariaDB on node2:

sudo systemctl stop mariadb

Verify cluster continues with wsrep_cluster_size=2 on remaining nodes. Restart node2 and confirm automatic rejoining and Synced state.

Recovery Options

Graceful Node Restart

A stopped node rejoins automatically on systemctl start mariadb. It uses Incremental State Transfer (IST) if the donor node has the missing writesets in its gcache; otherwise it falls back to State Snapshot Transfer (SST) via mariabackup.

Split-Brain Recovery

If network partition causes split-brain (two partitions each thinking they're primary), identify the most advanced node using wsrep_last_committed:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'wsrep_last_committed';"

On the most advanced node, re-bootstrap the primary component:

mysql -u root -p -e "SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';"

Other nodes will rejoin when connectivity restores.

Full Cluster Recovery from Backup

Use mariabackup with --galera-info to preserve GTID position:

# On donor node
mariabackup --backup --galera-info --target-dir=/backup/galera_full --user=root --password

# Restore on new node
mariabackup --prepare --target-dir=/backup/galera_full
mariabackup --copy-back --target-dir=/backup/galera_full
chown -R mysql:mysql /var/lib/mysql
# Start with --wsrep-new-cluster if first node, otherwise normal start

Schema Change Strategy

Default Total Order Isolation (TOI) locks the entire cluster during DDL. For non-blocking schema changes, use Rolling Schema Upgrade (RSU):

SET SESSION wsrep_OSU_method=RSU;
ALTER TABLE large_table ADD COLUMN new_col INT;
SET SESSION wsrep_OSU_method=TOI;

Apply DDL on each node sequentially. RSU replicates the statement only after local commit, avoiding cluster-wide locks.

Limitations and Operational Notes

• All tables must have explicit primary keys; tables without PKs are not replicated and cause write failures
• Large transactions (>100MB) trigger flow control and memory pressure; split into smaller batches
• Only InnoDB tables replicate; MyISAM, MEMORY, and CSV tables are not replicated and may cause inconsistencies
• Major version upgrades require full cluster shutdown and bootstrap; rolling upgrades only work within same major version with compatible wsrep_provider
• Monitor wsrep_flow_control_paused — sustained values >0.1 indicate network or performance issues

Verification Checklist

1. wsrep_cluster_size = 3 on all nodes
2. wsrep_ready = ON on all nodes
3. wsrep_local_state_comment = Synced on all nodes
4. Write on one node replicates to others within milliseconds
5. Node stop/start cycle results in automatic rejoin and Synced state
6. Flow control paused near zero under normal load