Chapter 7. Management of MySQL Cluster

Chapter 7. Management of MySQL Cluster
Prev		Next

Chapter 7. Management of MySQL Cluster

Table of Contents

7.1. Summary of MySQL Cluster Start Phases

7.2. Commands in the MySQL Cluster Management Client

7.3. Online Backup of MySQL Cluster

7.3.1. MySQL Cluster Backup Concepts
7.3.2. Using The MySQL Cluster Management Client to Create a Backup
7.3.3. Configuration for MySQL Cluster Backups
7.3.4. MySQL Cluster Backup Troubleshooting

7.4. Event Reports Generated in MySQL Cluster

7.4.1. MySQL Cluster Logging Management Commands
7.4.2. MySQL Cluster Log Events
7.4.3. Using CLUSTERLOG STATISTICS in the MySQL Cluster Management Client

7.5. MySQL Cluster Log Messages

7.5.1. MySQL Cluster — Messages in the Cluster Log
7.5.2. MySQL Cluster — NDB Transporter Errors

7.6. MySQL Cluster Single User Mode

7.7. Quick Reference: MySQL Cluster SQL Statements

7.8. Adding MySQL Cluster Data Nodes Online

7.8.1. Adding MySQL Cluster Data Nodes Online: General Issues
7.8.2. Adding MySQL Cluster Data Nodes Online: Basic procedure
7.8.3. Adding MySQL Cluster Data Nodes Online: Detailed Example

Managing a MySQL Cluster involves a number of tasks, the first of which is to configure and start MySQL Cluster. This is covered in Chapter 3, MySQL Cluster Configuration, and Chapter 6, MySQL Cluster Programs.

The following sections cover the management of a running MySQL Cluster.

For information about security issues relating to management and deployment of a MySQL Cluster, see Chapter 8, MySQL Cluster Security Issues.

There are essentially two methods of actively managing a running MySQL Cluster. The first of these is through the use of commands entered into the management client whereby cluster status can be checked, log levels changed, backups started and stopped, and nodes stopped and started. The second method involves studying the contents of the cluster log ndb_node_id_cluster.log; this is usually found in the management server's DataDir directory, but this location can be overridden using the LogDestination option — see Section 3.4.5, “Defining a MySQL Cluster Management Server”, for details. (Recall that node_id represents the unique identifier of the node whose activity is being logged.) The cluster log contains event reports generated by ndbd. It is also possible to send cluster log entries to a Unix system log.

In addition, some aspects of the cluster's operation can be monitored from an SQL node using the SHOW ENGINE NDB STATUS statement. See SHOW ENGINE Syntax, for more information.

7.1. Summary of MySQL Cluster Start Phases

This section provides a simplified outline of the steps involved when MySQL Cluster data nodes are started. More complete information can be found in MySQL Cluster Start Phases.

These phases are the same as those reported in the output from the node_id STATUS command in the management client. (See Section 7.2, “Commands in the MySQL Cluster Management Client”, for more information about this command.)

Start types. There are several different startup types and modes, as shown here:

Initial Start. The cluster starts with a clean file system on all data nodes. This occurs either when the cluster started for the very first time, or when all data nodes are restarted using the --initial option.
Note
Disk Data files are not removed when restarting a node using --initial.
System Restart. The cluster starts and reads data stored in the data nodes. This occurs when the cluster has been shut down after having been in use, when it is desired for the cluster to resume operations from the point where it left off.
Node Restart. This is the online restart of a cluster node while the cluster itself is running.
Initial Node Restart. This is the same as a node restart, except that the node is reinitialized and started with a clean file system.

Setup and initialization (Phase -1). Prior to startup, each data node (ndbd process) must be initialized. Initialization consists of the following steps:

Obtain a node ID
Fetch configuration data
Allocate ports to be used for inter-node communications
Allocate memory according to settings obtained from the configuration file

When a data node or SQL node first connects to the management node, it reserves a cluster node ID. To make sure that no other node allocates the same node ID, this ID is retained until the node has managed to connect to the cluster and at least one ndbd reports that this node is connected. This retention of the node ID is guarded by the connection between the node in question and ndb_mgmd.

Normally, in the event of a problem with the node, the node disconnects from the management server, the socket used for the connection is closed, and the reserved node ID is freed. However, if a node is disconnected abruptly — for example, due to a hardware failure in one of the cluster hosts, or because of network issues — the normal closing of the socket by the operating system may not take place. In this case, the node ID continues to be reserved and not released until a TCP timeout occurs 10 or so minutes later.

To take care of this problem, you can use PURGE STALE SESSIONS. Running this statement forces all reserved node IDs to be checked; any that are not being used by nodes actually connected to the cluster are then freed.

Beginning with MySQL 5.1.11, timeout handling of node ID assignments is implemented. This performs the ID usage checks automatically after approximately 20 seconds, so that PURGE STALE SESSIONS should no longer be necessary in a normal Cluster start.

After each data node has been initialized, the cluster startup process can proceed. The stages which the cluster goes through during this process are listed here:

Phase 0. The NDBFS and NDBCNTR blocks start (see NDB Kernel Blocks). The cluster file system is cleared, if the cluster was started with the --initial option.
Phase 1. In this stage, all remaining NDB kernel blocks are started. Cluster connections are set up, inter-block communications are established, and Cluster heartbeats are started. In the case of a node restart, API node connections are also checked.
Note
When one or more nodes hang in Phase 1 while the remaining node or nodes hang in Phase 2, this often indicates network problems. One possible cause of such issues is one or more cluster hosts having multiple network interfaces. Another common source of problems causing this condition is the blocking of TCP/IP ports needed for communications between cluster nodes. In the latter case, this is often due to a misconfigured firewall.
Phase 2. The NDBCNTR kernel block checks the states of all existing nodes. The master node is chosen, and the cluster schema file is initialized.
Phase 3. The DBLQH and DBTC kernel blocks set up communications between them. The startup type is determined; if this is a restart, the DBDIH block obtains permission to perform the restart.
Phase 4. For an initial start or initial node restart, the redo log files are created. The number of these files is equal to NoOfFragmentLogFiles.
For a system restart:
- Read schema or schemas.
- Read data from the local checkpoint.
- Apply all redo information until the latest restorable global checkpoint has been reached.
For a node restart, find the tail of the redo log.
Phase 5. Most of the database-related portion of a data node start is performed during this phase. For an initial start or system restart, a local checkpoint is executed, followed by a global checkpoint. Periodic checks of memory usage begin during this phase, and any required node takeovers are performed.
Phase 6. In this phase, node groups are defined and set up.
Phase 7. The arbitrator node is selected and begins to function. The next backup ID is set, as is the backup disk write speed. Nodes reaching this start phase are marked as Started. It is now possible for API nodes (including SQL nodes) to connect to the cluster. connect.
Phase 8. If this is a system restart, all indexes are rebuilt (by DBDIH).
Phase 9. The node internal startup variables are reset.
Phase 100 (OBSOLETE). Formerly, it was at this point during a node restart or initial node restart that API nodes could connect to the node and begin to receive events. Currently, this phase is empty.
Phase 101. At this point in a node restart or initial node restart, event delivery is handed over to the node joining the cluster. The newly-joined node takes over responsibility for delivering its primary data to subscribers. This phase is also referred to as SUMA handover phase.

After this process is completed for an initial start or system restart, transaction handling is enabled. For a node restart or initial node restart, completion of the startup process means that the node may now act as a transaction coordinator.

7.2. Commands in the MySQL Cluster Management Client

In addition to the central configuration file, a cluster may also be controlled through a command-line interface available through the management client ndb_mgm. This is the primary administrative interface to a running cluster.

Commands for the event logs are given in Section 7.4, “Event Reports Generated in MySQL Cluster”; commands for creating backups and restoring from them are provided in Section 7.3, “Online Backup of MySQL Cluster”.

The management client has the following basic commands. In the listing that follows, node_id denotes either a database node ID or the keyword ALL, which indicates that the command should be applied to all of the cluster's data nodes.

