Troubleshooting a ClusterControl MySQL PXC cluster

We’ve been testing ClusterControl with three Percona XtraDB Cluster nodes on a virtualized environment and found it to be pretty resilient to faults.  Even after performing a brutal shutdown of all VMs (which is equivalent to pulling the plug on a physical machine) or wiping out the MySQL database on one of the nodes, the cluster returns to its normal state when the VMs are powered back up.  Still, sometimes things can go wrong, and it’s good to know what to do in this case.

First of all, always check the status of the jobs in Logs -> Jobs.  Remember to refresh both the job list window and the job messages window.  Check also the nodes’ logs in Nodes -> Logs.  From shell, peruse /var/lib/mysql/error.log and /var/log/mysqld.log; usually you’ll find the problem there.

ClusterControl will run a cluster recovery job if the cluster is in Degraded or Failure state, and try to repeat this job periodically.  As long as the job is marked as RUNNING in the job list, it’s working.  You should also see in the job messages window the message « GALERA_NODE_MYSQL_DISCONNECTED, expected state GALERA_NODE_SYNCED – waiting! ».  Don’t assume that the system is hung; be patient and wait that the job finishes.

A joiner node might take some time before it gets synced via SST.  During this whole time the node will be marked orange in the UI (note that the donor will be marked orange, too).  A 1-Gb database can take half an hour to be fully synced to one node.

However, if the job ends as FAILED the cluster was definitely unable to automatically do a cluster recovery.  You will also see messages such as « Cannot determine most advanced node », « Unable to join the cluster », and « Node will never reach the expected state » in the job logs.  In this case it’s time to brush up the Linux shell skills. Note: Before operating on the nodes’ MySQL daemons via shell, prevent ClusterControl from interfering by setting Auto Recovery: Cluster / Node to OFF.

Starting a node

You can start a node with the command

or, if this is the first node of the cluster,

The latter command defines the node as the one with the correct copy of the database (to which the other nodes should sync via SST) and boots up the cluster.

If MySQL refuses to start and complains about a lock file or a PID file, it’s a telltale sign that the previous MySQL process did not exit normally.  Get rid of these files:

and try to start MySQL again.

Syncing a node

When a node is out of sync it will try a IST towards a donor node to resynchronize itself with the cluster.  If this is not possible, it will try a SST.  If even this fails, you may force a SST by wiping out the contents of the MySQL data directory (we recommend a rename instead of a delete, so you’ll have a copy in case something goes wrong).

First, stop the joiner node.  Try first a graceful stop, then a merciless killing:

Ensure that the MySQL daemon is not running anymore:

Then empty the MySQL data directory:

Finally, restart the node:

If everything goes well you should see a message « Starting MySQL (Percona XtraDB Cluster)… State transfer in progress, setting sleep higher… » followed by a long series of dots.  Be patient and wait that the state transfer finishes.

Checking the status of a node

Sometimes the ClusterControl interface may not show the latest status of a node; the node parameters may be wrong, or the node could be shown in orange while everything is working fine.  If this happens, you can fix this glitch by restarting the cmon process in the controller, and logging out and in again in the web interface.

However, remember that you can also know a node’s replication status directly by peeking into its database:

Here you can see that the node is correctly in the cluster and synced with the others.

This is the full list of WSREP status variables.  For reference, you might also want to have a look at the list of WSREP system variables which specify how the node is configured for replication.