MariaDB Galera Cluster: Synchronous Multi-Master Replication Configuration and Operational Limits

The Problem: Synchronous Multi-Master Without Split-Brain

Running multiple writable database nodes that stay consistent is difficult. Traditional asynchronous replication risks data loss on failover; two-phase commit kills throughput. MariaDB Galera Cluster uses certification-based replication: transactions execute optimistically on one node, then a deterministic certification step runs on all nodes before commit. If certification passes, every node applies the write-set in the same order. The result is synchronous multi-master with no single point of failure—provided you respect the constraints.

Minimal Three-Node Configuration

Create /etc/my.cnf.d/galera.cnf on each node (root or sudo required). Replace IP addresses and node names per host.

[mysqld]
# Galera provider library (path varies by package)
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so

# Cluster connection string — list all seed nodes
wsrep_cluster_address=gcomm://192.168.1.10,192.168.1.11,192.168.1.12

# Logical cluster name (must match on all nodes)
wsrep_cluster_name=my_cluster

# Unique node identifier
wsrep_node_name=node1

# This node's IP for Galera traffic
wsrep_node_address=192.168.1.10

# Required for certification-based replication
binlog_format=ROW
innodb_autoinc_lock_mode=2

# Optional: tune flow control for high-latency links
# wsrep_provider_options="evs.send_window=128; evs.user_send_window=64"

Start the first node with galera_new_cluster (systemd: systemctl start mariadb --wsrep-new-cluster). Join the remaining nodes with normal systemctl start mariadb. Each node must run MariaDB 10.6+ with the galera-4 package installed.

How Certification Detects Conflicts

When a transaction commits locally, Galera builds a write-set (rows changed, primary key values). That write-set is broadcast to the cluster. Every node runs a deterministic certification test: does any primary key in the write-set conflict with an uncommitted local transaction or a previously certified write-set? If yes, the later transaction is aborted with ERROR 1213 (ER_LOCK_DEADLOCK). The application must retry.

Example conflict test (run on two nodes simultaneously):

-- Node A
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (42, 100);
-- pause before commit

-- Node B
START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (42, 200);
COMMIT;  -- succeeds

-- Node A
COMMIT;  -- returns ERROR 1213 (ER_LOCK_DEADLOCK)

This behavior is mandatory: all replicated tables must have a primary key. Without it, certification cannot uniquely identify rows and the cluster will refuse to replicate the table.

DDL and Schema Changes

By default, ALTER TABLE uses Total Order Isolation (TOI): the DDL statement is replicated and executed on all nodes in the same order as transactions, blocking writes cluster-wide until complete. For large tables this can stall the cluster for minutes or hours.

Alternative: Rolling Schema Upgrade (RSU) runs the DDL locally on each node without replication. Use only for backward-compatible changes (adding nullable columns, indexes). Set per-session:

SET wsrep_OSU_method=RSU;
ALTER TABLE large_table ADD COLUMN notes TEXT;
SET wsrep_OSU_method=TOI;  -- revert

Risk: RSU on incompatible changes (dropping columns, changing types) causes data divergence. Test on a staging cluster first.

Flow Control and Large Transactions

Galera throttles writes when the receive queue exceeds a threshold. Monitor with:

SHOW STATUS LIKE 'wsrep_flow_control_paused';

A sustained value above 0.1 (10% of time paused) means write throughput exceeds replication capacity. Causes include: large transactions (bulk inserts, huge deletes), high network latency, or undersized wsrep_provider_options send windows. Mitigate by splitting large transactions, increasing evs.send_window (default 128), or adding a dedicated replica node for read-heavy workloads.

Common Mistakes to Avoid

• Missing primary keys: Galera will not replicate tables without a PK. Add PRIMARY KEY (id) before enabling replication.
• Auto-increment collisions: Keep wsrep_auto_increment_control=ON (default). It adjusts auto_increment_increment and auto_increment_offset per node based on cluster size. Disabling it causes duplicate key errors.
• High-latency links without tuning: Cross-datacenter clusters need larger evs.send_window and evs.user_send_window. Start with 256/128 and monitor wsrep_flow_control_paused.
• Using MyISAM or non-transactional tables: They are not replicated. Convert to InnoDB.

Quorum and Network Partitions

Galera requires a majority of nodes to form a primary component. With three nodes, losing two causes the remaining node to become non-primary (read-only). Prevent split-brain by:

• Running an odd number of nodes (3, 5, 7).
• Or adding a Galera Arbitrator (garbd) on a third site for quorum without data storage.
• Tuning wsrep_provider_options: evs.suspect_timeout=PT5S, evs.inactive_timeout=PT15S to detect partitions faster.

Backup Strategy

Running mariabackup on a donor node triggers State Snapshot Transfer (SST), making the donor unavailable to serve writes during the transfer. For production clusters:

• Use a dedicated replica node (non-Galera, async replica) for backups.
• Or take incremental backups with --incremental-basedir to reduce SST frequency.
• If SST is required, prefer wsrep_sst_method=mariabackup (parallel, compressed) over rsync.

Verification Checklist

After startup, run on each node:

SHOW STATUS LIKE 'wsrep_%';

Key values to confirm:

Variable	Expected
wsrep_cluster_size	3 (or total node count)
wsrep_ready	ON
wsrep_local_state_comment	Synced
wsrep_cluster_status	Primary

If any node shows wsrep_local_state_comment=Joiner or Donor/Desynced, check the error log for SST failures or network issues.

Limitations Summary

• All tables must be InnoDB with explicit primary keys.
• Write throughput limited by slowest node and network round-trip latency.
• Large transactions cause flow control pauses; split them.
• DDL blocks cluster in TOI mode; use RSU only for compatible changes.
• No support for foreign keys with ON DELETE CASCADE across clusters (certification cannot guarantee order).

Practical Next Step

Deploy a three-node test cluster using the configuration above. Run the conflict test to verify ERROR 1213 behavior. Monitor wsrep_flow_control_paused under your actual write load. Only then plan production topology, backup nodes, and garbd placement.