Galera Cluster: Multi-Master Setup and Its Pitfalls

1. What makes Galera Cluster different

Galera Cluster differs fundamentally from classic MySQL replication: instead of a single writer with asynchronously catching-up replicas, Galera allows writes on every node at the same time, secured synchronously through group communication between all members. Every node is a master simultaneously, and a transaction only counts as committed once it has been successfully certified on every node. That is the core of what makes Galera a genuine multi-master system.

For applications, this means a failover of a single node is practically invisible to the rest of the cluster, because the remaining nodes already hold the same data state anyway. At the same time, this model brings limitations that do not exist with classic asynchronous replication: write conflicts between nodes, a quorum mechanism against split brain, and a flow control system that ties the cluster speed to the slowest node. Anyone using Galera Cluster trades replication lag for new, but well understood, complexity.

2. Understanding certification-based replication

The mechanism behind Galera Cluster is called certification-based replication and works fundamentally differently from classic two-phase commit. A transaction runs locally on the node where it started, and is only distributed as a writeset to all nodes in the group at commit time. Every node then independently checks whether this writeset collides with already certified transactions from the same time window, for example because the same row was changed on two nodes at once.

If the writeset does not collide, the transaction is applied on every node and counts as certified. If it does collide, the transaction fails on the node that loses the certification race, with a classic deadlock error that the application must treat like a regular lock conflict and retry. This optimistic approach avoids distributed locking over the network, but shifts the responsibility for conflict handling into the application layer.

3. Cluster setup: bringing up three nodes

A production ready Galera Cluster needs at least three nodes for quorum reasons, since an even number cannot deliver a clear majority result during a network partition. Every node needs the same cluster address in my.cnf, a unique node name, and the replication provider, usually the Galera library libgalera_smm.so.

The first node is started with a special bootstrap command that initializes a new cluster, while every other node joins the already running cluster through the cluster address and automatically requests a full data sync. This sequence must happen in exactly this order, since bootstrapping several nodes at once leads to separate, incompatible clusters.

# my.cnf: Galera Cluster base configuration (identical on all three nodes,
# except wsrep_node_address and wsrep_node_name)
[mysqld]
wsrep_on                  = ON
wsrep_provider             = /usr/lib/galera/libgalera_smm.so
wsrep_cluster_name         = shop_cluster
wsrep_cluster_address      = gcomm://10.0.1.10,10.0.1.11,10.0.1.12
wsrep_node_address         = 10.0.1.10
wsrep_node_name            = node1
wsrep_sst_method           = xtrabackup-v2
wsrep_sst_auth              = sst_user:sst_password
binlog_format               = ROW
default_storage_engine      = InnoDB
innodb_autoinc_lock_mode    = 2

4. Comparing state snapshot transfer methods

When a new node joins a running Galera Cluster, it first needs a full data sync, the state snapshot transfer, or SST for short. Galera supports several SST methods with different trade-offs between speed and the availability of the donor node during the transfer. The method is set through wsrep_sst_method and should be chosen deliberately for production clusters.

In practice, xtrabackup-v2 is the recommended method for production environments, because unlike mysqldump and rsync it does not force a blocking state on the donor node, which can keep serving writes throughout the entire transfer. For very large databases in the terabyte range, plan for the fact that a full SST can take several hours depending on network bandwidth.

5. Detecting and handling write conflicts

Write conflicts in a Galera Cluster are not an error case but expected behavior under parallel writes on multiple nodes. If two transactions on different nodes touch the same row nearly simultaneously, the transaction that gets certified first wins, and the other one receives an error back that behaves like a classic InnoDB deadlock from the application perspective.

Applications writing against a Galera Cluster must therefore implement retry logic for this class of error, exactly as with regular deadlocks. In practice, the conflict rate drops considerably when writes for a given table range are concentrated on a preferred node as much as possible, instead of spreading write load evenly across all nodes, even though that initially seems to contradict the multi-master idea.

-- Typical certification conflict from the application's point of view
UPDATE inventory SET stock = stock - 1 WHERE sku = 'ABC-123';
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction

-- Check wsrep-specific error codes for differentiation
SHOW VARIABLES LIKE 'wsrep_retry_autocommit';
-- Configure automatic retries for autocommit statements
SET GLOBAL wsrep_retry_autocommit = 3;

