Recover from a PXC crash

Recover from a PXC Crash

Twice last year we had a situation where our whole PXC cluster of 3 nodes just crashed in a domino effect manner. In this case, we had to make sure that we bootstrap the last server in the cluster to go down since it will have the latest data. But how do you know which server went down last?

Today, will i will try to demonsrtate how to recover from difference pxc crash scenaios.

PXC Recovery Scenarios

In this scenario, a single node crashed, but you want to control who the donor should be from the available pxc cluster nodes. If you care to control the donor, you can simply start the node that went down with the following command:

    sudo /etc/init.d/mysql start --wsrep_sst_donor=nodeC

Scenario 2: Form a new cluster alone.

So lets say you have three nodes in your PXC (A,B & C). If nodes A & B crash ( due to data inconsistency, bug, etc. ) or off due to power outage, you need to do manual action to enable primary component on the C node, before you can bring A and B back. You can manually now form a new cluster with C alone without A and B (while they are still down). On C, run the command:

    SET GLOBAL wsrep_provider_options='pc.bootstrap=true';
   

Scenario 3: All nodes went down without proper shutdown procedure

In this scenario, all the nodes in the cluster went down, and you need to bootstrap from the last node to go down as it has the latest data. Last year, we actually experienced this with our entire PXC going down in a domino effect fashion. So we had to figure out which node went down last.

Run the following command on all the crashed nodes
    sudo mysqld_safe --wsrep-recover
   
The results will look something like this
    ...
    140821 15:57:17 mysqld_safe WSREP: Recovered position 4b83bbe6-28bb-11e4-a885-
    4fc539d5eb6a:2
    ...
So the last committed transaction sequence number on this node was 2. Choose the node with the largest committed transaction sequence,
and bootstrap from the that node first and then start the others.
   

REFERENCES

https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/