HELP
Displays information on all available commands.
SHOW
Displays information on the cluster's status.
Note
In a cluster where multiple management nodes are in use, this command displays information only for data nodes that are actually connected to the current management server.
node_id START
Brings online the data node identified by node_id (or all data nodes).
ALL START works on all data nodes only, and does not affect management nodes.
Important
To use this command to bring a data node online, the data node must have been started using ndbd --nostart or ndbd -n.
node_id STOP
Stops the data or management node identified by node_id. Note that ALL STOP works to stop all data nodes only, and does not affect management nodes.
A node affected by this command disconnects from the cluster, and its associated ndbd or ndb_mgmd process terminates.
node_id RESTART [-n] [-i] [-a]
Restarts the data node identified by node_id (or all data nodes).
Using the -i option with RESTART causes the data node to perform an initial restart; that is, the node's file system is deleted and recreated. The effect is the same as that obtained from stopping the data node process and then starting it again using ndbd --initial from the system shell. Note that backup files and Disk Data files are not removed when this option is used.
Using the -n option causes the data node process to be restarted, but the data node is not actually brought online until the appropriate START command is issued. The effect of this option is the same as that obtained from stopping the data node and then starting it again using ndbd --nostart or ndbd -n from the system shell.
Using the -a causes all current transactions relying on this node to be aborted. No GCP check is done when the node rejoins the cluster.
node_id STATUS
Displays status information for the data node identified by node_id (or for all data nodes).
node_id REPORT report-type
Displays a report of type report-type for the data node identified by node_id, or for all data nodes using ALL.
Currently, there are two accepted values for report-type:
- BackupStatus provides a status report on a cluster backup in progress
- MemoryUsage displays how much data memory and index memory is being used by each data node.
The REPORT command was introduced in MySQL Cluster NDB 6.2.3 and MySQL Cluster NDB 6.3.0.
ENTER SINGLE USER MODE node_id
Enters single user mode, whereby only the MySQL server identified by the node ID node_id is allowed to access the database.
Important
Currently, it is not possible in for data nodes to join a MySQL Cluster while it is running in single user mode. (Bug#20395)
EXIT SINGLE USER MODE
Exits single user mode, allowing all SQL nodes (that is, all running mysqld processes) to access the database.
Note
It is possible to use EXIT SINGLE USER MODE even when not in single user mode, although the command has no effect in this case.
QUIT, EXIT
Terminates the management client.
This command does not affect any nodes connected to the cluster.
SHUTDOWN
Shuts down all cluster data nodes and management nodes. To exit the management client after this has been done, use EXIT or QUIT.
This command does not shut down any SQL nodes or API nodes that are connected to the cluster.
CREATE NODEGROUP nodeid[, nodeid, ...]
Creates a new MySQL Cluster node group and causes data nodes to join it.
This command is used after adding new data nodes online to a MySQL Cluster, and causes them to join a new node group and thus to begin participating fully in the cluster. The command takes as its sole parameter a comma-separated list of node IDs — these are the IDs of the nodes just added and started that are to join the new node group. The number of nodes must be the same as the number of nodes in each node group that is already part of the cluster (each MySQL Cluster node group must have the same number of nodes). In other words, if the MySQL Cluster has 2 node groups of 2 data nodes each, then the new node group must also have 2 data nodes.
The node group ID of the new node group created by this command is determined automatically, and always the next highest unused node group ID in the cluster; it is not possible to set it manually.
This command was introduced in MySQL Cluster NDB 6.4.0. For more information, see Section 7.8, “Adding MySQL Cluster Data Nodes Online”.
DROP NODEGROUP nodegroup_id
Drops the MySQL Cluster node group with the given nodegroup_id.
This command can be used to drop a node group from a MySQL Cluster. DROP NODEGROUP takes as its sole argument the node group ID of the node group to be dropped.
DROP NODEGROUP acts only to remove the data nodes in the effected node group from that node group. It does not stop data nodes, assign them to a different node group, or remove them from the cluster's configuration. A data node that does not belong to a node group is indicated in the output of the management client SHOW command with no nodegroup in place of the node group ID, like this (indicated using bold text):
```
id=3    @10.100.2.67  (5.1.34-ndb-7.0.7, no nodegroup)
```
Prior to MySQL Cluster NDB 7.0.4, the SHOW output was not updated correctly following DROP NODEGROUP. (Bug#43413)
DROP NODEGROUP works only when all data nodes in the node group to be dropped are completely empty of any table data and table definitions. Since there is currently no way using ndb_mgm or in the mysql client to remove all data from a specific data node or node group, this means that the command succeeds only in the two following cases:
1. After issuing CREATE NODEGROUP in the ndb_mgm client, but before issuing any ALTER ONLINE TABLE ... REORGANIZE PARTITION statements in the mysql client.
2. After dropping all NDBCLUSTER tables using DROP TABLE.
  TRUNCATE does not work for this purpose because this removes only the table data; the data nodes continue to store an NDBCLUSTER table's definition until a DROP TABLE statement is issued that causes the table metadata to be dropped.
DROP NODEGROUP was introduced in MySQL Cluster NDB 6.4.0. For more information, see Section 7.8, “Adding MySQL Cluster Data Nodes Online”.

7.3. Online Backup of MySQL Cluster

7.3.1. MySQL Cluster Backup Concepts
7.3.2. Using The MySQL Cluster Management Client to Create a Backup
7.3.3. Configuration for MySQL Cluster Backups
7.3.4. MySQL Cluster Backup Troubleshooting

This section describes how to create a backup and how to restore the database from a backup at a later time.

7.3.1. MySQL Cluster Backup Concepts

A backup is a snapshot of the database at a given time. The backup consists of three main parts:

Metadata. The names and definitions of all database tables
Table records. The data actually stored in the database tables at the time that the backup was made
Transaction log. A sequential record telling how and when data was stored in the database

Each of these parts is saved on all nodes participating in the backup. During backup, each node saves these three parts into three files on disk:

BACKUP-backup_id.node_id.ctl
A control file containing control information and metadata. Each node saves the same table definitions (for all tables in the cluster) to its own version of this file.
BACKUP-backup_id-0.node_id.data
A data file containing the table records, which are saved on a per-fragment basis. That is, different nodes save different fragments during the backup. The file saved by each node starts with a header that states the tables to which the records belong. Following the list of records there is a footer containing a checksum for all records.
BACKUP-backup_id.node_id.log
A log file containing records of committed transactions. Only transactions on tables stored in the backup are stored in the log. Nodes involved in the backup save different records because different nodes host different database fragments.

In the listing above, backup_id stands for the backup identifier and node_id is the unique identifier for the node creating the file.

7.3.2. Using The MySQL Cluster Management Client to Create a Backup

Before starting a backup, make sure that the cluster is properly configured for performing one. (See Section 7.3.3, “Configuration for MySQL Cluster Backups”.)

The START BACKUP command is used to create a backup:

START BACKUP [backup_id] [wait_option] [snapshot_option]
wait_option:
WAIT {STARTED | COMPLETED} | NOWAIT
snapshot_option:
SNAPSHOTSTART | SNAPSHOTEND

Successive backups are automatically identified sequentially, so the backup_id, an integer greater than or equal to 1, is optional; if it is omitted, the next available value is used. If an existing backup_id value is used, the backup fails with the error Backup failed: file already exists. If used, the backup_id must follow START BACKUP immediately, before any other options are used.

Prior to MySQL Cluster NDB 6.2.17, 6.3.23, and 6.4.3, backup IDs greater than 2147483648 (2³¹) were not supported correctly. (Bug#43042) Beginning with these versions, the maximum possible backup ID is 4294967296 (2³²).

Note

Prior to MySQL Cluster NDB 7.0.5, when starting a backup using ndb_mgm -e "START BACKUP", the backup_id was required. (Bug#31754)

The wait_option can be used to determine when control is returned to the management client after a START BACKUP command is issued, as shown in the following list:

If NOWAIT is specified, the management client displays a prompt immediately, as seen here:
```
ndb_mgm> START BACKUP NOWAIT
ndb_mgm>
```
In this case, the management client can be used even while it prints progress information from the backup process.
With WAIT STARTED the management client waits until the backup has started before returning control to the user, as shown here:
```
ndb_mgm> START BACKUP WAIT STARTED
Waiting for started, this may take several minutes
Node 2: Backup 3 started from node 1
ndb_mgm>
```
WAIT COMPLETED causes the management client to wait until the backup process is complete before returning control to the user.

WAIT COMPLETED is the default.

Beginning with MySQL Cluster NDB 6.4.0, a snapshot_option can be used to determine whether the backup matches the state of the cluster when START BACKUP was issued, or when it was completed. SNAPSHOTSTART causes the backup to match the state of the cluster when the backup began; SNAPSHOTEND causes the backup to reflect the state of the cluster when the backup was finished. SNAPSHOTEND is the default, and matches the behavior found in previous MySQL Cluster releases.

Note

If you use the SNAPSHOTSTART option with START BACKUP, and the CompressedBackup parameter is enabled, only the data and control files are compressed — the log file is not compressed.

If both a wait_option and a snapshot_option are used, they may be specified in either order. For example, all of the following commands are valid, assuming that there is no existing backup having 4 as its ID:

START BACKUP WAIT STARTED SNAPSHOTSTART
START BACKUP SNAPSHOTSTART WAIT STARTED
START BACKUP 4 WAIT COMPLETED SNAPSHOTSTART
START BACKUP SNAPSHOTEND WAIT COMPLETED
START BACKUP 4 NOWAIT SNAPSHOTSTART

The procedure for creating a backup consists of the following steps:

Start the management client (ndb_mgm), if it not running already.

Execute the START BACKUP command. This produces several lines of output indicating the progress of the backup, as shown here:

ndb_mgm> START BACKUP
Waiting for completed, this may take several minutes
Node 2: Backup 1 started from node 1
Node 2: Backup 1 started from node 1 completed
 StartGCP: 177 StopGCP: 180
 #Records: 7362 #LogRecords: 0
 Data: 453648 bytes Log: 0 bytes
ndb_mgm>

When the backup has started the management client displays this message:
```
Backup backup_id started from node node_id
```
backup_id is the unique identifier for this particular backup. This identifier is saved in the cluster log, if it has not been configured otherwise. node_id is the identifier of the management server that is coordinating the backup with the data nodes. At this point in the backup process the cluster has received and processed the backup request. It does not mean that the backup has finished. An example of this statement is shown here:
```
	
Node 2: Backup 1 started from node 1
```
The management client indicates with a message like this one that the backup has started:
```
Backup backup_id started from node node_id completed
```
As is the case for the notification that the backup has started, backup_id is the unique identifier for this particular backup, and node_id is the node ID of the management server that is coordinating the backup with the data nodes. This output is accompanied by additional information including relevant global checkpoints, the number of records backed up, and the size of the data, as shown here:
```
	
Node 2: Backup 1 started from node 1 completed
 StartGCP: 177 StopGCP: 180
 #Records: 7362 #LogRecords: 0
 Data: 453648 bytes Log: 0 bytes
```

It is also possible to perform a backup from the system shell by invoking ndb_mgm with the -e or --execute option, as shown in this example:

shell> ndb_mgm -e "START BACKUP 6 WAIT COMPLETED SNAPSHOTSTART"

When using START BACKUP in this way, you must specify the backup ID.

Cluster backups are created by default in the BACKUP subdirectory of the DataDir on each data node. This can be overridden for one or more data nodes individually, or for all cluster data nodes in the config.ini file using the BackupDataDir configuration parameter as discussed in Identifying Data Nodes. The backup files created for a backup with a given backup_id are stored in a subdirectory named BACKUP-backup_id in the backup directory.

To abort a backup that is already in progress:

Start the management client.
Execute this command:
```
ndb_mgm> ABORT BACKUP backup_id
```
The number backup_id is the identifier of the backup that was included in the response of the management client when the backup was started (in the message Backup backup_id started from node management_node_id).
The management client will acknowledge the abort request with Abort of backup backup_id ordered.
Note
At this point, the management client has not yet received a response from the cluster data nodes to this request, and the backup has not yet actually been aborted.
After the backup has been aborted, the management client will report this fact in a manner similar to what is shown here:
```
Node 1: Backup 3 started from 5 has been aborted. Error: 1321 - Backup aborted by user request: Permanent error: User defined error
Node 3: Backup 3 started from 5 has been aborted. Error: 1323 - 1323: Permanent error: Internal error
Node 2: Backup 3 started from 5 has been aborted. Error: 1323 - 1323: Permanent error: Internal error
Node 4: Backup 3 started from 5 has been aborted. Error: 1323 - 1323: Permanent error: Internal error
```
In this example, we have shown sample output for a cluster with 4 data nodes, where the sequence number of the backup to be aborted is 3, and the management node to which the cluster management client is connected has the node ID 5. The first node to complete its part in aborting the backup reports that the reason for the abort was due to a request by the user. (The remaining nodes report that the backup was aborted due to an unspecified internal error.)
Note
There is no guarantee that the cluster nodes respond to an ABORT BACKUP command in any particular order.
The Backup backup_id started from node management_node_id has been aborted messages mean that the backup has been terminated and that all files relating to this backup have been removed from the cluster file system.

It is also possible to abort a backup in progress from a system shell using this command:

shell> ndb_mgm -e "ABORT BACKUP backup_id"

Note

If there is no backup having the ID backup_id running when an ABORT BACKUP is issued, the management client makes no response, nor is it indicated in the cluster log that an invalid abort command was sent.

7.3.3. Configuration for MySQL Cluster Backups

Five configuration parameters are essential for backup:

BackupDataBufferSize
The amount of memory used to buffer data before it is written to disk.
BackupLogBufferSize
The amount of memory used to buffer log records before these are written to disk.
BackupMemory
The total memory allocated in a database node for backups. This should be the sum of the memory allocated for the backup data buffer and the backup log buffer.
BackupWriteSize
The default size of blocks written to disk. This applies for both the backup data buffer and the backup log buffer.
BackupMaxWriteSize
The maximum size of blocks written to disk. This applies for both the backup data buffer and the backup log buffer.

More detailed information about these parameters can be found in Backup Parameters.

7.3.4. MySQL Cluster Backup Troubleshooting

If an error code is returned when issuing a backup request, the most likely cause is insufficient memory or disk space. You should check that there is enough memory allocated for the backup.

Important

If you have set BackupDataBufferSize and BackupLogBufferSize and their sum is greater than 4MB, then you must also set BackupMemory as well. See BackupMemory.

You should also make sure that there is sufficient space on the hard drive partition of the backup target.

NDB does not support repeatable reads, which can cause problems with the restoration process. Although the backup process is “hot”, restoring a MySQL Cluster from backup is not a 100% “hot” process. This is due to the fact that, for the duration of the restore process, running transactions get nonrepeatable reads from the restored data. This means that the state of the data is inconsistent while the restore is in progress.

MySQL Enterprise MySQL Enterprise subscribers will find more information about Cluster backup in the Knowledge Base article, How Do I Backup my Cluster Database. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

7.4. Event Reports Generated in MySQL Cluster

7.4.1. MySQL Cluster Logging Management Commands
7.4.2. MySQL Cluster Log Events
7.4.3. Using CLUSTERLOG STATISTICS in the MySQL Cluster Management Client

In this section, we discuss the types of event logs provided by MySQL Cluster, and the types of events that are logged.

MySQL Cluster provides two types of event log:

The cluster log, which includes events generated by all cluster nodes. The cluster log is the log recommended for most uses because it provides logging information for an entire cluster in a single location.
By default, the cluster log is saved to a file named ndb_node_id_cluster.log, (where node_id is the node ID of the management server) in the same directory where the ndb_mgm binary resides.
Cluster logging information can also be sent to stdout or a syslog facility in addition to or instead of being saved to a file, as determined by the values set for the DataDir and LogDestination configuration parameters. See Section 3.4.5, “Defining a MySQL Cluster Management Server”, for more information about these parameters.
Node logs are local to each node.
Output generated by node event logging is written to the file ndb_node_id_out.log (where node_id is the node's node ID) in the node's DataDir. Node event logs are generated for both management nodes and data nodes.
Node logs are intended to be used only during application development, or for debugging application code.

Both types of event logs can be set to log different subsets of events.

Each reportable event can be distinguished according to three different criteria:

Category: This can be any one of the following values: STARTUP, SHUTDOWN, STATISTICS, CHECKPOINT, NODERESTART, CONNECTION, ERROR, or INFO.
Priority: This is represented by one of the numbers from 1 to 15 inclusive, where 1 indicates “most important” and 15 “least important.”
Severity Level: This can be any one of the following values: ALERT, CRITICAL, ERROR, WARNING, INFO, or DEBUG.

Both the cluster log and the node log can be filtered on these properties.

The format used in the cluster log is as shown here:

2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 1: Data usage is 2%(60 32K pages of total 2560)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 1: Index usage is 1%(24 8K pages of total 2336)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 1: Resource 0 min: 0 max: 639 curr: 0
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 2: Data usage is 2%(76 32K pages of total 2560)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 2: Index usage is 1%(24 8K pages of total 2336)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 2: Resource 0 min: 0 max: 639 curr: 0
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 3: Data usage is 2%(58 32K pages of total 2560)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 3: Index usage is 1%(25 8K pages of total 2336)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 3: Resource 0 min: 0 max: 639 curr: 0
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 4: Data usage is 2%(74 32K pages of total 2560)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 4: Index usage is 1%(25 8K pages of total 2336)
2007-01-26 19:35:55 [MgmSrvr] INFO     -- Node 4: Resource 0 min: 0 max: 639 curr: 0
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 4: Node 9 Connected
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 1: Node 9 Connected
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 1: Node 9: API version 5.1.15
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 2: Node 9 Connected
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 2: Node 9: API version 5.1.15
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 3: Node 9 Connected
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 3: Node 9: API version 5.1.15
2007-01-26 19:39:42 [MgmSrvr] INFO     -- Node 4: Node 9: API version 5.1.15
2007-01-26 19:59:22 [MgmSrvr] ALERT    -- Node 2: Node 7 Disconnected
2007-01-26 19:59:22 [MgmSrvr] ALERT    -- Node 2: Node 7 Disconnected

Each line in the cluster log contains the following information:

A timestamp in YYYY-MM-DD HH:MM:SS format.
The type of node which is performing the logging. In the cluster log, this is always [MgmSrvr].
The severity of the event.
The ID of the node reporting the event.
A description of the event. The most common types of events to appear in the log are connections and disconnections between different nodes in the cluster, and when checkpoints occur. In some cases, the description may contain status information.

7.4.1. MySQL Cluster Logging Management Commands

The following management commands are related to the cluster log:

CLUSTERLOG ON
Turns the cluster log on.
CLUSTERLOG OFF
Turns the cluster log off.
CLUSTERLOG INFO
Provides information about cluster log settings.
node_id CLUSTERLOG category=threshold
Logs category events with priority less than or equal to threshold in the cluster log.
CLUSTERLOG FILTER severity_level
Toggles cluster logging of events of the specified severity_level.

The following table describes the default setting (for all data nodes) of the cluster log category threshold. If an event has a priority with a value lower than or equal to the priority threshold, it is reported in the cluster log.

Note that events are reported per data node, and that the threshold can be set to different values on different nodes.

Category	Default threshold (All data nodes)
`STARTUP`	7
`SHUTDOWN`	7
`STATISTICS`	7
`CHECKPOINT`	7
`NODERESTART`	7
`CONNECTION`	7
`ERROR`	15
`INFO`	7

The STATISTICS category can provide a great deal of useful data. See Section 7.4.3, “Using CLUSTERLOG STATISTICS in the MySQL Cluster Management Client”, for more information.

Thresholds are used to filter events within each category. For example, a STARTUP event with a priority of 3 is not logged unless the threshold for STARTUP is set to 3 or higher. Only events with priority 3 or lower are sent if the threshold is 3.

The following table shows the event severity levels.

Note

These correspond to Unix syslog levels, except for LOG_EMERG and LOG_NOTICE, which are not used or mapped.

1	`ALERT`	A condition that should be corrected immediately, such as a corrupted system database
2	`CRITICAL`	Critical conditions, such as device errors or insufficient resources
3	`ERROR`	Conditions that should be corrected, such as configuration errors
4	`WARNING`	Conditions that are not errors, but that might require special handling
5	`INFO`	Informational messages
6	`DEBUG`	Debugging messages used for `NDBCLUSTER` development

Event severity levels can be turned on or off (using CLUSTERLOG FILTER — see above). If a severity level is turned on, then all events with a priority less than or equal to the category thresholds are logged. If the severity level is turned off then no events belonging to that severity level are logged.

Important

Cluster log levels are set on a per ndb_mgmd, per subscriber basis. This means that, in a MySQL Cluster with multiple management servers, using a CLUSTERLOG command in an instance of ndb_mgm connected to one management server affects only logs generated by that management server but not by any of the others. This also means that, should one of the management servers be restarted, only logs generated by that management server are affected by the resetting of log levels caused by the restart.

7.4.2. MySQL Cluster Log Events

An event report reported in the event logs has the following format:

datetime [string] severity -- message

For example:

09:19:30 2005-07-24 [NDB] INFO -- Node 4 Start phase 4 completed

This section discusses all reportable events, ordered by category and severity level within each category.

In the event descriptions, GCP and LCP mean “Global Checkpoint” and “Local Checkpoint”, respectively.

CONNECTION Events

These events are associated with connections between Cluster nodes.

Event	Priority	Severity Level	Description
data nodes connected	8	`INFO`	Data nodes connected
data nodes disconnected	8	`INFO`	Data nodes disconnected
Communication closed	8	`INFO`	SQL node or data node connection closed
Communication opened	8	`INFO`	SQL node or data node connection opened

CHECKPOINT Events

The logging messages shown here are associated with checkpoints.

Event	Priority	Severity Level	Description
LCP stopped in calc keep GCI	0	`ALERT`	LCP stopped
Local checkpoint fragment completed	11	`INFO`	LCP on a fragment has been completed
Global checkpoint completed	10	`INFO`	GCP finished
Global checkpoint started	9	`INFO`	Start of GCP: REDO log is written to disk
Local checkpoint completed	8	`INFO`	LCP completed normally
Local checkpoint started	7	`INFO`	Start of LCP: data written to disk

STARTUP Events

The following events are generated in response to the startup of a node or of the cluster and of its success or failure. They also provide information relating to the progress of the startup process, including information concerning logging activities.

Event	Priority	Severity Level	Description
Internal start signal received STTORRY	15	`INFO`	Blocks received after completion of restart
New REDO log started	10	`INFO`	GCI keep `X`, newest restorable GCI `Y`
New log started	10	`INFO`	Log part `X`, start MB `Y`, stop MB `Z`
Node has been refused for inclusion in the cluster	8	`INFO`	Node cannot be included in cluster due to misconfiguration, inability to establish communication, or other problem
data node neighbors	8	`INFO`	Shows neighboring data nodes
data node start phase `X` completed	4	`INFO`	A data node start phase has been completed
Node has been successfully included into the cluster	3	`INFO`	Displays the node, managing node, and dynamic ID
data node start phases initiated	1	`INFO`	NDB Cluster nodes starting
data node all start phases completed	1	`INFO`	NDB Cluster nodes started
data node shutdown initiated	1	`INFO`	Shutdown of data node has commenced
data node shutdown aborted	1	`INFO`	Unable to shut down data node normally

NODERESTART Events

The following events are generated when restarting a node and relate to the success or failure of the node restart process.

Event	Priority	Severity Level	Description
Node failure phase completed	8	`ALERT`	Reports completion of node failure phases
Node has failed, node state was `X`	8	`ALERT`	Reports that a node has failed
Report arbitrator results	2	`ALERT`	There are eight different possible results for arbitration attempts: Arbitration check failed — less than 1/2 nodes left Arbitration check succeeded — node group majority Arbitration check failed — missing node group Network partitioning — arbitration required Arbitration succeeded — affirmative response from node `X` Arbitration failed - negative response from node `X` Network partitioning - no arbitrator available Network partitioning - no arbitrator configured
Completed copying a fragment	10	`INFO`
Completed copying of dictionary information	8	`INFO`
Completed copying distribution information	8	`INFO`
Starting to copy fragments	8	`INFO`
Completed copying all fragments	8	`INFO`
GCP takeover started	7	`INFO`
GCP takeover completed	7	`INFO`
LCP takeover started	7	`INFO`
LCP takeover completed (state = `X`)	7	`INFO`
Report whether an arbitrator is found or not	6	`INFO`	There are seven different possible outcomes when seeking an arbitrator: Management server restarts arbitration thread [state=`X`] Prepare arbitrator node `X` [ticket=`Y`] Receive arbitrator node `X` [ticket=`Y`] Started arbitrator node `X` [ticket=`Y`] Lost arbitrator node `X` - process failure [state=`Y`] Lost arbitrator node `X` - process exit [state=`Y`] Lost arbitrator node `X` <error msg> [state=`Y`]

STATISTICS Events

The following events are of a statistical nature. They provide information such as numbers of transactions and other operations, amount of data sent or received by individual nodes, and memory usage.

Event	Priority	Severity Level	Description
Report job scheduling statistics	9	`INFO`	Mean internal job scheduling statistics
Sent number of bytes	9	`INFO`	Mean number of bytes sent to node `X`
Received # of bytes	9	`INFO`	Mean number of bytes received from node `X`
Report transaction statistics	8	`INFO`	Numbers of: transactions, commits, reads, simple reads, writes, concurrent operations, attribute information, and aborts
Report operations	8	`INFO`	Number of operations
Report table create	7	`INFO`
Memory usage	5	`INFO`	Data and index memory usage (80%, 90%, and 100%)

ERROR Events

These events relate to Cluster errors and warnings. The presence of one or more of these generally indicates that a major malfunction or failure has occurred.

Event	Priority	Severity	Description
Dead due to missed heartbeat	8	`ALERT`	Node `X` declared “dead” due to missed heartbeat
Transporter errors	2	ERROR
Transporter warnings	8	`WARNING`
Missed heartbeats	8	`WARNING`	Node `X` missed heartbeat #`Y`
General warning events	2	`WARNING`

INFO Events

These events provide general information about the state of the cluster and activities associated with Cluster maintenance, such as logging and heartbeat transmission.

Event	Priority	Severity	Description
Sent heartbeat	12	`INFO`	Heartbeat sent to node `X`
Create log bytes	11	`INFO`	Log part, log file, MB
General information events	2	`INFO`

7.4.3. Using `CLUSTERLOG STATISTICS` in the MySQL Cluster Management Client

The NDB management client's CLUSTERLOG STATISTICS command can provide a number of useful statistics in its output. Counters providing information about the state of the cluster are updated at 5-second reporting intervals by the transaction coordinator (TC) and the local query handler (LQH), and written to the cluster log.

Transaction coordinator statistics. Each transaction has one transaction coordinator, which is chosen by one of the following methods:

In a round-robin fashion
By communication proximity
(Beginning with MySQL Cluster NDB 6.3.4:) By supplying a data placement hint when the transaction is started

Note

You can determine which TC selection method is used for transactions started from a given SQL node using the ndb_optimized_node_selection system variable.

All operations within the same transaction use the same transaction coordinator, which reports the following statistics:

Trans count. This is the number transactions started in the last interval using this TC as the transaction coordinator. Any of these transactions may have committed, have been aborted, or remain uncommitted at the end of the reporting interval.
Note
Transactions do not migrate between TCs.
Commit count. This is the number of transactions using this TC as the transaction coordinator that were committed in the last reporting interval. Because some transactions committed in this reporting interval may have started in a previous reporting interval, it is possible for Commit count to be greater than Trans count.
Read count. This is the number of primary key read operations using this TC as the transaction coordinator that were started in the last reporting interval, including simple reads. This count also includes reads performed as part of unique index operations. A unique index read operation generates 2 primary key read operations — 1 for the hidden unique index table, and 1 for the table on which the read takes place.
Simple read count. This is the number of simple read operations using this TC as the transaction coordinator that were started in the last reporting interval. This is a subset of Read count. Because the value of Simple read count is incremented at a different point in time from Read count, it can lag behind Read count slightly, so it is conceivable that Simple read count is not equal to Read count for a given reporting interval, even if all reads made during that time were in fact simple reads.
Write count. This is the number of primary key write operations using this TC as the transaction coordinator that were started in the last reporting interval. This includes all inserts, updates, writes and deletes, as well as writes performed as part of unique index operations.
Note
A unique index update operation can generate multiple PK read and write operations on the index table and on the base table.
AttrInfoCount. This is the number of 32-bit data words received in the last reporting interval for primary key operations using this TC as the transaction coordinator. For reads, this is proportional to the number of columns requested. For inserts and updates, this is proportional to the number of columns written, and the size of their data. For delete operations, this is usually zero. Unique index operations generate multiple PK operations and so increase this count. However, data words sent to describe the PK operation itself, and the key information sent, are not counted here. Attribute information sent to describe columns to read for scans, or to describe ScanFilters, is also not counted in AttrInfoCount.
Concurrent Operations. This is the number of primary key or scan operations using this TC as the transaction coordinator that were started during the last reporting interval but that were not completed. Operations increment this counter when they are started and decrement it when they are completed; this occurs after the transaction commits. Dirty reads and writes — as well as failed operations — decrement this counter. The maximum value that Concurrent Operations can have is the maximum number of operations that a TC block can support; currently, this is (2 * MaxNoOfConcurrentOperations) + 16 + MaxNoOfConcurrentTransactions. (For more information about these configuration parameters, see the Transaction Parameters section of Section 3.4.6, “Defining MySQL Cluster Data Nodes”.)
Abort count. This is the number of transactions using this TC as the transaction coordinator that were aborted during the last reporting interval. Because some transactions that were aborted in the last reporting interval may have started in a previous reporting interval, Abort count can sometimes be greater than Trans count.
Scans. This is the number of table scans using this TC as the transaction coordinator that were started during the last reporting interval. This does not include range scans (that is, ordered index scans).
Range scans. This is the number of ordered index scans using this TC as the transaction coordinator that were started in the last reporting interval.

Local query handler statistics (Operations). There is 1 cluster event per local query handler block (that is, 1 per data node process). Operations are recorded in the LQH where the data they are operating on resides.

Note

A single transaction may operate on data stored in multiple LQH blocks.

The Operations statistic provides the number of local operations performed by this LQH block in the last reporting interval, and includes all types of read and write operations (insert, update, write, and delete operations). This also includes operations used to replicate writes — for example, in a 2-replica cluster, the write to the primary replica is recorded in the primary LQH, and the write to the backup will be recorded in the backup LQH. Unique key operations may result in multiple local operations; however, this does not include local operations generated as a result of a table scan or ordered index scan, which are not counted.

Process scheduler statistics. In addition to the statistics reported by the transaction coordinator and local query handler, each ndbd process has a scheduler which also provides useful metrics relating to the performance of a MySQL Cluster. This scheduler runs in an infinite loop; during each loop the scheduler performs the following tasks:

Read any incoming messages from sockets into a job buffer.
Check whether there are any timed messages to be executed; if so, put these into the job buffer as well.
Execute (in a loop) any messages in the job buffer.
Send any distributed messages that were generated by executing the messages in the job buffer.
Wait for any new incoming messages.

Process scheduler statistics include the following:

Mean Loop Counter. This is the number of loops executed in the third step from the preceding list. This statistic increases in size as the utilization of the TCP/IP buffer improves. You can use this to monitor changes in performance as you add new data node processes.
Mean send size and Mean receive size. These statistics allow you to gauge the efficiency of, respectively writes and reads between nodes. The values are given in bytes. Higher values mean a lower cost per byte sent or received; the maximum value is 64K.

To cause all cluster log statistics to be logged, you can use the following command in the NDB management client:

ndb_mgm> ALL CLUSTERLOG STATISTICS=15

Note

Setting the threshold for STATISTICS to 15 causes the cluster log to become very verbose, and to grow quite rapidly in size, in direct proportion to the number of cluster nodes and the amount of activity in the MySQL Cluster.

For more information about MySQL Cluster management client commands relating to logging and reporting, see Section 7.4.1, “MySQL Cluster Logging Management Commands”.

7.5. MySQL Cluster Log Messages

7.5.1. MySQL Cluster — Messages in the Cluster Log
7.5.2. MySQL Cluster — NDB Transporter Errors

This section contains information about the messages written to the cluster log in response to different cluster log events. It provides additional, more specific information on NDB transporter errors.

7.5.1. MySQL Cluster — Messages in the Cluster Log

The following table lists the most common NDB cluster log messages. For information about the cluster log, log events, and event types, see Section 7.4, “Event Reports Generated in MySQL Cluster”. These log messages also correspond to log event types in the MGM API; see The Ndb_logevent_type Type, for related information of interest to Cluster API developers.

Log Message. Node mgm_node_id: Node data_node_id Connected

Description. The data node having node ID node_id has connected to the management server (node mgm_node_id).

Event Name. Connected

Event Type. Connection

Priority. 8

Severity. INFO

Log Message. Node mgm_node_id: Node data_node_id Disconnected

Description. The data node having node ID data_node_id has disconnected from the management server (node mgm_node_id).

Event Name. Disconnected

Event Type. Connection

Priority. 8

Severity. ALERT

Log Message. Node data_node_id: Communication to Node api_node_id closed

Description. The API node or SQL node having node ID api_node_id is no longer communicating with data node data_node_id.

Event Name. CommunicationClosed

Event Type. Connection

Priority. 8

Severity. INFO

Log Message. Node data_node_id: Communication to Node api_node_id opened

Description. The API node or SQL node having node ID api_node_id is now communicating with data node data_node_id.

Event Name. CommunicationOpened

Event Type. Connection

Priority. 8

Severity. INFO

Log Message. Node mgm_node_id: Node api_node_id: API version

Description. The API node having node ID api_node_id has connected to management node mgm_node_id using NDB API version version (generally the same as the MySQL version number).

Event Name. ConnectedApiVersion

Event Type. Connection

Priority. 8

Severity. INFO

Log Message. Node node_id: Global checkpoint gci started

Description. A global checkpoint with the ID gci has been started; node node_id is the master responsible for this global checkpoint.

Event Name. GlobalCheckpointStarted

Event Type. Checkpoint

Priority. 9

Severity. INFO

Log Message. Node node_id: Global checkpoint gci completed

Description. The global checkpoint having the ID gci has been completed; node node_id was the master responsible for this global checkpoint.

Event Name. GlobalCheckpointCompleted

Event Type. Checkpoint

Priority. 10

Severity. INFO

Log Message. Node node_id: Local checkpoint lcp started. Keep GCI = current_gci oldest restorable GCI = old_gci

Description. The local checkpoint having sequence ID lcp has been started on node node_id. The most recent GCI that can be used has the index current_gci, and the oldest GCI from which the cluster can be restored has the index old_gci.

Event Name. LocalCheckpointStarted

Event Type. Checkpoint

Priority. 7

Severity. INFO

Log Message. Node node_id: Local checkpoint lcp completed

Description. The local checkpoint having sequence ID lcp on node node_id has been completed.

Event Name. LocalCheckpointCompleted

Event Type. Checkpoint

Priority. 8

Severity. INFO

Log Message. Node node_id: Local Checkpoint stopped in CALCULATED_KEEP_GCI

Description. The node was unable to determine the most recent usable GCI.

Event Name. LCPStoppedInCalcKeepGci

Event Type. Checkpoint

Priority. 0

Severity. ALERT

Log Message. Node node_id: Table ID = table_id, fragment ID = fragment_id has completed LCP on Node node_id maxGciStarted: started_gci maxGciCompleted: completed_gci

Description. A table fragment has been checkpointed to disk on node node_id. The GCI in progress has the index started_gci, and the most recent GCI to have been completed has the index completed_gci.

Event Name. LCPFragmentCompleted

Event Type. Checkpoint

Priority. 11

Severity. INFO

Log Message. Node node_id: ACC Blocked num_1 and TUP Blocked num_2 times last second

Description. Undo logging is blocked because the log buffer is close to overflowing.

Event Name. UndoLogBlocked

Event Type. Checkpoint

Priority. 7

Severity. INFO

Log Message. Node node_id: Start initiated version

Description. Data node node_id, running NDB version version, is beginning its startup process.

Event Name. NDBStartStarted

Event Type. StartUp

Priority. 1

Severity. INFO

Log Message. Node node_id: Started version

Description. Data node node_id, running NDB version version, has started successfully.

Event Name. NDBStartCompleted

Event Type. StartUp

Priority. 1

Severity. INFO

Log Message. Node node_id: STTORRY received after restart finished

Description. The node has received a signal indicating that a cluster restart has completed.

Event Name. STTORRYRecieved

Event Type. StartUp

Priority. 15

Severity. INFO

Log Message. Node node_id: Start phase phase completed (type)

Description. The node has completed start phase phase of a type start. For a listing of start phases, see Section 7.1, “Summary of MySQL Cluster Start Phases”. (type is one of initial, system, node, initial node, or <Unknown>.)

Event Name. StartPhaseCompleted

Event Type. StartUp

Priority. 4

Severity. INFO

Log Message. Node node_id: CM_REGCONF president = president_id, own Node = own_id, our dynamic id = dynamic_id

Description. Node president_id has been selected as “president”. own_id and dynamic_id should always be the same as the ID (node_id) of the reporting node.

Event Name. CM_REGCONF

Event Type. StartUp

Priority. 3

Severity. INFO

Log Message. Node node_id: CM_REGREF from Node president_id to our Node node_id. Cause = cause

Description. The reporting node (ID node_id) was unable to accept node president_id as president. The cause of the problem is given as one of Busy, Election with wait = false, Not president, Election without selecting new candidate, or No such cause.

Event Name. CM_REGREF

Event Type. StartUp

Priority. 8

Severity. INFO

Log Message. Node node_id: We are Node own_id with dynamic ID dynamic_id, our left neighbour is Node id_1, our right is Node id_2

Description. The node has discovered its neighboring nodes in the cluster (node id_1 and node id_2). node_id, own_id, and dynamic_id should always be the same; if they are not, this indicates a serious misconfiguration of the cluster nodes.

Event Name. FIND_NEIGHBOURS

Event Type. StartUp

Priority. 8

Severity. INFO

Log Message. Node node_id: type shutdown initiated

Description. The node has received a shutdown signal. The type of shutdown is either Cluster or Node.

Event Name. NDBStopStarted

Event Type. StartUp

Priority. 1

Severity. INFO

Log Message. Node node_id: Node shutdown completed[, action] [Initiated by signal signal.]

Description. The node has been shut down. This report may include an action, which if present is one of restarting, no start, or initial. The report may also include a reference to an NDB Protocol signal; for possible signals, refer to Operations and Signals.

Event Name. NDBStopCompleted

Event Type. StartUp

Priority. 1

Severity. INFO

Log Message. Node node_id: Forced node shutdown completed[, action]. [Occured during startphase start_phase.] [Initiated by signal.] [Caused by error error_code: 'error_message(error_classification). error_status'. [(extra info extra_code)]]

Description. The node has been forcibly shut down. The action (one of restarting, no start, or initial) subsequently being taken, if any, is also reported. If the shutdown occurred while the node was starting, the report includes the start_phase during which the node failed. If this was a result of a signal sent to the node, this information is also provided (see Operations and Signals, for more information). If the error causing the failure is known, this is also included; for more information about NDB error messages and classifications, see MySQL Cluster API Errors.

Event Name. NDBStopForced

Event Type. StartUp

Priority. 1

Severity. ALERT

Log Message. Node node_id: Node shutdown aborted

Description. The node shutdown process was aborted by the user.

Event Name. NDBStopAborted

Event Type. StartUp

Priority. 1

Severity. INFO

Log Message. Node node_id: StartLog: [GCI Keep: keep_pos LastCompleted: last_pos NewestRestorable: restore_pos]

Description. This reports global checkpoints referenced during a node start. The redo log prior to keep_pos is dropped. last_pos is the last global checkpoint in which data node the participated; restore_pos is the global checkpoint which is actually used to restore all data nodes.

Event Name. StartREDOLog

Event Type. StartUp

Priority. 4

Severity. INFO

Log Message. startup_message [Listed separately; see below.]

Description. There are a number of possible startup messages that can be logged under different circumstances.

Event Name. StartReport

Event Type. StartUp

Priority. 4

Severity. INFO

Log Message. Node node_id: Node restart completed copy of dictionary information

Description. Copying of data dictionary information to the restarted node has been completed.

Event Name. NR_CopyDict

Event Type. NodeRestart

Priority. 8

Severity. INFO

Log Message. Node node_id: Node restart completed copy of distribution information

Description. Copying of data distribution information to the restarted node has been completed.

Event Name. NR_CopyDistr

Event Type. NodeRestart

Priority. 8

Severity. INFO

Log Message. Node node_id: Node restart starting to copy the fragments to Node node_id

Description. Copy of fragments to starting data node node_id has begun

Event Name. NR_CopyFragsStarted

Event Type. NodeRestart

Priority. 8

Severity. INFO

Log Message. Node node_id: Table ID = table_id, fragment ID = fragment_id have been copied to Node node_id

Description. Fragment fragment_id from table table_id has been copied to data node node_id

Event Name. NR_CopyFragDone

Event Type. NodeRestart

Priority. 10

Severity. INFO

Log Message. Node node_id: Node restart completed copying the fragments to Node node_id

Description. Copying of all table fragments to restarting data node node_id has been completed

Event Name. NR_CopyFragsCompleted

Event Type. NodeRestart

Priority. 8

Severity. INFO

Log Message. Any of the following:

Node node_id: Node node1_id completed failure of Node node2_id
All nodes completed failure of Node node_id
Node failure of node_idblock completed

Description. One of the following (each corresponding to the same-numbered message listed above):

Data node node1_id has detected the failure of data node node2_id
All (remaining) data nodes have detected the failure of data node node_id
The failure of data node node_id has been detected in the blockNDB kernel block, where block is 1 of DBTC, DBDICT, DBDIH, or DBLQH; for more information, see NDB Kernel Blocks

Event Name. NodeFailCompleted

Event Type. NodeRestart

Priority. 8

Severity. ALERT

Log Message. Node mgm_node_id: Node data_node_id has failed. The Node state at failure was state_code

Description. A data node has failed. Its state at the time of failure is described by an arbitration state code state_code: possible state code values can be found in the file include/kernel/signaldata/ArbitSignalData.hpp.

Event Name. NODE_FAILREP

Event Type. NodeRestart

Priority. 8

Severity. ALERT

Log Message. President restarts arbitration thread [state=state_code] or Prepare arbitrator node node_id [ticket=ticket_id] or Receive arbitrator node node_id [ticket=ticket_id] or Started arbitrator node node_id [ticket=ticket_id] or Lost arbitrator node node_id - process failure [state=state_code] or Lost arbitrator node node_id - process exit [state=state_code] or Lost arbitrator node node_id - error_message [state=state_code]

Description. This is a report on the current state and progress of arbitration in the cluster. node_id is the node ID of the management node or SQL node selected as the arbitrator. state_code is an arbitration state code, as found in include/kernel/signaldata/ArbitSignalData.hpp. When an error has occurred, an error_message, also defined in ArbitSignalData.hpp, is provided. ticket_id is a unique identifier handed out by the arbitrator when it is selected to all the nodes that participated in its selection; this is used to insure that each node requesting arbitration was one of the nodes that took part in the selection process.

Event Name. ArbitState

Event Type. NodeRestart

Priority. 6

Severity. INFO

Log Message. Arbitration check lost - less than 1/2 nodes left or Arbitration check won - all node groups and more than 1/2 nodes left or Arbitration check won - node group majority or Arbitration check lost - missing node group or Network partitioning - arbitration required or Arbitration won - positive reply from node node_id or Arbitration lost - negative reply from node node_id or Network partitioning - no arbitrator available or Network partitioning - no arbitrator configured or Arbitration failure - error_message [state=state_code]

Description. This message reports on the result of arbitration. In the event of arbitration failure, an error_message and an arbitration state_code are provided; definitions for both of these are found in include/kernel/signaldata/ArbitSignalData.hpp.

Event Name. ArbitResult

Event Type. NodeRestart

Priority. 2

Severity. ALERT

Log Message. Node node_id: GCP Take over started

Description. This node is attempting to assume responsibility for the next global checkpoint (that is, it is becoming the master node)

Event Name. GCP_TakeoverStarted

Event Type. NodeRestart

Priority. 7

Severity. INFO

Log Message. Node node_id: GCP Take over completed

Description. This node has become the master, and has assumed responsibility for the next global checkpoint

Event Name. GCP_TakeoverCompleted

Event Type. NodeRestart

Priority. 7

Severity. INFO

Log Message. Node node_id: LCP Take over started

Description. This node is attempting to assume responsibility for the next set of local checkpoints (that is, it is becoming the master node)

Event Name. LCP_TakeoverStarted

Event Type. NodeRestart

Priority. 7

Severity. INFO

Log Message. Node node_id: LCP Take over completed

Description. This node has become the master, and has assumed responsibility for the next set of local checkpoints

Event Name. LCP_TakeoverCompleted

Event Type. NodeRestart

Priority. 7

Severity. INFO

Log Message. Node node_id: Trans. Count = transactions, Commit Count = commits, Read Count = reads, Simple Read Count = simple_reads, Write Count = writes, AttrInfo Count = AttrInfo_objects, Concurrent Operations = concurrent_operations, Abort Count = aborts, Scans = scans, Range scans = range_scans

Description. This report of transaction activity is given approximately once every 10 seconds

Event Name. TransReportCounters

Event Type. Statistic

Priority. 8

Severity. INFO

Log Message. Node node_id: Operations=operations

Description. Number of operations performed by this node, provided approximately once every 10 seconds

Event Name. OperationReportCounters

Event Type. Statistic

Priority. 8

Severity. INFO

Log Message. Node node_id: Table with ID = table_id created

Description. A table having the table ID shown has been created

Event Name. TableCreated

Event Type. Statistic

Priority. 7

Severity. INFO

Log Message. Node node_id: Mean loop Counter in doJob last 8192 times = count

Description.

Event Name. JobStatistic

Event Type. Statistic

Priority. 9

Severity. INFO

Log Message. Mean send size to Node = node_id last 4096 sends = bytes bytes

Description. This node is sending an average of bytes bytes per send to node node_id

Event Name. SendBytesStatistic

Event Type. Statistic

Priority. 9

Severity. INFO

Log Message. Mean receive size to Node = node_id last 4096 sends = bytes bytes

Description. This node is receiving an average of bytes of data each time it receives data from node node_id

Event Name. ReceiveBytesStatistic

Event Type. Statistic

Priority. 9

Severity. INFO

Log Message. Node node_id: Data usage is data_memory_percentage% (data_pages_used 32K pages of total data_pages_total) / Node node_id: Index usage is index_memory_percentage% (index_pages_used 8K pages of total index_pages_total)

Description. This report is generated when a DUMP 1000 command is issued in the cluster management client; for more information, see DUMP 1000, in MySQL Cluster Internals

Event Name. MemoryUsage

Event Type. Statistic

Priority. 5

Severity. INFO

Log Message. Node node1_id: Transporter to node node2_id reported error error_code: error_message

Description. A transporter error occurred while communicating with node node2_id; for a listing of transporter error codes and messages, see NDB Transporter Errors, in MySQL Cluster Internals

Event Name. TransporterError

Event Type. Error

Priority. 2

Severity. ERROR

Log Message. Node node1_id: Transporter to node node2_id reported error error_code: error_message

Description. A warning of a potential transporter problem while communicating with node node2_id; for a listing of transporter error codes and messages, see NDB Transporter Errors, for more information

Event Name. TransporterWarning

Event Type. Error

Priority. 8

Severity. WARNING

Log Message. Node node1_id: Node node2_id missed heartbeat heartbeat_id

Description. This node missed a heartbeat from node node2_id

Event Name. MissedHeartbeat

Event Type. Error

Priority. 8

Severity. WARNING

Log Message. Node node1_id: Node node2_id declared dead due to missed heartbeat

Description. This node has missed at least 3 heartbeats from node node2_id, and so has declared that node “dead”

Event Name. DeadDueToHeartbeat

Event Type. Error

Priority. 8

Severity. ALERT

Log Message. Node node1_id: Node Sent Heartbeat to node = node2_id

Description. This node has sent a heartbeat to node node2_id

Event Name. SentHeartbeat

Event Type. Info

Priority. 12

Severity. INFO

Log Message. Node node_id: Event buffer status: used=bytes_used (percent_used%) alloc=bytes_allocated (percent_available%) max=bytes_available apply_gci=latest_restorable_GCI latest_gci=latest_GCI

Description. This report is seen during heavy event buffer usage, for example, when many updates are being applied in a relatively short period of time; the report shows the number of bytes and the percentage of event buffer memory used, the bytes allocated and percentage still available, and the latest and latest restorable global checkpoints

Event Name. EventBufferStatus

Event Type. Info

Priority. 7

Severity. INFO

Log Message. Node node_id: Entering single user mode, Node node_id: Entered single user mode Node API_node_id has exclusive access, Node node_id: Entering single user mode

Description. These reports are written to the cluster log when entering and exiting single user mode; API_node_id is the node ID of the API or SQL having exclusive access to the cluster (fro mroe information, see Section 7.6, “MySQL Cluster Single User Mode”); the message Unknown single user report API_node_id indicates an error has taken place and should never be seen in normal operation

Event Name. SingleUser

Event Type. Info

Priority. 7

Severity. INFO

Log Message. Node node_id: Backup backup_id started from node mgm_node_id

Description. A backup has been started using the management node having mgm_node_id; this message is also displayed in the cluster management client when the START BACKUP command is issued; for more information, see Section 7.3.2, “Using The MySQL Cluster Management Client to Create a Backup”

Event Name. BackupStarted

Event Type. Backup

Priority. 7

Severity. INFO

Log Message. Node node_id: Backup backup_id started from node mgm_node_id completed. StartGCP: start_gcp StopGCP: stop_gcp #Records: records #LogRecords: log_records Data: data_bytes bytes Log: log_bytes bytes

Description. The backup having the ID backup_id has been completed; for more information, see Section 7.3.2, “Using The MySQL Cluster Management Client to Create a Backup”

Event Name. BackupCompleted

Event Type. Backup

Priority. 7

Severity. INFO

Log Message. Node node_id: Backup request from mgm_node_id failed to start. Error: error_code

Description. The backup failed to start; for error codes, see MGM API Errors

Event Name. BackupFailedToStart

Event Type. Backup

Priority. 7

Severity. ALERT

Log Message. Node node_id: Backup backup_id started from mgm_node_id has been aborted. Error: error_code

Description. The backup was terminated after starting, possibly due to user intervention

Event Name. BackupAborted

Event Type. Backup

Priority. 7

Severity. ALERT

7.5.2. MySQL Cluster — `NDB` Transporter Errors

This section lists error codes, names, and messages that are written to the cluster log in the event of transporter errors.

Error Code	Error Name	Error Text
0x00	TE_NO_ERROR	No error
0x01	TE_ERROR_CLOSING_SOCKET	Error found during closing of socket
0x02	TE_ERROR_IN_SELECT_BEFORE_ACCEPT	Error found before accept. The transporter will retry
0x03	TE_INVALID_MESSAGE_LENGTH	Error found in message (invalid message length)
0x04	TE_INVALID_CHECKSUM	Error found in message (checksum)
0x05	TE_COULD_NOT_CREATE_SOCKET	Error found while creating socket(can't create socket)
0x06	TE_COULD_NOT_BIND_SOCKET	Error found while binding server socket
0x07	TE_LISTEN_FAILED	Error found while listening to server socket
0x08	TE_ACCEPT_RETURN_ERROR	Error found during accept(accept return error)
0x0b	TE_SHM_DISCONNECT	The remote node has disconnected
0x0c	TE_SHM_IPC_STAT	Unable to check shm segment
0x0d	TE_SHM_UNABLE_TO_CREATE_SEGMENT	Unable to create shm segment
0x0e	TE_SHM_UNABLE_TO_ATTACH_SEGMENT	Unable to attach shm segment
0x0f	TE_SHM_UNABLE_TO_REMOVE_SEGMENT	Unable to remove shm segment
0x10	TE_TOO_SMALL_SIGID	Sig ID too small
0x11	TE_TOO_LARGE_SIGID	Sig ID too large
0x12	TE_WAIT_STACK_FULL	Wait stack was full
0x13	TE_RECEIVE_BUFFER_FULL	Receive buffer was full
0x14	TE_SIGNAL_LOST_SEND_BUFFER_FULL	Send buffer was full,and trying to force send fails
0x15	TE_SIGNAL_LOST	Send failed for unknown reason(signal lost)
0x16	TE_SEND_BUFFER_FULL	The send buffer was full, but sleeping for a while solved
0x0017	TE_SCI_LINK_ERROR	There is no link from this node to the switch
0x18	TE_SCI_UNABLE_TO_START_SEQUENCE	Could not start a sequence, because system resources are exumed or no sequence has been created
0x19	TE_SCI_UNABLE_TO_REMOVE_SEQUENCE	Could not remove a sequence
0x1a	TE_SCI_UNABLE_TO_CREATE_SEQUENCE	Could not create a sequence, because system resources are exempted. Must reboot
0x1b	TE_SCI_UNRECOVERABLE_DATA_TFX_ERROR	Tried to send data on redundant link but failed
0x1c	TE_SCI_CANNOT_INIT_LOCALSEGMENT	Cannot initialize local segment
0x1d	TE_SCI_CANNOT_MAP_REMOTESEGMENT	Cannot map remote segment
0x1e	TE_SCI_UNABLE_TO_UNMAP_SEGMENT	Cannot free the resources used by this segment (step 1)
0x1f	TE_SCI_UNABLE_TO_REMOVE_SEGMENT	Cannot free the resources used by this segment (step 2)
0x20	TE_SCI_UNABLE_TO_DISCONNECT_SEGMENT	Cannot disconnect from a remote segment
0x21	TE_SHM_IPC_PERMANENT	Shm ipc Permanent error
0x22	TE_SCI_UNABLE_TO_CLOSE_CHANNEL	Unable to close the sci channel and the resources allocated

7.6. MySQL Cluster Single User Mode

Single user mode allows the database administrator to restrict access to the database system to a single API node, such as a MySQL server (SQL node) or an instance of ndb_restore. When entering single user mode, connections to all other API nodes are closed gracefully and all running transactions are aborted. No new transactions are permitted to start.

Once the cluster has entered single user mode, only the designated API node is granted access to the database.

You can use the ALL STATUS command to see when the cluster has entered single user mode.

Example:

ndb_mgm> ENTER SINGLE USER MODE 5

After this command has executed and the cluster has entered single user mode, the API node whose node ID is 5 becomes the cluster's only permitted user.

The node specified in the preceding command must be an API node; attempting to specify any other type of node will be rejected.

Note

When the preceding command is invoked, all transactions running on the designated node are aborted, the connection is closed, and the server must be restarted.

The command EXIT SINGLE USER MODE changes the state of the cluster's data nodes from single user mode to normal mode. API nodes — such as MySQL Servers — waiting for a connection (that is, waiting for the cluster to become ready and available), are again permitted to connect. The API node denoted as the single-user node continues to run (if still connected) during and after the state change.

Example:

ndb_mgm> EXIT SINGLE USER MODE

There are two recommended ways to handle a node failure when running in single user mode:

Method 1:
1. Finish all single user mode transactions
2. Issue the EXIT SINGLE USER MODE command
3. Restart the cluster's data nodes
Method 2:
Restart database nodes prior to entering single user mode.

7.7. Quick Reference: MySQL Cluster SQL Statements

This section discusses several SQL statements that can prove useful in managing and monitoring a MySQL server that is connected to a MySQL Cluster, and in some cases provide information about the cluster itself.

SHOW ENGINE NDB STATUS, SHOW ENGINE NDBCLUSTER STATUS
The output of this statement contains information about the server's connection to the cluster, creation and usage of MySQL Cluster objects, and binary logging for MySQL Cluster replication.
See SHOW ENGINE Syntax, for a usage example and more detailed information.
SHOW ENGINES
This statement can be used to determine whether or not clustering support is enabled in the MySQL server, and if so, whether it is active.
See SHOW ENGINES Syntax, for more detailed information.
Note
In MySQL 5.1, this statement no longer supports a LIKE clause. However, you can use LIKE to filter queries against the INFORMATION_SCHEMA.ENGINES, as discussed in the next item.
SELECT * FROM INFORMATION_SCHEMA.ENGINES [WHERE ENGINE LIKE 'NDB%']
This is the equivalent of SHOW ENGINES, but uses the ENGINES table of the INFORMATION_SCHEMA database (available beginning with MySQL 5.1.5). Unlike the case with the SHOW ENGINES statement, it is possible to filter the results using a LIKE clause, and to select specific columns to obtain information that may be of use in scripts. For example, the following query shows whether the server was built with NDB support and, if so, whether it is enabled:
```
mysql> SELECT SUPPORT FROM INFORMATION_SCHEMA.ENGINES
    ->   WHERE ENGINE LIKE 'NDB%';
+---------+
| support |
+---------+
| ENABLED |
+---------+
```
See The INFORMATION_SCHEMA ENGINES Table, for more information.

SHOW VARIABLES LIKE 'NDB%'

This statement provides a list of most server system variables relating to the NDB storage engine, and their values, as shown here:

mysql> SHOW VARIABLES LIKE 'NDB%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| ndb_autoincrement_prefetch_sz       | 32    |
| ndb_cache_check_time                | 0     |
| ndb_extra_logging                   | 0     |
| ndb_force_send                      | ON    |
| ndb_index_stat_cache_entries        | 32    |
| ndb_index_stat_enable               | OFF   |
| ndb_index_stat_update_freq          | 20    |
| ndb_report_thresh_binlog_epoch_slip | 3     |
| ndb_report_thresh_binlog_mem_usage  | 10    |
| ndb_use_copying_alter_table         | OFF   |
| ndb_use_exact_count                 | ON    |
| ndb_use_transactions                | ON    |
+-------------------------------------+-------+

See Server System Variables, for more information.

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'NDB%';

This statement is the equivalent of the SHOW command described in the previous item, and provides almost identical output, as shown here:

mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
    ->   WHERE VARIABLE_NAME LIKE 'NDB%';
+-------------------------------------+----------------+
| VARIABLE_NAME                       | VARIABLE_VALUE |
+-------------------------------------+----------------+
| NDB_AUTOINCREMENT_PREFETCH_SZ       | 32             |
| NDB_CACHE_CHECK_TIME                | 0              |
| NDB_EXTRA_LOGGING                   | 0              |
| NDB_FORCE_SEND                      | ON             |
| NDB_INDEX_STAT_CACHE_ENTRIES        | 32             |
| NDB_INDEX_STAT_ENABLE               | OFF            |
| NDB_INDEX_STAT_UPDATE_FREQ          | 20             |
| NDB_REPORT_THRESH_BINLOG_EPOCH_SLIP | 3              |
| NDB_REPORT_THRESH_BINLOG_MEM_USAGE  | 10             |
| NDB_USE_COPYING_ALTER_TABLE         | OFF            |
| NDB_USE_EXACT_COUNT                 | ON             |
| NDB_USE_TRANSACTIONS                | ON             |
+-------------------------------------+----------------+

Unlike the case with the SHOW command, it is possible to select individual columns. For example:

mysql> SELECT VARIABLE_VALUE 
    ->   FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
    ->   WHERE VARIABLE_NAME = 'ndb_force_send';
+----------------+
| VARIABLE_VALUE |
+----------------+
| ON             |
+----------------+

See The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables, and Server System Variables, for more information.

SHOW STATUS LIKE 'NDB%'

This statement shows at a glance whether or not the MySQL server is acting as a cluster SQL node, and if so, it provides the MySQL server's cluster node ID, the host name and port for the cluster management server to which it is connected, and the number of data nodes in the cluster, as shown here:

mysql> SHOW STATUS LIKE 'NDB%';
+--------------------------+---------------+
| Variable_name            | Value         |
+--------------------------+---------------+
| Ndb_cluster_node_id      | 10            |
| Ndb_config_from_host     | 192.168.0.103 |
| Ndb_config_from_port     | 1186          |
| Ndb_number_of_data_nodes | 4             |
+--------------------------+---------------+

If the MySQL server was built with clustering support, but it is not connected to a cluster, all rows in the output of this statement contain a zero or an empty string:

mysql> SHOW STATUS LIKE 'NDB%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Ndb_cluster_node_id      | 0     |
| Ndb_config_from_host     |       |
| Ndb_config_from_port     | 0     |
| Ndb_number_of_data_nodes | 0     |
+--------------------------+-------+

7.8. Adding MySQL Cluster Data Nodes Online

7.8.1. Adding MySQL Cluster Data Nodes Online: General Issues
7.8.2. Adding MySQL Cluster Data Nodes Online: Basic procedure
7.8.3. Adding MySQL Cluster Data Nodes Online: Detailed Example

This section describes how to add MySQL Cluster data nodes “online” — that is, without needing to shut down the cluster completely and restart it as part of the process. This capability is available in MySQL Cluster NDB 7.0 (beginning with MySQL Cluster NDB 6.4.0) and later MySQL Cluster release series.

Important

Currently, you must add new data nodes to a MySQL Cluster as part of a new node group. In addition, it is not possible to change the number of replicas (or the number of nodes per node group) online.

7.8.1. Adding MySQL Cluster Data Nodes Online: General Issues

This section provides general information about the behavior of and current limitations in adding MySQL Cluster nodes online.

Redistribution of Data. The ability to add new nodes online includes a means to reorganize NDBCLUSTER table data and indexes so that they are distributed across all data nodes, including the new ones. Table reorganization of both in-memory and Disk Data tables is supported. This redistribution does not currently include unique indexes (only ordered indexes are redistributed) or BLOB table data, but we are working to add redistibution of these in the near future. The redistribution for NDBCLUSTER tables already existing before the new data nodes were added is not automatic, but can be accomplished using simple SQL statements in mysql or another MySQL client application. However, all data and indexes added to tables created after a new node group has been added are distributed automatically among all cluster data nodes, including those added as part of the new node group.

Partial starts. It is possible to add a new node group without all of the new data nodes being started. It is also possible to add a new node group to a degraded cluster — that is, a cluster that is only partially started, or where one or more data nodes are not running. In the latter case, the cluster must have enough nodes running to be viable before the new node group can be added.

Effects on ongoing operations. Normal DML operations using MySQL Cluster data are not prevented by the creation or addition of a new node group, or by table reorganization. However, it is not possible to perform DDL concurrently with table reorganization — that is, no other DDL statements can be issued while an ALTER TABLE ... REORGANIZE PARTITION statement is executing. In addition, during the execution of ALTER TABLE ... REORGANIZE PARTITION (or the execution of any other DDL statement), it is not possible to restart cluster data nodes.

Failure handling. Failures of data nodes during node group creation and table reorganization are handled as hown in the following table:

Failure occurs during:	Failure occurs in:
Failure occurs during:	“Old” data nodes	“New” data nodes	System
Node group creation	If a node other than the master fails: The creation of the node group is always rolled forward. If the master fails: If the internal commit point has been reached: The creation of the node group is rolled forward. If the internal commit point has not yet been reached. The creation of the node group is rolled back	If a node other than the master fails: The creation of the node group is always rolled forward. If the master fails: If the internal commit point has been reached: The creation of the node group is rolled forward. If the internal commit point has not yet been reached. The creation of the node group is rolled back	If the execution of `CREATE NODEGROUP` has reached the internal commit point: When restarted, the cluster includes the new node group. Otherwise it without. If the execution of `CREATE NODEGROUP` has not yet reached the internal commit point: When restarted, the cluster does not include the new node group.
Table reorganization	If a node other than the master fails: The table reorganization is always rolled forward. If the master fails: If the internal commit point has been reached: The table reorganization is rolled forward. If the internal commit point has not yet been reached. The table reorganization is rolled back.	If a node other than the master fails: The table reorganization is always rolled forward. If the master fails: If the internal commit point has been reached: The table reorganization is rolled forward. If the internal commit point has not yet been reached. The table reorganization is rolled back.	If the execution of an `ALTER ONLINE TABLE table REORGANIZE PARTITION` statement has reached the internal commit point: When the cluster is restarted, the data and indexes belonging to `table` are distributed using the “new” data nodes. If the execution of an `ALTER ONLINE TABLE table REORGANIZE PARTITION` statement has not yet reached the internal commit point: When the cluster is restarted, the data and indexes belonging to `table` are distributed using only the “old” data nodes.

Dropping node groups. The ndb_mgm client supports a DROP NODEGROUP command, but it is possible to drop a node group only when no data nodes in the node group contain any data. Since there is currently no way to “empty” a specific data node or node group, this command works only the following two cases:

After issuing CREATE NODEGROUP in the ndb_mgm client, but before issuing any ALTER ONLINE TABLE ... REORGANIZE PARTITION statements in the mysql client.
After dropping all NDBCLUSTER tables using DROP TABLE.
TRUNCATE does not work for this purpose because the data nodes continue to store the table definitions.

7.8.2. Adding MySQL Cluster Data Nodes Online: Basic procedure

In this section, we list the basic steps required to add new data nodes to a MySQL Cluster. For a detailed example, see Section 7.8.3, “Adding MySQL Cluster Data Nodes Online: Detailed Example”.

Note

Beginning with MySQL Cluster NDB 7.0.4, this procedure applies whether you are using ndbd or ndbmtd binaries for the data node processes. Previously, this did not work with multi-threaded data nodes. (Bug#43108)

Assuming that you already have a running MySQL Cluster, adding data nodes online requires the following steps:

Edit the cluster configuration config.ini file, adding new [ndbd] sections corresponding to the nodes to be added. In the case where the cluster uses multiple management servers, these changes need to be made to all config.ini files used by the management servers.
Perform a rolling restart of all MySQL Cluster management servers.
Important
All management servers must be restarted with the --reload or --initial option to force the reading of the new configuration.
Perform a rolling restart of all existing MySQL Cluster data nodes.
Note
It is not necessary to use --initial when restarting the existing data nodes.
Perform a rolling restart of any SQL or API nodes connected to the MySQL Cluster.
Perform an initial start of the new data nodes.
Note
The new data nodes may be started in any order, and can also be started concurrently, as long as they are started after the rolling restarts of all existing nodes have been completed and before proceeding to the next step.
Execute one or more CREATE NODEGROUP commands in the MySQL Cluster management client to create the new node group or node groups to which the new data nodes will belong.
Redistribute the cluster's data among all data nodes (including the new ones) by issuing an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement in the mysql client for each NDBCLUSTER table.
Note
This needs to be done only for tables already existing at the time the new node group is added. Data in tables created after the new node group is added is distributed automatically; however, data added to any given table tbl that existed before the new nodes were added is not distributed using the new nodes until that table has been reorganized using ALTER ONLINE TABLE tbl REORGANIZE PARTITION.
Reclaim the space freed on the “old” nodes by issuing, for each NDBCLUSTER table, an OPTIMIZE TABLE statement in the mysql client.

7.8.3. Adding MySQL Cluster Data Nodes Online: Detailed Example

In this section we provide a detailed example illustrating how to add new MySQL Cluster data nodes online, starting with a MySQL Cluster having 2 data nodes in a single node group and concluding with a cluster having 4 data nodes in 2 node groups.

Starting configuration. For purposes of illustration, we assume a minimal configuration, and that the cluster uses a config.ini file containing only the following information:

[ndbd default]
DataMemory = 100M
IndexMemory = 100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster
[ndbd]
Id = 1
HostName = 192.168.0.1
[ndbd]
Id = 2
HostName = 192.168.0.2
[mgm]
HostName = 192.168.0.10
Id = 10
[api]
Id=20
HostName = 192.168.0.20
[api]
Id=21
HostName = 192.168.0.21

We also assume that you have already started the cluster using the appropriate command line or my.cnf options, and that running SHOW in the management client produces output similar to what is shown here:

-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.0.1  (5.1.34-ndb-7.0.7, Nodegroup: 0, Master)
id=2    @192.168.0.2  (5.1.34-ndb-7.0.7, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=10   @192.168.0.10  (5.1.34-ndb-7.0.7)
[mysqld(API)]   2 node(s)
id=20   @192.168.0.20  (5.1.34-ndb-7.0.7)
id=21   @192.168.0.21  (5.1.34-ndb-7.0.7)

Finally, we assume that the cluster contains a single NDBCLUSTER table created as shown here:

USE n;
CREATE TABLE ips (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    type CHAR(4) NOT NULL,
    ip_address varchar(15) NOT NULL,
    addresses BIGINT UNSIGNED DEFAULT NULL,
    date BIGINT UNSIGNED DEFAULT NULL
)   ENGINE NDBCLUSTER;

The memory usage and related information shown later in this section was generated after inserting approximately 50000 rows into this table.

Note

In this example, we show the single-threaded ndbd being used for the data node processes. However — beginning with MySQL Cluster NDB 7.0.4 (Bug#43108) — you can also apply this example if you are using the multi-threaded ndbmtd by substituting ndbmtd for ndbd wherever it appears in the steps that follow.

Step 1: Update configuration file. Open the cluster global configuration file in a text editor and add [ndbd] sections corresponding to the 2 new data nodes. (We give these data nodes IDs 3 and 4, and assume that they are to be run on host machines at addresses 192.168.0.3 and 192.168.0.4, respectively.) After you have added the new sections, the contents of the config.ini file should look like what is shown here, where the additions to the file are shown in bold type:

[ndbd default]
DataMemory = 100M
IndexMemory = 100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster
[ndbd]
Id = 1
HostName = 192.168.0.1
[ndbd]
Id = 2
HostName = 192.168.0.2
[ndbd]
Id = 3
HostName = 192.168.0.3
[ndbd]
Id = 4
HostName = 192.168.0.4
[mgm]
HostName = 192.168.0.10
Id = 10
[api]
Id=20
HostName = 192.168.0.20
[api]
Id=21
HostName = 192.168.0.21

Once you have made the necessary changes, save the file.

Step 2: Restart the management server. Restarting the cluster management server requires that you issue separate commands to stop the management server and then to start it again, as follows:

Stop the management server using the management client STOP command, as shown here:

ndb_mgm> 10 STOP
Node 10 has shut down.
Disconnecting to allow Management Server to shutdown
shell>

Because shutting down the management server causes the management client to terminate, you must start the management server from the system shell. For simplicity, we assume that config.ini is in the same directory as the management server binary, but in practice, you must supply the correct path to the configuration file. You must also supply the --reload or --initial option so that the management server reads the new configuration from the file rather than its configuration cache. If your shell's current directory is also the same as the directory where the management server binary is located, then you can invoke the management server as shown here:
```
shell> ndb_mgmd -f config.ini --reload
2008-12-08 17:29:23 [MgmSrvr] INFO     -- NDB Cluster Management Server. 5.1.34-ndb-7.0.7
2008-12-08 17:29:23 [MgmSrvr] INFO     -- Reading cluster configuration from 'config.ini'
```

If you check the output of SHOW in the management client after restarting the ndb_mgm process, you should now see something like this:

-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.0.1  (5.1.34-ndb-7.0.7, Nodegroup: 0, Master)
id=2    @192.168.0.2  (5.1.34-ndb-7.0.7, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)
[ndb_mgmd(MGM)] 1 node(s)
id=10   @192.168.0.10  (5.1.34-ndb-7.0.7)
[mysqld(API)]   2 node(s)
id=20   @192.168.0.20  (5.1.34-ndb-7.0.7)
id=21   @192.168.0.21  (5.1.34-ndb-7.0.7)

Step 3: Perform a rolling restart of the existing data nodes. This step can be accomplished entirely within the cluster management client using the RESTART command, as shown here:

ndb_mgm> 1 RESTART
Node 1: Node shutdown initiated
Node 1: Node shutdown completed, restarting, no start.
Node 1 is being restarted
ndb_mgm> Node 1: Start initiated (version 7.0.7)
Node 1: Started (version 7.0.7)
ndb_mgm> 2 RESTART
Node 2: Node shutdown initiated
Node 2: Node shutdown completed, restarting, no start.
Node 2 is being restarted
ndb_mgm> Node 2: Start initiated (version 7.0.7)
ndb_mgm> Node 2: Started (version 7.0.7)

Important

After issuing each X RESTART command, wait until the management client reports Node X: Started (version ...) before proceeding any further.

Step 4: Perform a rolling restart of all cluster API nodes. Shut down and restart each MySQL server acting as an SQL node in the cluster using mysqladmin shutdown followed by mysqld_safe (or another startup script). This should be similar to what is shown here, where password is the MySQL root password for a given MySQL server instance:

shell> mysqladmin -uroot -ppassword shutdown
081208 20:19:56 mysqld_safe mysqld from pid file
/usr/local/mysql/var/tonfisk.pid ended
shell> mysqld_safe --ndbcluster --ndb-connectstring=192.168.0.10 &
081208 20:20:06 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.
081208 20:20:06 mysqld_safe Starting mysqld daemon with databases
from /usr/local/mysql/var

Of course, the exact input and output depend on how and where MySQL is installed on the system, as well as which options you choose to start it (and whether or not some or all of these options are specified in a my.cnf file).

Step 5: Perform an initial start of the new data nodes. From a system shell on each of the hosts for the new data nodes, start the data nodes as shown here, using the --initial option:

shell> ndbd -c 192.168.0.10 --initial

Note

Unlike the case with restarting the existing data nodes, you can start the new data nodes concurrently; you do not need to wait for one to finish starting before starting the other.

Wait until both of the new data nodes have started before proceeding with the next step. Once the new data nodes have started, you can see in the output of the management client SHOW command that they do not yet belong to any node group (as indicated with bold type here):

ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.0.1  (5.1.34-ndb-7.0.7, Nodegroup: 0, Master)
id=2    @192.168.0.2  (5.1.34-ndb-7.0.7, Nodegroup: 0)
id=3    @192.168.0.3  (5.1.34-ndb-7.0.7, no nodegroup)
id=4    @192.168.0.4  (5.1.34-ndb-7.0.7, no nodegroup)
[ndb_mgmd(MGM)] 1 node(s)
id=10   @192.168.0.10  (5.1.34-ndb-7.0.7)
[mysqld(API)]   2 node(s)
id=20   @192.168.0.20  (5.1.34-ndb-7.0.7)
id=21   @192.168.0.21  (5.1.34-ndb-7.0.7)

Step 6: Create a new node group. You can do this by issuing a CREATE NODEGROUP command in the cluster management client. This command takes as its argument a comma-separated list of the node IDs of the data nodes to be included in the new node group, as shown here:

ndb_mgm> CREATE NODEGROUP 3,4
Nodegroup 1 created

By issuing SHOW again, you can verify that data nodes 3 and 4 have joined the new node group (again indicated in bold type):

ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.0.1  (5.1.34-ndb-7.0.7, Nodegroup: 0, Master)
id=2    @192.168.0.2  (5.1.34-ndb-7.0.7, Nodegroup: 0)
id=3    @192.168.0.3  (5.1.34-ndb-7.0.7, Nodegroup: 1)
id=4    @192.168.0.4  (5.1.34-ndb-7.0.7, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=10   @192.168.0.10  (5.1.34-ndb-7.0.7)
[mysqld(API)]   2 node(s)
id=20   @192.168.0.20  (5.1.34-ndb-7.0.7)
id=21   @192.168.0.21  (5.1.34-ndb-7.0.7)

Step 7: Redistribute cluster data. When a node group is created, existing data and indexes are not automatically distributed to the new node group's data nodes, as you can see by issuing the appropriate REPORT command in the management client:

ndb_mgm> ALL REPORT MEMORY
Node 1: Data usage is 5%(177 32K pages of total 3200)
Node 1: Index usage is 0%(108 8K pages of total 12832)
Node 2: Data usage is 5%(177 32K pages of total 3200)
Node 2: Index usage is 0%(108 8K pages of total 12832)
Node 3: Data usage is 0%(0 32K pages of total 3200)
Node 3: Index usage is 0%(0 8K pages of total 12832)
Node 4: Data usage is 0%(0 32K pages of total 3200)
Node 4: Index usage is 0%(0 8K pages of total 12832)

By using ndb_desc with the -p option, which causes the output to include partitioning information, you can see that the table still uses only 2 partitions (in the Per partition info section of the output, shown here in bold text):

shell> ndb_desc -c 192.168.0.10 -d n ips -p
-- ips --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 340
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY
-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex
-- Per partition info --
Partition   Row count   Commit count  Frag fixed memory   Frag varsized memory
0           26086       26086         1572864             557056
1           26329       26329         1605632             557056
NDBT_ProgramExit: 0 - OK

You can cause the data to be redistributed among all of the data nodes by performing, for each NDBCLUSTER table, an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement in the mysql client. After issuing the statement ALTER ONLINE TABLE ips REORGANIZE PARTITION, you can see using ndb_desc that the data for this table is now stored using 4 partitions, as shown here (with the relevant portions of the output in bold type):

shell> ndb_desc -c 192.168.0.10 -d n ips -p
-- ips --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 341
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY
-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex
-- Per partition info --
Partition   Row count   Commit count  Frag fixed memory   Frag varsized memory
0           12981       52296         1572864             557056
1           13236       52515         1605632             557056
2           13105       13105         819200              294912
3           13093       13093         819200              294912
NDBT_ProgramExit: 0 - OK

Note

Normally, ALTER [ONLINE] TABLE table_name REORGANIZE PARTITION is used with a list of partition identifiers and a set of partition definitions to create a new partitioning scheme for a table that has already been explicitly partitioned. Its use here to redistribute data onto a new MySQL Cluster node group is an exception in this regard; when used in this way, only the name of the table is used following the TABLE keyword, and no other keywords or identifiers follow REORGANIZE PARTITION.

Prior to MySQL Cluster NDB 6.4.3, ALTER ONLINE TABLE ... REORGANIZE PARTITION with no partition_names INTO (partition_definitions) option did not work correctly with Disk Data tables or with in-memory NDBCLUSTER tables having one or more disk-based columns. (Bug#42549)

For more information, see ALTER TABLE Syntax.

Also, for each table, the ALTER ONLINE TABLE statement should be followed by an OPTIMIZE TABLE to reclaim wasted space. You can obtain a list of all NDBCLUSTER tables using the following query against the INFORMATION_SCHEMA.TABLES table:

SELECT TABLE_SCHEMA, TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE ENGINE = 'NDBCLUSTER';

Note

The INFORMATION_SCHEMA.TABLES.ENGINE value for a MySQL Cluster table is always NDBCLUSTER, regardless of whether the CREATE TABLE statement used to create the table (or ALTER TABLE statement used to convert an existing table from a different storage engine) used NDB or NDBCLUSTER in its ENGINE option.

You can see after performing these statements in the output of ALL REPORT MEMORY that the data and indexes are now redistributed between all cluster data nodes, as shown here:

ndb_mgm> ALL REPORT MEMORY
Node 1: Data usage is 5%(176 32K pages of total 3200)
Node 1: Index usage is 0%(76 8K pages of total 12832)
Node 2: Data usage is 5%(176 32K pages of total 3200)
Node 2: Index usage is 0%(76 8K pages of total 12832)
Node 3: Data usage is 2%(80 32K pages of total 3200)
Node 3: Index usage is 0%(51 8K pages of total 12832)
Node 4: Data usage is 2%(80 32K pages of total 3200)
Node 4: Index usage is 0%(50 8K pages of total 12832)

Note

Since only one DDL operation on NDBCLUSTER tables can be executed at a time, you must wait for each ALTER ONLINE TABLE ... REORGANIZE PARTITION statement to finish before issuing the next one.

It is not necessary to issue ALTER ONLINE TABLE ... REORGANIZE PARTITION statements for NDBCLUSTER tables created after the new data nodes have been added; data added to such tables is distributed among all data nodes automatically. However, in NDBCLUSTER tables that existed prior to the addition of the new nodes, neither existing nor new data is distributed using the new nodes until these tables have been reorganized using ALTER ONLINE TABLE ... REORGANIZE PARTITION.

Alternative procedure, without rolling restart. It is possible to avoid the need for a rolling restart by configuring the extra data nodes, but not starting them, when first starting the cluster. This can be accomplished by using the NodeGroup data node configuration parameter in the config.ini file, as shown here (note the section with bold text). We assume, as before, that you wish to start with two data nodes — nodes 1 and 2 — in one node group and later to expand the cluster to four data nodes, by adding a second node group consisting of nodes 3 and 4:

[ndbd default]
DataMemory = 100M
IndexMemory = 100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster
[ndbd]
Id = 1
HostName = 192.168.0.1
[ndbd]
Id = 2
HostName = 192.168.0.2
[ndbd]
Id = 3
HostName = 192.168.0.3
NodeGroup = 65535
[ndbd]
Id = 4
HostName = 192.168.0.4
NodeGroup = 65535
[mgm]
HostName = 192.168.0.10
Id = 10
[api]
Id=20
HostName = 192.168.0.20
[api]
Id=21
HostName = 192.168.0.21

In this case, you must perform the initial start of the cluster using the --nowait option with ndbd (or ndbmtd in MySQL Cluster NDB 7.0.4 and later) for each of the data nodes that you wish to have online immediately, so that the cluster does not wait for the remaining nodes to start:

shell> ndbd -c 192.168.0.10 --initial --nowait=3,4

When you are ready to add the second nodegroup, you need only perform the following additional steps:

Start data nodes 3 and 4, invoking the data node process once for each new node:
```
shell> ndbd -c 192.168.0.10 --initial
```
Issue the appropriate CREATE NODEGROUP command in the management client:
```
ndb_mgm> CREATE NODEGROUP 3,4
```
In the mysql client, issue ALTER ONLINE TABLE ... REORGANIZE PARTITION and OPTIMIZE TABLE statements for each existing NDBCLUSTER table. (As noted elsewhere in this section, existing MySQL Cluster tables cannot use the new nodes for data distribution until this has been done.)

Prev		Next
Chapter 6. MySQL Cluster Programs	Home	Chapter 8. MySQL Cluster Security Issues

Bookmark & Share

Document Actions

Send this

Sections

Personal tools

Chapter 7. Management of MySQL Cluster