• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How To Shut Down a Node in MySQL Cluster

By admin

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

or

$ 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)
Note: Using the shutdown command requires the SHUTDOWN privilege.

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. How to Generate Unique IDs For MySQL Cluster Backups
  2. MySQL: How to kill a Long Running Query using max_execution_time
  3. How to Set Space limits for MySQL for database/schema/table
  4. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  5. Recommended Configuration of the MySQL Performance Schema
  6. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  7. How To Start And Stop MySQL Cluster
  8. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  9. Granting All Privileges On All databases Except One Specific Table in MySQL
  10. Counting Rows Of A Table In MySQL Server

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary