How To Shut Down a Node in MySQL Cluster

One of the key aspects of a highly-available system is that routine maintenance can be carried out without any service interruption to users. MySQL Cluster achieves this through its shared-nothing architecture, and in this post, we will show how to restart the three types of nodes online (without taking the cluster down as a whole).

Management and Data Nodes

1. Using ndb_mgm

The preferred way to shut down a management or data node is to use the ndb_mgm management client. You connect using ndb_mgm similar to:

shell$ ndb_mgm --ndb_connectstring=192.168.56.101:1186,192.168.56.102:1186
-- NDB Cluster -- Management Client --
ndb_mgm>

Alternatively you can specify the command directly using the -e or –execute argument, for example:

shell$ ndb_mgm --ndb_connectstring=192.168.56.101:1186,192.168.56.102:1186 -e "SHUTDOWN"

Use either the STOP or SHUTDOWN command depending on whether a single node or the cluster as a whole should be shut down:

1. To shutdown the cluster as a whole (all management and data nodes):

ndb_mgm> SHUTDOWN

2. To shutdown the node with NodeId = 1:

ndb_mgm> 1 STOP

In either case, ndb_mgm will block until the node or nodes have completed the shutdown.

2. Using Signal

An alternative method to stop a single node is to send the SIGTERM signal, for example:

shell$ kill -s SIGTERM 18693

$ kill -15 18693

Important! Do never send the SIGKILL (kill -9) signal unless it is a severe emergency. SIGKILL will not allow the node to do any handling of the signal; the node must exit immediately. For a data node, a SIGKILL signal, for example, increases the probability of a corrupted NDB file system for the node being killed; a corrupted file system requires the data node to be restarted with –initial to completely re-initialize the node. If both data nodes in a node group end up with a corrupted file system, it will require you to restore from a backup! Do not use SIGKILL unless you are prepared to re-initialize the node!

The ndb_mgm STOP Command Compared to SIGTERM

However for data nodes, stopping a node through using ndb_mgm and sending a SIGTERM signal is not the same thing:

ndb_mgm -e “[NodeId] STOP” goes through a multi-step shutdown intended to “de-load” the node being stopped before the actual shutdown.
SIGTERM tells the node to exit as soon as possible.

This means particularly with much concurrent traffic with some read-only transactions, it is expected to see more aborts when sending the SIGTERM signal compared to the STOP command. However, neither guarantees zero aborts.

SQL Nodes

SQL nodes (mysqld) should be stopped in the same way as when used standalone in MySQL Server. The options are:

1. mysqladmin

The mysqladmin utility can trigger mysqld to shut down using the shutdown command, for example:

shell$ mysqladmin --user=root --password --socket=/var/lib/mysql/mysql.sock shutdown
Enter password:

Note: Using the shutdown command requires the SHUTDOWN privilege.

2. SIGTERM

For mysqld sending a SIGTERM signal is equivalent to shutting down the node using mysqladmin. The advantage is that no password is required. For example:

shell$ kill -s SIGTERM 22327

3. The SHUTDOWN SQL statement

This is available in MySQL Cluster 7.5 and later:

mysql> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)