Galera Cluster Limitations and Recovery

This guide covers the most common limitations, startup specifics, crash-recovery procedures, single-node recovery steps, and monitoring checks for a MariaDB Galera Cluster hosted on the platform.

Galera Cluster Limitations

1. Tables Should Have a Primary Key

All tables should contain a primary key. Multi-column primary keys are supported. DELETE operations are not supported on tables without a primary key, and row order may differ between nodes when a table has no primary key.

Use the following query to find base tables without a primary key:

select tab.table_schema as database_name,
tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
on tab.table_schema = tco.table_schema
and tab.table_name = tco.table_name
and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
and tab.table_schema not in ('mysql', 'information_schema',
'sys', 'performance_schema')
and tab.table_type = 'BASE TABLE'
order by tab.table_schema,
tab.table_name;

2. MyISAM Tables

Galera replication works with the InnoDB storage engine. Writes to tables using other storage engines, including many system tables, are not replicated. The source documentation notes experimental MyISAM support through the wsrep_replicate_myisam system variable.

Stop, Start, and Restart Specifics

A clean Galera Cluster shutdown should stop nodes sequentially. The last container becomes the bootstrap node for the next cluster start. The platform automates this process, so the cluster can normally be started, stopped, or restarted from the dashboard like a regular environment.

Restarting only one cluster node follows the standard node-restart procedure.

Node with Maximum Transactions

Recovery should bootstrap from the node with the highest sequence number of the last transaction. Check the seqno value in /var/lib/mysql/grastate.dat on each node:

cat /var/lib/mysql/grastate.dat | grep seqno

If one node has the highest value, use that node. If several nodes share the same highest value, any of them can be selected; the source recommends preferring the primary container of the layer.

If any node has seqno: -1, the nodes cannot be assumed consistent. In that case, recover the transaction position on each node with:

mysqld --wsrep-recover

Find the Recovered position in the output and compare the numeric value at the end of the line across all nodes. Bootstrap from the node with the highest recovered position.

After selecting the correct node, set safe_to_bootstrap to 1 in its grastate.dat file and use that node to bootstrap the cluster.

Starting the Cluster after a Crash

1. Ensure no MySQL process is running on any node. After a crash, a process can appear to be running while not responding correctly; such processes must be killed manually.
2. Restart all MySQL containers after the hanging processes are terminated.
3. Check safe_to_bootstrap in /var/lib/mysql/grastate.dat. It should initially be 0.
4. On the node with the highest transaction position, change safe_to_bootstrap to 1 and start MySQL.
5. Start MySQL sequentially on the remaining nodes.

Check the current bootstrap value:

grep safe_to_bootstrap /var/lib/mysql/grastate.dat

Set the selected node for bootstrap and start MySQL:

sed -i 's/safe_to_bootstrap: 0/safe_to_bootstrap: 1/g' /var/lib/mysql/grastate.dat
grep safe_to_bootstrap /var/lib/mysql/grastate.dat
service mysql start

Then start MySQL on each remaining node:

service mysql start

Single Node Failure

A common reason for a node crash is an unsupported or ignored Galera limitation. Check /var/log/mysql/mysqld.log for the relevant error.

To restore a single failed node:

• Ensure no MySQL processes are running on the failed node.
• Set safe_to_bootstrap to 0 in /var/lib/mysql/grastate.dat.
• Restart the node using the init script.

/etc/init.d/mysql restart

If the original crash was caused by an unresolved Galera limitation, the same error can occur again later.

Monitoring Galera Cluster

Use SHOW GLOBAL STATUS LIKE on any database node to check key Galera status parameters.

mysql -uuser -ppass -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"

mysql -uuser -ppass -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';"

mysql -uuser -ppass -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"

If the cluster includes ProxySQL nodes, run the following command on any ProxySQL node to check backend database status:

mysql -uadmin -padmin -P6032 -h127.0.0.1 -e "select * from runtime_mysql_servers;"

All backend database nodes should be shown with ONLINE status.

Expected Result

A failed or crashed Galera Cluster is recovered by identifying the most up-to-date node, bootstrapping from that node, then starting the remaining nodes sequentially. Monitoring confirms that the cluster is in a Primary state, nodes are Synced, and ProxySQL sees all backends as ONLINE.

Important Notes

• Back up /var/lib/mysql on every node before manual recovery.
• Bootstrap from the node with the highest valid transaction position.
• Use mysqld --wsrep-recover when seqno values are unreliable.
• Set safe_to_bootstrap=1 only on the selected bootstrap node.
• Start the remaining nodes sequentially after the bootstrap node starts successfully.
• Check mysqld.log when a node fails to rejoin.