6. Flow control and cluster performance

Flow control is the mechanism that keeps Galera Cluster from letting fast nodes race so far ahead that a slower node loses touch. Once the queue of unapplied writesets on a node exceeds a configured threshold, that node signals the others to pause new writes until it has caught up. This protects cluster consistency, but with a permanently slow node it can noticeably throttle overall performance for every node.

In practice, a single undersized node, for example with slower disks or a weaker CPU, is the most common cause of poor cluster performance, because flow control slows the entire system down to that node pace. All nodes of a Galera Cluster should therefore get as close to identical hardware resources as possible, instead of combining heterogeneous servers.

-- Check flow control activity and cluster size
SHOW STATUS LIKE 'wsrep_flow_control_paused';
SHOW STATUS LIKE 'wsrep_flow_control_sent';
SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_local_recv_queue';

-- Adjust flow control thresholds
SET GLOBAL wsrep_provider_options = 'gcs.fc_limit=100; gcs.fc_factor=0.5';

7. When Galera fits and when it does not

Galera Cluster fits particularly well for workloads with many short transactions, geographically distributed reads, and a need for nearly invisible failover, for example OLTP systems with high availability requirements. It is also a sensible choice for environments where every node should both read and write without needing an external routing layer like ProxySQL.

Galera is less suited for workloads with very large, long running transactions, high write contention on the same rows from multiple nodes, or applications that depend on non-transactional storage engines like MyISAM, since Galera only works reliably with InnoDB. Batch jobs with huge single transactions also quickly lead to noticeable flow control through synchronous certification and should be split into smaller chunks.

8. Quorum, split brain and network partitions

The quorum mechanism protects a Galera Cluster from split brain situations, where a network partition splits the cluster into two isolated groups that would independently accept conflicting writes. Galera only allows writes on the partition that contains more than half of all original cluster members. The smaller partition automatically switches into a read-only state until the connection is restored.

This is exactly why an odd number of nodes is essential: with two nodes, neither side can reach a majority, so both partitions get locked during a network outage. If the entire cluster fails and needs to be restarted, a manual bootstrap is required, choosing the node with the highest seqno value as the starting point, so no already committed transactions are lost.

# Bootstrap the cluster again after a complete outage
# First, determine the highest seqno value on every node
cat /var/lib/mysql/grastate.dat | grep seqno

# Initialize the cluster on the node with the highest seqno
galera_new_cluster

# Start every other node normally, they join automatically
systemctl start mysql

# Check cluster size after the restart
mysql -e "SHOW STATUS LIKE 'wsrep_cluster_size';"

9. Monitoring the key status variables

A production Galera Cluster should be monitored continuously through its wsrep_ status variables, since classic MySQL metrics alone do not sufficiently reflect the cluster state. Especially important are wsrep_cluster_status, which must show the value Primary, and wsrep_local_state_comment, which describes the synchronization state of an individual node.

A node in the state Donor/Desynced is currently delivering a state snapshot transfer and should not be used for application load during that time, which is why an upstream routing layer like ProxySQL should actively exclude this state from its health check. Alerting on wsrep_cluster_size dropping below the expected node count and on sustained flow control pauses belongs in every production monitoring setup for Galera.

-- Key Galera status variables for monitoring
SHOW STATUS LIKE 'wsrep_cluster_status';    -- expect Primary
SHOW STATUS LIKE 'wsrep_cluster_size';      -- number of active nodes
SHOW STATUS LIKE 'wsrep_local_state_comment'; -- expect Synced
SHOW STATUS LIKE 'wsrep_ready';             -- expect ON
SHOW STATUS LIKE 'wsrep_local_cert_failures'; -- certification conflicts

10. Summary

Galera Cluster replaces asynchronous replication with synchronous certification-based replication and allows genuine multi-master writes. The price is write conflicts that must be handled through retry logic, flow control that ties cluster speed to the slowest node, and a quorum mechanism that requires an odd number of nodes.

Galera works best with short transactions, evenly sized hardware across all nodes, and an application that reacts to certification conflicts with retries. For very large batch transactions or strongly heterogeneous cluster hardware, classic asynchronous replication or a different architecture is often the better choice.

11. FAQ: Galera Cluster Multi-Master Setup