1

I have a 5 node cluster. When I run backups it locks the entire cluster. I use DDL statements in a stored procedure that is used heavily. What I am trying to achieve is taking one node out of the cluster. Running the backup then adding the node back to the cluster.
How do I remove a node from a cluster?

 set wsrep_cluster_address = gcomm://192.168.2.59,192.168.2.60,192.168.2.61,192.168.2.62

Leaving the the node I want to backup out of the update. On the node I want to backup do I set wsrep_cluster_address:

 set wsrep_cluster_address = gcomm://

Do I change the cluster name on the node as well?

1 Answer 1

1

I wouldn't remove the node, it's sufficient to desync it from the cluster:

SET GLOBAL wsrep_desync=ON;

Then do the backup. Then:

SET GLOBAL wsrep_desync=OFF;

When a node is desynced, it will remain part of the cluster. The node will receive transactions, but not process them. Flow control is disabled, which allows the node to fall behind the cluster. Once we turn off desync again, it will process the queued up transactions.

During desync, the status variable wsrep_cluster_size doesn't change, and the node itself will have wsrep_connected=ON, wsrep_ready=ON as before. However, it will also have wsrep_local_state_comment='Donor/Desynced'.

For your DDL statements, I think your options are:

  • pt-online-schema-change
  • wsrep_OSU_method = RSU
  • wsrep_OSU_method = NBO
5
  • When dsync is on does the other nodes see the dsync'd node? Or will they see it but just ignore it. Mar 16, 2022 at 18:34
  • @newdeveloper They will see it, but it doesn't process transactions. I've updated my answer to explain a little better.
    – dbdemon
    Mar 16, 2022 at 21:54
  • I will give this a try. Changing the wsrep_osu_method wouldn't work. Stored procedure is heavily used. I could try setting the wsrep_osu_method per each session when the procedure is executed. Not sure what kind of headaches would occur. That was the reason I was hoping to remove the node backup then re add the node. However this wsrep_desync might work. Thank you for the help and explanation. Mar 17, 2022 at 12:17
  • @newdeveloper Running DDL from a stored procedure sounds a bit unusual, but I have seen stranger things. I forgot to mention, although you may be aware, that some ALTER TABLE statements can be done "online" now, i.e. with no or with minimal disruption / locking. (Use ALTER TABLE .... , ALGORITHM =INPLACE | INSTANT - inplace since MySQL 5.7 and instant since 8.0. If the particular ALTER TABLE operation you want to do isn't supported by the algorithm specified, then you'll get an error which your stored procedure can catch in a handler).
    – dbdemon
    Mar 17, 2022 at 16:19
  • The statement DDL command I am using is to create a temp table and insert into the table then select from it then drop a temp table. I didn't think that was all that weird. Mar 17, 2022 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.