MySQL Galera Cluster: Setup, Tuning, and Common Pitfalls

Running MySQL in a single-master setup means every write path goes through one machine — when that machine goes down, so does your application. MySQL Galera Cluster changes the equation by providing synchronous multi-master replication where every node can accept writes and every write is committed across all nodes before the client receives an acknowledgement. That guarantee sounds expensive, and it is, but for workloads where data loss is not an option it is the right trade-off. This post walks through a full production setup, the tuning knobs that matter most, and the failure modes that catch teams off guard.

What is Galera Cluster?

Galera is a synchronous multi-master replication plugin for MySQL and MariaDB developed by Codership. It exposes the WSRep API (Write-Set Replication API) — a standardised interface between the database engine and the replication provider. When a transaction commits on any node, Galera packages the row-level changes into a write set, broadcasts that write set to every other node, and runs a deterministic certification algorithm to detect conflicts before the commit is acknowledged. If no conflicts exist, all nodes apply the write set and commit in lockstep. If a conflict is detected, the originating node rolls back its local transaction and returns a deadlock error to the application.

Three properties define the model: synchronous replication (no replica lag), true multi-master (any node accepts writes), and automatic membership control (nodes leaving or joining do not require manual reconfiguration). The trade-off is write latency — each commit incurs at least one network round-trip across all nodes, so Galera is unsuitable for write-heavy workloads that require sub-millisecond commit times.

Architecture Overview

A minimal production cluster has three nodes. The cluster uses a group communication system (GCS) — Galera ships with its own called gcomm — to maintain membership and order write sets. Each node maintains a GCache (the Galera cache, a ring buffer on disk) that holds recent write sets. When a joining node is slightly behind it can catch up via Incremental State Transfer (IST), pulling only the missing write sets from the GCache of a donor node. If the joining node is too far behind — or is brand new — it requires a State Snapshot Transfer (SST), which is a full copy of the data.

Setting Up a Three-Node Cluster

Prerequisites

• Three Linux nodes (bare metal or VM) with the same MySQL/MariaDB major version installed
• Galera library installed: galera-4 (MySQL 8) or galera-3 (MySQL 5.7 / MariaDB 10.x)
• Firewall rules open: port 3306 (MySQL), 4567 (Galera replication), 4568 (IST), 4444 (SST)
• xtrabackup or mariabackup installed on all nodes for SST

Core Configuration (my.cnf)

Add the following to /etc/mysql/mysql.conf.d/galera.cnf on every node, adjusting the addresses for each:

[mysqld]
# Galera provider
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Cluster topology
wsrep_cluster_name="prod_galera"
wsrep_cluster_address="gcomm://10.0.1.11,10.0.1.12,10.0.1.13"

# Node identity
wsrep_node_name="db-node-1"          # unique per node
wsrep_node_address="10.0.1.11"       # this node's IP

# SST method
wsrep_sst_method=mariabackup         # or xtrabackup-v2 for MySQL 8

# InnoDB settings required by Galera
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2           # required for Galera
binlog_format=ROW                    # required

# Optional but recommended
wsrep_slave_threads=4                # parallel apply threads
innodb_flush_log_at_trx_commit=0    # relax durability slightly for speed

Bootstrapping the First Node

The first node must be started with a special flag to initialise the cluster. Never run this command on a node that is joining an existing cluster — it will create a new cluster of one, causing split-brain.

# On node 1 ONLY — one time only at cluster creation
sudo mysqld_bootstrap
# or via systemd with the Galera-aware unit:
sudo galera_new_cluster

Confirm the node is the sole member:

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

Joining Subsequent Nodes

Start MySQL normally on nodes 2 and 3. Galera detects the cluster address in the config, connects via gcomm, and automatically initiates SST or IST:

sudo systemctl start mysql

Watch /var/log/mysql/error.log for SST progress. Once joined, verify on any node:

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

mysql> SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+

Key Tuning Parameters

GCache Size

