23 Aug 2023

Recovering nodes in a Galera Cluster

In our lab environment, we received some emails from our provider, DigitalOcean saying a few nodes would be going down, because of issues with the physical nodes. Since we run a 9-node Galera Cluster in the lab, across 3 regions (San Francisco, London and Singapore), this posed an interesting problem!


Upon connecting to one of the nodes, we see:

mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 7     |
+--------------------+-------+
1 row in set (0.02 sec)

So we know that 2 of the nodes are down. Presuming you do not have any monitoring setup (might we recommend Galera Manager for this), how would you know which nodes of yours are down?

You could drop down to the console and check out /var/lib/mysql/gvwstate.dat (the gvwstate.dat file has sufficient information to not just tell you the member, but also the segment – we document this in Recovering Primary Component). Here is an example of a gvwstate.dat file:

cat /var/lib/mysql/gvwstate.dat 
my_uuid: 2bf7c931-5c08-11ed-845e-a3d834668673
#vwbeg
view_id: 3 113a4bb9-8010-11ec-bc06-c38d9738c824 127662
bootstrap: 0
member: 113a4bb9-8010-11ec-bc06-c38d9738c824 1
member: 2bf7c931-5c08-11ed-845e-a3d834668673 2
member: 351d0644-af38-11ec-b412-ea0e312fa551 1
member: 5c1de60c-943c-11ed-ab24-32db997c78f0 2
member: 5ef4993d-5c08-11ed-a48c-4eeb3088db59 2
member: d17b21e7-8011-11ec-a1f0-3ae557d8ce7f 1
member: f61e40aa-f54f-11ed-a199-4e236b6a798c 0
#vwend

But maybe, this doesn’t help us figure out which nodes are down, in an easy fashion. Considering this is a 9-node cluster, you could set wsrep_node_name in your my.cnf, or you could just benefit from the hostname information, but where do you find this? Here’s a good feature of Galera 4 in MySQL 8: the mysql.wsrep_cluster_members table. Execute: SELECT * FROM mysql.wsrep_cluster_members and the output is:

mysql> select * from mysql.wsrep_cluster_members;
+--------------------------------------+--------------------------------------+-------------+-----------------------+
| node_uuid                            | cluster_uuid                         | node_name   | node_incoming_address |
+--------------------------------------+--------------------------------------+-------------+-----------------------+
| 113a4bb9-8010-11ec-bc06-c38d9738c824 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-02 | AUTO                  |
| 2bf7c931-5c08-11ed-845e-a3d834668673 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera      | AUTO                  |
| 351d0644-af38-11ec-b412-ea0e312fa551 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-03 | AUTO                  |
| 5c1de60c-943c-11ed-ab24-32db997c78f0 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-03   | AUTO                  |
| 5ef4993d-5c08-11ed-a48c-4eeb3088db59 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-02   | AUTO                  |
| d17b21e7-8011-11ec-a1f0-3ae557d8ce7f | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf    | AUTO                  |
| f61e40aa-f54f-11ed-a199-4e236b6a798c | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon   | AUTO                  |
+--------------------------------------+--------------------------------------+-------------+-----------------------+
7 rows in set (0.00 sec)

Now we have node names! As the hosts came back up, mysqld should have started automatically, but if you notice issues (is SELinux playing up?), you might see:

mysql -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

Now just start it normally, via systemctl start mysqld, and voila, we see the wsrep_cluster_size change:

mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 8     |
+--------------------+-------+
1 row in set (0.00 sec)

It is Galera Cluster, so you know you’re either going to get an Incremental State Transfer (IST) or a full State Snapshot Transfer (SST) if there were more transactions that could be held in the gcache, during the downtime. Thankfully, this downtime wasn’t lengthy and we constantly got ISTs during this.

And it didn’t take long for us to get back to a full, healthy 9-node cluster, spanning across three geographies.

mysql> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 9     |
+--------------------+-------+
1 row in set (0.00 sec)

cat /var/lib/mysql/gvwstate.dat 
my_uuid: 2bf7c931-5c08-11ed-845e-a3d834668673
#vwbeg
view_id: 3 0c8142de-4170-11ee-a367-164b4d777f03 127664
bootstrap: 0
member: 0c8142de-4170-11ee-a367-164b4d777f03 0
member: 113a4bb9-8010-11ec-bc06-c38d9738c824 1
member: 2bf7c931-5c08-11ed-845e-a3d834668673 2
member: 351d0644-af38-11ec-b412-ea0e312fa551 1
member: 5c1de60c-943c-11ed-ab24-32db997c78f0 2
member: 5ef4993d-5c08-11ed-a48c-4eeb3088db59 2
member: 707798f1-4170-11ee-8f3d-86b645e36cfc 0
member: d17b21e7-8011-11ec-a1f0-3ae557d8ce7f 1
member: f61e40aa-f54f-11ed-a199-4e236b6a798c 0
#vwend

mysql> select * from mysql.wsrep_cluster_members;
+--------------------------------------+--------------------------------------+--------------+-----------------------+
| node_uuid                            | cluster_uuid                         | node_name    | node_incoming_address |
+--------------------------------------+--------------------------------------+--------------+-----------------------+
| 0c8142de-4170-11ee-a367-164b4d777f03 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon-02 | AUTO                  |
| 113a4bb9-8010-11ec-bc06-c38d9738c824 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-02  | AUTO                  |
| 2bf7c931-5c08-11ed-845e-a3d834668673 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera       | AUTO                  |
| 351d0644-af38-11ec-b412-ea0e312fa551 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf-03  | AUTO                  |
| 5c1de60c-943c-11ed-ab24-32db997c78f0 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-03    | AUTO                  |
| 5ef4993d-5c08-11ed-a48c-4eeb3088db59 | d04a1319-d89d-11eb-86c2-3fe756007e90 | galera-02    | AUTO                  |
| 707798f1-4170-11ee-8f3d-86b645e36cfc | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon-03 | AUTO                  |
| d17b21e7-8011-11ec-a1f0-3ae557d8ce7f | d04a1319-d89d-11eb-86c2-3fe756007e90 | galerasf     | AUTO                  |
| f61e40aa-f54f-11ed-a199-4e236b6a798c | d04a1319-d89d-11eb-86c2-3fe756007e90 | galeralon    | AUTO                  |
+--------------------------------------+--------------------------------------+--------------+-----------------------+
9 rows in set (0.00 sec)

So the best tips here are:

  1. use wsrep_node_name (and if you don’t, you’ll benefit from hostname
  2. look at gvwstate.dat to grab information of members and also the segments they are in
  3. in a geo-replicated cluster, always setup segments (this is useful for network traffic too)
  4. make sure you’re using the latest version of MySQL, and Galera, so you can benefit from the new features, and in this case, we used one of the 3 new tables in mysql
  5. enjoy using mysql.wsrep_cluster_members to help with debugging
  6. use something for monitoring and deployment, something like Galera Manager