Tech Notes:- How To Bootsrap a Percona Xtradb Cluster After All The Nodes Are Down

Often there is confusion regarding which node to start first after we shut down all the nodes or a Percona XtraCB Cluster. So here is how we can decide that

Safe_to_bootstrap Flag

Our decision should be based on the value of this flag, if we are sure that the cluster was shut down properly. If we are talking about a 3 nodes cluster, you have to check the value of this flag in the file /var/lib/mysql/grastate.dat in each of the nodes.

The node with the value 1 should be the one we use for bootstrap. Others will have a value of 0. So here in my case, Node1 with the value of 1 for safe_to_bootstrap should be the one which we bootstrap.

But what happens if you try to  bootstrap from the nodes 2 and 3 which had a value of 0 for safe_to_bootstrap. The cluster will refuse to start those nodes. You can see the below message in the logs /var/log/mysqld.log

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

What if the nodes were not shut down properly.?

In case of a hard crash or an abrupt shutdown, all nodes will have a value of 0 for the safe_to_bootstrap. In this case, we have to check the DB engine to determine which node has committed the last transaction. For this, we have to start mysqld in each node with the wsrep-recover variable  

sudo mysqld_safe --wsrep-recover

The above command will have something like below in the output

 mysqld_safe WSREP: Recovered position  f23bb233-5840-11e9-919a-9eac3900b870:14 

The number after the UUID string is the one to look for. Select  the node that has the highest number and update its /var/lib/mysql/grastate.dat to set  safe_to_bootstrap: 1

sudo less /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    f23bb233-5840-11e9-919a-9eac3900b870

seqno:   -1

safe_to_bootstrap: 1

Now it is safe to bootstrap this node.

Note:-The above procedure is not needed for most recent versions of Percona XtraDB cluster as there is a new option pc.recovery which is enabled by default and will recover the cluster promoting the node with the last commit as the primary node after all the members again start to see each other.