GCache is the single most important tuning target for IST success. If a node goes offline and comes back before GCache wraps around, it will use IST (fast, non-blocking). If GCache has overwritten the needed write sets, SST is required (slow, donor is briefly read-only). Size GCache based on your write rate and expected node downtime:

wsrep_provider_options="gcache.size=2G; gcache.page_size=512M"

A rule of thumb: GCache size = write throughput (bytes/sec) × maximum acceptable downtime (seconds) × 1.5 safety factor. For a cluster doing 50 MB/s of writes and a 30-minute maintenance window, that is roughly 135 GB — plan accordingly.

Flow Control Threshold

Galera pauses replication (flow control) when a node's receive queue exceeds a threshold:

wsrep_provider_options="gcs.fc_limit=100; gcs.fc_factor=0.5"

gcs.fc_limit is the receive queue length at which flow control fires. Raising it allows a slower node more headroom but risks it falling further behind. Do not tune this until you have profiled the actual lag cause — it is often a missing index or a large transaction, not a capacity issue.

Parallel Apply Threads

wsrep_slave_threads=8    # match to CPU count; start at 4

Common Pitfalls

Split-Brain Scenarios

A split-brain occurs when the cluster loses quorum — more than half of the nodes are unreachable from the remaining nodes — and two sub-clusters continue accepting writes independently. When the partition heals, there is no safe automatic merge; you have divergent data. Galera prevents this by halting writes on any partition that does not hold a majority of wsrep_cluster_size.

# Run the Galera Arbitrator daemon on a lightweight node
garbd --group=prod_galera \
      --address="gcomm://10.0.1.11,10.0.1.12,10.0.1.13" \
      --log=/var/log/garbd.log \
      --daemon

Large Transaction Certification Conflicts

Galera certifies write sets at commit time. If two nodes modify the same row during the same time window — even without an explicit conflict at the SQL level — one transaction will be rolled back with ER_LOCK_DEADLOCK. Large transactions that touch many rows dramatically increase the window of conflict. Common culprits: batch deletes, mass updates, and ALTER TABLE on large tables.

Decompose large batches:

-- Instead of one giant delete:
-- DELETE FROM events WHERE created_at < '2024-01-01';

-- Use a loop in small batches:
DELETE FROM events WHERE created_at < '2024-01-01' LIMIT 1000;
-- repeat until ROW_COUNT() = 0

SST Donor Blocking

When a node requires SST, the donor node is briefly set to read-only while the snapshot is taken (with xtrabackup or mariabackup this window is very short — under a second — because backup tools use InnoDB's non-blocking backup lock). Avoid SST during peak traffic if possible, and always prefer IST by keeping GCache appropriately sized.

Monitoring wsrep Status

All Galera health metrics are available via SHOW STATUS LIKE 'wsrep_%'. The critical ones to alert on:

SHOW STATUS LIKE 'wsrep_%';

Export these via mysqld_exporter with the info_schema.wsrep_status collector and graph them in Grafana. Alert when wsrep_cluster_size drops below expected or wsrep_cluster_status != Primary.

# Prometheus mysqld_exporter snippet
--collect.info_schema.innodb_metrics \
--collect.global_status \
--collect.global_variables

ProxySQL Integration

ProxySQL is the standard connection proxy for Galera clusters. It monitors wsrep_local_state_comment on each backend and automatically removes nodes that are not Synced from the active pool. Configure a hostgroup for writers (any single node) and a hostgroup for readers (all synced nodes):

-- In ProxySQL admin (port 6032):
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
  (10, '10.0.1.11', 3306, 1),  -- writer hostgroup
  (20, '10.0.1.11', 3306, 1),  -- reader hostgroup
  (20, '10.0.1.12', 3306, 1),
  (20, '10.0.1.13', 3306, 1);

-- Galera health check user
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('app_user', 'secret', 10, 1);

-- Enable Galera-aware monitoring
UPDATE global_variables
SET variable_value='true'
WHERE variable_name='mysql-monitor_galera_healthcheck_interval';

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;