How to start a Galera MySQL/MariaDB cluster (Bootstrap)

The way to start a Galera cluster for MySQL or MariaDB is different from starting a standard MySQL instance or a MySQL cluster. Galera requires a bootstrapped node in the cluster as a reference point before the remaining nodes can join and become part of the cluster. This process is known as Bootstrap. Bootstrapping is the initial step of introducing a database node as the primary component before others see it as a reference point for synchronizing data.

How does it work?

When Galera starts with the bootstrap command on a node, that particular node will reach primary status (check the value of wsrep_cluster_status). The remaining nodes will only require a normal start command and will automatically look for the existing primary component (PC) in the cluster and join to form a cluster. Data synchronization then occurs through an incremental state transfer (IST) or a snapshot state transfer (SST) between the joining node and the donor.

Basically, you should only bootstrap the cluster if you want to start a new cluster or when no other node in the cluster is in PRIMARY status. You must be careful when choosing which action to take, or you could end up with split clusters or data loss.

How do you start Galera Cluster?

Different Galera clusters use different bootstrap commands (based on the latest software version). On the first node, run:

# galera_new_cluster

# service mysql bootstrap

# service mysql start --wsrep-new-cluster

# mysqld_safe --wsrep-new-cluster

# galera_new_cluster

# service mysql bootstrap

# service mysql start --wsrep-new-cluster

# mysqld_safe --wsrep-new-cluster

# systemctl start mysql@bootstrap.service

# service mysql bootstrap-pxc

The command above is just a wrapper, and what it actually does is start the MySQL instance on that node with gcomm:// as the wsrep_cluster_address variable. You can also manually define the variables inside my.cnf and run the standard start/restart command. However, do not forget to change wsrep_cluster_address back so that it contains the addresses of all nodes after startup.

Once a primary node is active, simply run the following command on subsequent nodes:

# systemctl start mysql

# service mysql start

The new node connects to the cluster members as defined by the wsrep_cluster_address parameter . It will now automatically retrieve the cluster map and connect to the remaining nodes, forming a cluster.

Warning: never bootstrap when you want to reconnect a node to an existing cluster, and NEVER run bootstrap on more than one node at a time. 

Safe bootstrap flag

Starting with version 3.19, Galera comes with a new flag called "safe_to_bootstrap" inside grastate.dat. This flag makes the decision easier and prevents unsafe choices by tracking the order in which nodes shut down. The last node to shut down will be marked as "Safe-to-Bootstrap". All other nodes will be marked as unsafe to bootstrap.

If you look at the contents of grastate.dat (the default is MySQL datadir): 

# cat /var/lib/mysql/grastate.dat

You should notice the flag on the last line (seqno):

# GALERA saved state
version: 2.1
uuid:    8bcf4a34-aedb-14e5-bcc3-d3e36277729f
seqno:   2575
safe_to_bootstrap: 0

When starting the new cluster, Galera will refuse to start the first node that was marked as unsafe to bootstrap. You will see the following message in the logs:

“It may not be safe to bootstrap the cluster from this node. It was not the last to leave the cluster and may not contain all updates. 

To force the cluster to bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1”. 

In the event of an unclean shutdown or a complete crash, all nodes will have "safe_to_bootstrap: 0", so you will need to query the InnoDB storage engine to determine which node committed the last transaction in the cluster. This can be done by starting mysqld with the “–wsrep-recover” variable on each of the nodes:

# mysqld --user=root --wsrep_recover

This produces output like this:

...
2022-10-06 05:43:22 36311 [Note] InnoDB: Database was not shutdown normally!
2022-10-06 05:43:22 36311 [Note] InnoDB: Starting crash recovery.
...
2022-10-06 05:43:23 36311 [Note] WSREP: Recovered position: 8bcf4a34-aedb-14e5-bcc3-d3e36277729f:114428
...

The number after the UUID string on the "Recovered position" line is the one to look for. Choose the node with the highest number and edit its grastate.dat to set "safe_to_bootstrap: 1", as shown in the following example:

# GALERA saved state
version: 2.1
uuid:    8bcf4a34-aedb-14e5-bcc3-d3e36277729f
seqno:   -1
safe_to_bootstrap: 1

You can then run the standard bootstrap command on the chosen node.

What if the nodes have diverged?

Under certain circumstances, the nodes may have diverged from one another. The status of all nodes may become Non-primary due to a network partition between the nodes, a cluster crash, or if Galera encounters an exception when determining the Primary Component. You will then need to select a node and promote it to a primary component.

To determine which node should be bootstrapped, compare the value of wsrep_last_committed on all database nodes: 

node1> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed | 10032       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

node2> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed | 10348       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

node3> SHOW STATUS LIKE 'wsrep_%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| wsrep_last_committed |   997       |
...
| wsrep_cluster_status | non-Primary |
+----------------------+-------------+

From the results above, node 2 has the most up-to-date data. In this case, all Galera nodes are already started, so you do not need to bootstrap the cluster again. You only need to promote node 2 to a primary component:

node2> SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

The remaining nodes will reconnect to the primary component (node 2) and resynchronize their data based on this node.

Sources MariaDB Galera Cluster Documentation, Severalnines and Percona Product Documentation. 

Remember, if you have any questions about this or any other issue related to your servers on Clouding, feel free to write to soporte@clouding.io We are here for whatever you need!