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

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:

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

Before starting a backup, make sure that the cluster is properly configured for performing one. (See Section 17.5.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} | NOWAITsnapshot_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.

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:

WAIT COMPLETED is the default.

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.

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 SNAPSHOTSTARTSTART BACKUP SNAPSHOTSTART WAIT STARTEDSTART BACKUP 4 WAIT COMPLETED SNAPSHOTSTARTSTART BACKUP SNAPSHOTEND WAIT COMPLETEDSTART BACKUP 4 NOWAIT SNAPSHOTSTART

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

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. 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:

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"

Five configuration parameters are essential for backup:

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

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.

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.

The following management commands are related to the cluster log:

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.

The STATISTICS category can provide a great deal of useful data. See Section 17.5.6.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.

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.

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.

CHECKPOINT Events

The logging messages shown here are associated with checkpoints.

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.

NODERESTART Events

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

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.

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.

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.

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:

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

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.

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:

Process scheduler statistics include the following:

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

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

The following table lists the most common NDB cluster log messages. For information about the cluster log, log events, and event types, see Section 17.5.6, "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.

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

The arbitrator_validity_detail table shows the view that each data node in the cluster of the arbitrator. It is a subset of the membership table.

The following table provides information about the columns in the arbitrator_validity_detail table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The node ID is the same as that reported by ndb_mgm -e "SHOW".

All nodes should show the same arbitrator and arb_ticket values as well as the same arb_state value. Possible arb_state values are ARBIT_NULL, ARBIT_INIT, ARBIT_FIND, ARBIT_PREP1, ARBIT_PREP2, ARBIT_START, ARBIT_RUN, ARBIT_CHOOSE, ARBIT_CRASH, and UNKNOWN.

arb_connected shows whether the current node is connected to the arbitrator.

Like the membership and arbitrator_validity_summary tables, this table was added in MySQL Cluster NDB 7.2.10.

The arbitrator_validity_summary table provides a composite view of the arbitrator with regard to the cluster's data nodes.

The following table provides information about the columns in the arbitrator_validity_detail table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

In normal operations, this table should have only 1 row for any appreciable length of time. If it has more than 1 row for longer than a few moments, then either not all nodes are connected to the arbitrator, or all nodes are connected, but do not agree on the same arbitrator.

The arbitrator column shows the arbitrator's node ID.

arb_ticket is the internal identifier used by this arbitrator.

arb_connected shows whether this node is connected to the cluster as an arbitrator.

Like the membership and arbitrator_validity_detail tables, this table was added in MySQL Cluster NDB 7.2.10.

The blocks table is a static table which simply contains the names and internal IDs of all NDB kernel blocks (see NDB Kernel Blocks). It is for use by the other ndbinfo tables (most of which are actually views) in mapping block numbers to block names for producing human-readable output.

The following table provides information about the columns in the blocks table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

To obtain a list of all block names, simply execute SELECT block_name FROM ndbinfo.blocks. Although this is a static table, its content can vary between different MySQL Cluster releases.

The cluster_operations table provides a per-operation (stateful primary key op) view of all activity in the MySQL Cluster from the point of view of the local data management (LQH) blocks (see The DBLQH Block).

The following table provides information about the columns in the cluster_operations table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The transaction ID is a unique 64-bit number which can be obtained using the NDB API's getTransactionId() method. (Currently, the MySQL Server does not expose the NDB API transaction ID of an ongoing transaction.)

The operation_type column can take any one of the values READ, READ-SH, READ-EX, INSERT, UPDATE, DELETE, WRITE, UNLOCK, REFRESH, SCAN, SCAN-SH, SCAN-EX, or <unknown>.

The state column can have any one of the values ABORT_QUEUED, ABORT_STOPPED, COMMITTED, COMMIT_QUEUED, COMMIT_STOPPED, COPY_CLOSE_STOPPED, COPY_FIRST_STOPPED, COPY_STOPPED, COPY_TUPKEY, IDLE, LOG_ABORT_QUEUED, LOG_COMMIT_QUEUED, LOG_COMMIT_QUEUED_WAIT_SIGNAL, LOG_COMMIT_WRITTEN, LOG_COMMIT_WRITTEN_WAIT_SIGNAL, LOG_QUEUED, PREPARED, PREPARED_RECEIVED_COMMIT, SCAN_CHECK_STOPPED, SCAN_CLOSE_STOPPED, SCAN_FIRST_STOPPED, SCAN_RELEASE_STOPPED, SCAN_STATE_USED, SCAN_STOPPED, SCAN_TUPKEY, STOPPED, TC_NOT_CONNECTED, WAIT_ACC, WAIT_ACC_ABORT, WAIT_AI_AFTER_ABORT, WAIT_ATTR, WAIT_SCAN_AI, WAIT_TUP, WAIT_TUPKEYINFO, WAIT_TUP_COMMIT, or WAIT_TUP_TO_ABORT. (If the MySQL Server is running with ndbinfo_show_hidden enabled, you can view this list of states by selecting from the ndb$dblqh_tcconnect_state table, which is normally hidden.)

You can obtain the name of an NDB table from its table ID by checking the output of ndb_show_tables.

The fragid is the same as the partition number seen in the output of ndb_desc --extra-partition-info (short form -p).

In client_node_id and client_block_ref, client refers to a MySQL Cluster API or SQL node (that is, an NDB API client or a MySQL Server attached to the cluster).

This table was added in MySQL Cluster NDB 7.2.2.

The cluster_transactions table shows information about all ongoing transactions in a MySQL Cluster.

The following table provides information about the columns in the cluster_transactions table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The transaction ID is a unique 64-bit number which can be obtained using the NDB API's getTransactionId() method. (Currently, the MySQL Server does not expose the NDB API transaction ID of an ongoing transaction.)

The state column can have any one of the values CS_ABORTING, CS_COMMITTING, CS_COMMIT_SENT, CS_COMPLETE_SENT, CS_COMPLETING, CS_CONNECTED, CS_DISCONNECTED, CS_FAIL_ABORTED, CS_FAIL_ABORTING, CS_FAIL_COMMITTED, CS_FAIL_COMMITTING, CS_FAIL_COMPLETED, CS_FAIL_PREPARED, CS_PREPARE_TO_COMMIT, CS_RECEIVING, CS_REC_COMMITTING, CS_RESTART, CS_SEND_FIRE_TRIG_REQ, CS_STARTED, CS_START_COMMITTING, CS_START_SCAN, CS_WAIT_ABORT_CONF, CS_WAIT_COMMIT_CONF, CS_WAIT_COMPLETE_CONF, CS_WAIT_FIRE_TRIG_REQ. (If the MySQL Server is running with ndbinfo_show_hidden enabled, you can view this list of states by selecting from the ndb$dbtc_apiconnect_state table, which is normally hidden.)

In client_node_id and client_block_ref, client refers to a MySQL Cluster API or SQL node (that is, an NDB API client or a MySQL Server attached to the cluster).

This table was added in MySQL Cluster NDB 7.2.2.

The config_params table is a static table which provides the names and internal ID numbers of all MySQL Cluster configuration parameters.

The following table provides information about the columns in the config_params table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

Although this is a static table, its content can vary between MySQL Cluster installations, since supported parameters can vary due to differences between software releases, cluster hardware configurations, and other factors.

The counters table provides running totals of events such as reads and writes for specific kernel blocks and data nodes. Counts are kept from the most recent node start or restart; a node start or restart resets all counters on that node. Not all kernel blocks have all types of counters.

The following table provides information about the columns in the counters table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

Each counter is associated with a particular NDB kernel block. Prior to MySQL Cluster NDB 7.2.0, this was limited to either the DBLQH kernel block or the DBTC kernel block.

The OPERATIONS counter is associated with the DBLQH (local query handler) kernel block (see The DBLQH Block).

The ATTRINFO, TRANSACTIONS, COMMITS, READS, SIMPLE_READS, WRITES, ABORTS, TABLE_SCANS, and RANGE_SCANS counters are associated with the DBTC (transaction co-ordinator) kernel block (see The DBTC Block).

MySQL Cluster NDB 7.2.0, as part of its implementation of distributed pushed-down joins, adds the LOCAL_TABLE_SCANS_SENT, READS_RECEIVED, PRUNED_RANGE_SCANS_RECEIVED, RANGE_SCANS_RECEIVED, LOCAL_READS_SENT, CONST_PRUNED_RANGE_SCANS_RECEIVED, LOCAL_RANGE_SCANS_SENT, REMOTE_READS_SENT, REMOTE_RANGE_SCANS_SENT, READS_NOT_FOUND, SCAN_BATCHES_RETURNED, TABLE_SCANS_RECEIVED, and SCAN_ROWS_RETURNED counters. These counters are associated with the DBSPJ (select push-down join) kernel block (see The DBSPJ Block).

A number of counters increasing the visibility of transporter overload and send buffer sizing when troubleshooting such issues were added in MySQL Cluster NDB 7.2.10. (Bug #15935206) For each LQH instance, there is one instance of each counter in the following list:

The diskpagebuffer table provides statistics about disk page buffer usage by MySQL Cluster Disk Data tables.

The following table provides information about the columns in the diskpagebuffer table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

You can use this table with MySQL Cluster Disk Data tables to determine whether DiskPageBufferMemory is sufficiently large to allow data to be read from the buffer rather from disk; minimizing disk seeks can help improve performance of such tables.

You can determine the proportion of reads from DiskPageBufferMemory to the total number of reads using a query such as this one, which obtains this ratio as a percentage:

SELECT node_id,   100 * page_requests_direct_return / (page_requests_direct_return + page_requests_wait_io)   AS hit_ratio FROM ndbinfo.diskpagebuffer;

The result from this query should be similar to what is shown here, with one row for each data node in the cluster (in this example, the cluster has 4 data nodes):

+---------+-----------+| node_id | hit_ratio |+---------+-----------+|   5 |   97.6744 ||   6 |   97.6879 ||   7 |   98.1776 ||   8 |   98.1343 |+---------+-----------+4 rows in set (0.00 sec)

hit_ratio values approaching 100% indicate that only a very small number of reads are being made from disk rather than from the buffer, which means that Disk Data read performance is approaching an optimum level. If any of these values are less than 95%, this is a strong indicator that the setting for DiskPageBufferMemory needs to be increased in the config.ini file.

The logbuffer table provides information on MySQL Cluster log buffer usage.

The following table provides information about the columns in the logbuffers table. For each column, the table shows the name, data type, and a brief description.

This table provides information about MySQL Cluster log space usage.

The following table provides information about the columns in the logspaces table. For each column, the table shows the name, data type, and a brief description.

The membership table describes the view that each data node has of all the others in the cluster, including node group membership, president node, arbitrator, arbitrator successor, arbitrator connection states, and other information.

The following table provides information about the columns in the membership table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The node ID and node group ID are the same as reported by ndb_mgm -e "SHOW".

left_node and right_node are defined in terms of a model that connects all data nodes in a circle, in order of their node IDs, similar to the ordering of the numbers on a clock dial, as shown here:

In this example, we have 8 data nodes, numbered 5, 6, 7, 8, 12, 13, 14, and 15, ordered clockwise in a circle. We determine "left" and "right" from the interior of the circle. The node to the left of node 5 is node 15, and the node to the right of node 5 is node 6. You can see all these relationships by running the following query and observing the output:

mysql> SELECT node_id,left_node,right_node -> FROM ndbinfo.membership;+---------+-----------+------------+| node_id | left_node | right_node |+---------+-----------+------------+|   5 | 15 |  6 ||   6 | 5 |  7 ||   7 | 6 |  8 ||   8 | 7 | 12 ||  12 | 8 | 13 ||  13 | 12 | 14 ||  14 | 13 | 15 ||  15 | 14 |  5 |+---------+-----------+------------+8 rows in set (0.00 sec)

The designations "left" and "right" are used in the event log in the same way.

The president node is the node viewed by the current node as responsible for setting an arbitrator (see MySQL Cluster Start Phases). If the president fails or becomes disconnected, the current node expects the node whose ID is shown in the successor column to become the new president. The succession_order column shows the place in the succession queue that the current node views itself as having.

In a normal MySQL Cluster, all data nodes should see the same node as president, and the same node (other than the president) as its successor. In addition, the current president should see itself as 1 in the order of succession, the successor node should see itself as 2, and so on.

All nodes should show the same arb_ticket values as well as the same arb_state values. Possible arb_state values are ARBIT_NULL, ARBIT_INIT, ARBIT_FIND, ARBIT_PREP1, ARBIT_PREP2, ARBIT_START, ARBIT_RUN, ARBIT_CHOOSE, ARBIT_CRASH, and UNKNOWN.

arb_connected shows whether this node is connected to the node shown as this node's arbitrator.

The connected_rank1_arbs and connected_rank2_arbs columns each display a list of 0 or more arbitrators having an ArbitrationRank equal to 1, or to 2, respectively.

Like the arbitrator_validity_detail and arbitrator_validity_summary tables, this table was added in MySQL Cluster NDB 7.2.10.

Querying this table provides information similar to that provided by the ALL REPORT MemoryUsage command in the ndb_mgm client, or logged by ALL DUMP 1000.

The following table provides information about the columns in the memoryusage table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The total column represents the total amount of memory in bytes available for the given resource (data memory or index memory) on a particular data node. This number should be approximately equal to the setting of the corresponding configuration parameter in the config.ini file.

Suppose that the cluster has 2 data nodes having node IDs 1 and 2, and the config.ini file contains the following:

[ndbd default]DataMemory = 100MIndexMemory = 100M

The following query shows approximately the same values:

mysql> SELECT node_id, memory_type, total  > FROM ndbinfo.memoryusage;+---------+--------------+-----------+| node_id | memory_type  | total |+---------+--------------+-----------+|   1 | Data memory  | 104857600 ||   1 | Index memory | 105119744 ||   2 | Data memory  | 104857600 ||   2 | Index memory | 105119744 |+---------+--------------+-----------+4 rows in set (0.30 sec)

In this case, the total column values for index memory are slightly higher than the value set of IndexMemory due to internal rounding.

For the used_pages and total_pages columns, resources are measured in pages, which are 32K in size for DataMemory and 8K for IndexMemory.

This table contains information on the status of data nodes. For each data node that is running in the cluster, a corresponding row in this table provides the node's node ID, status, and uptime. For nodes that are starting, it also shows the current start phase.

The following table provides information about the columns in the nodes table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The uptime column shows the time in seconds that this node has been running since it was last started or restarted. This is a BIGINT value. This figure includes the time actually needed to start the node; in other words, this counter starts running the moment that ndbd or ndbmtd is first invoked; thus, even for a node that has not yet finished starting, uptime may show a non-zero value.

The status column shows the node's current status. This is one of: NOTHING, CMVMI, STARTING, STARTED, SINGLEUSER, STOPPING_1, STOPPING_2, STOPPING_3, or STOPPING_4. When the status is STARTING, you can see the current start phase in the start_phase column (see later in this section). SINGLEUSER is displayed in the status column for all data nodes when the cluster is in single user mode (see Section 17.5.8, "MySQL Cluster Single User Mode"). Seeing one of the STOPPING states does not necessarily mean that the node is shutting down but can mean rather that it is entering a new state; for example, if you put the cluster in single user mode, you can sometimes see data nodes report their state briefly as STOPPING_2 before the status changes to SINGLEUSER.

The start_phase column uses the same range of values as those used in the output of the ndb_mgm client node_id STATUS command (see Section 17.5.2, "Commands in the MySQL Cluster Management Client"). If the node is not currently starting, then this column shows 0. For a listing of MySQL Cluster start phases with descriptions, see Section 17.5.1, "Summary of MySQL Cluster Start Phases".

The config_generation column shows which version of the cluster configuration is in effect on each data node. This can be useful when performing a rolling restart of the cluster in order to make changes in configuration parameters. For example, from the output of the following SELECT statement, you can see that node 3 is not yet using the latest version of the cluster configuration (6) although nodes 1, 2, and 4 are doing so:

mysql> USE ndbinfo;Database changedmysql> SELECT * FROM nodes;+---------+--------+---------+-------------+-------------------+| node_id | uptime | status  | start_phase | config_generation |+---------+--------+---------+-------------+-------------------+|   1 |  10462 | STARTED |   0 | 6 ||   2 |  10460 | STARTED |   0 | 6 ||   3 |  10457 | STARTED |   0 | 5 ||   4 |  10455 | STARTED |   0 | 6 |+---------+--------+---------+-------------+-------------------+2 rows in set (0.04 sec)

Therefore, for the case just shown, you should restart node 3 to complete the rolling restart of the cluster.

Nodes that are stopped are not accounted for in this table. Suppose that you have a MySQL Cluster with 4 data nodes (node IDs 1, 2, 3 and 4), and all nodes are running normally, then this table contains 4 rows, 1 for each data node:

mysql> USE ndbinfo;Database changedmysql> SELECT * FROM nodes;+---------+--------+---------+-------------+-------------------+| node_id | uptime | status  | start_phase | config_generation |+---------+--------+---------+-------------+-------------------+|   1 |  11776 | STARTED |   0 | 6 ||   2 |  11774 | STARTED |   0 | 6 ||   3 |  11771 | STARTED |   0 | 6 ||   4 |  11769 | STARTED |   0 | 6 |+---------+--------+---------+-------------+-------------------+4 rows in set (0.04 sec)

If you shut down one of the nodes, only the nodes that are still running are represented in the output of this SELECT statement, as shown here:

ndb_mgm> 2 STOPNode 2: Node shutdown initiatedNode 2: Node shutdown completed.Node 2 has shutdown.

mysql> SELECT * FROM nodes;+---------+--------+---------+-------------+-------------------+| node_id | uptime | status  | start_phase | config_generation |+---------+--------+---------+-------------+-------------------+|   1 |  11807 | STARTED |   0 | 6 ||   3 |  11802 | STARTED |   0 | 6 ||   4 |  11800 | STARTED |   0 | 6 |+---------+--------+---------+-------------+-------------------+3 rows in set (0.02 sec)

This table provides information about data node resource availability and usage.

These resources are sometimes known as super-pools.

The following table provides information about the columns in the resources table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The resource_name can be one of RESERVED, DISK_OPERATIONS, DISK_RECORDS, DATA_MEMORY, JOBBUFFER, FILE_BUFFERS, or TRANSPORTER_BUFFERS.

The server_operations table contains entries for all ongoing NDB operations that the current SQL node (MySQL Server) is currently involved in. It effectively is a subset of the the cluster_operations table, in which operations for other SQL and API nodes are not shown.

The following table provides information about the columns in the server_operations table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The mysql_connection_id is the same as the connection or session ID shown in the output of SHOW PROCESSLIST. It is obtained from the INFORMATION_SCHEMA table NDB_TRANSID_MYSQL_CONNECTION_MAP.

The transaction ID is a unique 64-bit number which can be obtained using the NDB API's getTransactionId() method. (Currently, the MySQL Server does not expose the NDB API transaction ID of an ongoing transaction.)

The operation_type column can take any one of the values READ, READ-SH, READ-EX, INSERT, UPDATE, DELETE, WRITE, UNLOCK, REFRESH, SCAN, SCAN-SH, SCAN-EX, or <unknown>.

The state column can have any one of the values ABORT_QUEUED, ABORT_STOPPED, COMMITTED, COMMIT_QUEUED, COMMIT_STOPPED, COPY_CLOSE_STOPPED, COPY_FIRST_STOPPED, COPY_STOPPED, COPY_TUPKEY, IDLE, LOG_ABORT_QUEUED, LOG_COMMIT_QUEUED, LOG_COMMIT_QUEUED_WAIT_SIGNAL, LOG_COMMIT_WRITTEN, LOG_COMMIT_WRITTEN_WAIT_SIGNAL, LOG_QUEUED, PREPARED, PREPARED_RECEIVED_COMMIT, SCAN_CHECK_STOPPED, SCAN_CLOSE_STOPPED, SCAN_FIRST_STOPPED, SCAN_RELEASE_STOPPED, SCAN_STATE_USED, SCAN_STOPPED, SCAN_TUPKEY, STOPPED, TC_NOT_CONNECTED, WAIT_ACC, WAIT_ACC_ABORT, WAIT_AI_AFTER_ABORT, WAIT_ATTR, WAIT_SCAN_AI, WAIT_TUP, WAIT_TUPKEYINFO, WAIT_TUP_COMMIT, or WAIT_TUP_TO_ABORT. (If the MySQL Server is running with ndbinfo_show_hidden enabled, you can view this list of states by selecting from the ndb$dblqh_tcconnect_state table, which is normally hidden.)

You can obtain the name of an NDB table from its table ID by checking the output of ndb_show_tables.

The fragid is the same as the partition number seen in the output of ndb_desc --extra-partition-info (short form -p).

In client_node_id and client_block_ref, client refers to a MySQL Cluster API or SQL node (that is, an NDB API client or a MySQL Server attached to the cluster).

This table was added in MySQL Cluster NDB 7.2.2.

The server_transactions table is subset of the cluster_transactions table, but includes only those transactions in which the current SQL node (MySQL Server) is a participant, while including the relevant connection IDs.

The following table provides information about the columns in the server_transactions table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The mysql_connection_id is the same as the connection or session ID shown in the output of SHOW PROCESSLIST. It is obtained from the INFORMATION_SCHEMA table NDB_TRANSID_MYSQL_CONNECTION_MAP.

The transaction ID is a unique 64-bit number which can be obtained using the NDB API's getTransactionId() method. (Currently, the MySQL Server does not expose the NDB API transaction ID of an ongoing transaction.)

The state column can have any one of the values CS_ABORTING, CS_COMMITTING, CS_COMMIT_SENT, CS_COMPLETE_SENT, CS_COMPLETING, CS_CONNECTED, CS_DISCONNECTED, CS_FAIL_ABORTED, CS_FAIL_ABORTING, CS_FAIL_COMMITTED, CS_FAIL_COMMITTING, CS_FAIL_COMPLETED, CS_FAIL_PREPARED, CS_PREPARE_TO_COMMIT, CS_RECEIVING, CS_REC_COMMITTING, CS_RESTART, CS_SEND_FIRE_TRIG_REQ, CS_STARTED, CS_START_COMMITTING, CS_START_SCAN, CS_WAIT_ABORT_CONF, CS_WAIT_COMMIT_CONF, CS_WAIT_COMPLETE_CONF, CS_WAIT_FIRE_TRIG_REQ. (If the MySQL Server is running with ndbinfo_show_hidden enabled, you can view this list of states by selecting from the ndb$dbtc_apiconnect_state table, which is normally hidden.)

In client_node_id and client_block_ref, client refers to a MySQL Cluster API or SQL node (that is, an NDB API client or a MySQL Server attached to the cluster).

This table was added in MySQL Cluster NDB 7.2.2.

The threadblocks table associates data nodes, threads, and instances of NDB kernel blocks.

The following table provides information about the columns in the threadblocks table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

The block_name is one of the values found in the block_name column when selecting from the ndbinfo.blocks table. Although the list of possible values is static for a given MySQL Cluster release, the list may vary between releases.

This table was added in MySQL Cluster NDB 7.2.2.

The threadstat table provides a rough snapshot of statistics for threads running in the NDB kernel.

The following table provides information about the columns in the threadstat table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

os_time uses the system gettimeofday() call.

The values of the os_ru_utime, os_ru_stime, os_ru_minflt, os_ru_majflt, os_ru_nvcsw, and os_ru_nivcsw columns are obtained using the system getrusage() call, or the equivalent.

Since this table contains counts taken at a given point in time, for best results it is necessary to query this table periodically and store the results in an intermediate table or tables. The MySQL Server's Event Scheduler can be employed to automate such monitoring. For more information, see Section 19.4, "Using the Event Scheduler".

This table was added in MySQL Cluster NDB 7.2.2.

This table contains information about NDB transporters.

The following table provides information about the columns in the transporters table. For each column, the table shows the name, data type, and a brief description. Additional information can be found in the notes following the table.

For each running data node in the cluster, the transporters table displays a row showing the status of each of that node's connections with all nodes in the cluster, including itself. This information is shown in the table's status column, which can have any one of the following values: CONNECTING, CONNECTED, DISCONNECTING, or DISCONNECTED.

Connections to API and management nodes which are configured but not currently connected to the cluster are shown with status DISCONNECTED. Rows where the node_id is that of a data nodes which is not currently connected are not shown in this table. (This is similar omission of disconnected nodes in the ndbinfo.nodes table.

The remote_address, bytes_sent, and bytes_received columns were added in MySQL Cluster NDB 7.2.9. The remote_address is the the host name or address for the node whose ID is shown in the remote_node_id column. The bytes_sent from this node and bytes_received by this node are the numbers, respectively, of bytes sent and received by the node using this connection since it was established; for nodes whose status is CONNECTING or DISCONNECTED, these columns always display 0.

The connect_count, overloaded, overload_count ,slowdown, and slowdown_count columns were added in MySQL Cluster NDB 7.2.10. These counters are reset on connection, and retain their values after the remote node disconnects. Also beginning with MySQL Cluster NDB 7.2.10, the bytes_send and bytes_received counters are reset on connection as well, and so retain their values following disconnection. (Previously, the values in these columns were reset on disconnection.) (Bug #15935206)

Assume you have a 5-node cluster consisting of 2 data nodes, 2 SQL nodes, and 1 management node, as shown in the output of the SHOW command in the ndb_mgm client:

ndb_mgm> SHOWConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=1 @10.100.10.1  (5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=2 @10.100.10.2  (5.5.29-ndb-7.2.10, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=10   @10.100.10.10  (5.5.29-ndb-7.2.10)[mysqld(API)]   2 node(s)id=20   @10.100.10.20  (5.5.29-ndb-7.2.10)id=21   @10.100.10.21  (5.5.29-ndb-7.2.10)

There are 10 rows in the transporters table-5 for the first data node, and 5 for the second-assuming that all data nodes are running, as shown here:

mysql> SELECT node_id, remote_node_id, status ->   FROM ndbinfo.transporters;+---------+----------------+---------------+| node_id | remote_node_id | status |+---------+----------------+---------------+|   1 |  1 | CONNECTED ||   1 |  2 | CONNECTED ||   1 | 10 | CONNECTED ||   1 | 20 | CONNECTED ||   1 | 21 | CONNECTED ||   2 |  1 | CONNECTED ||   2 |  2 | CONNECTED ||   2 | 10 | CONNECTED ||   2 | 20 | CONNECTED ||   2 | 21 | CONNECTED |+---------+----------------+---------------+10 rows in set (0.04 sec)

If you shut down one of the data nodes in this cluster using the command 2 STOP in the ndb_mgm client, then repeat the previous query (again using the mysql client), this table now shows only 5 rows-1 row for each connection from the remaining management node to another node, including both itself and the data node that is currently offline-and displays CONNECTING for the status of each remaining connection to the data node that is currently offline, as shown here:

mysql> SELECT node_id, remote_node_id, status ->   FROM ndbinfo.transporters;+---------+----------------+---------------+| node_id | remote_node_id | status |+---------+----------------+---------------+|   1 |  1 | CONNECTED ||   1 |  2 | CONNECTING ||   1 | 10 | CONNECTED ||   1 | 20 | CONNECTED ||   1 | 21 | CONNECTED |+---------+----------------+---------------+5 rows in set (0.02 sec)

In this section, we discuss basic network security issues as they relate to MySQL Cluster. It is extremely important to remember that MySQL Cluster "out of the box" is not secure; you or your network administrator must take the proper steps to ensure that your cluster cannot be compromised over the network.

Cluster communication protocols are inherently insecure, and no encryption or similar security measures are used in communications between nodes in the cluster. Because network speed and latency have a direct impact on the cluster's efficiency, it is also not advisable to employ SSL or other encryption to network connections between nodes, as such schemes will effectively slow communications.

It is also true that no authentication is used for controlling API node access to a MySQL Cluster. As with encryption, the overhead of imposing authentication requirements would have an adverse impact on Cluster performance.

In addition, there is no checking of the source IP address for either of the following when accessing the cluster:

For these reasons, it is necessary to protect the cluster on the network level. The safest network configuration for Cluster is one which isolates connections between Cluster nodes from any other network communications. This can be accomplished by any of the following methods:

Whatever network configuration you use, remember that your objective from the viewpoint of keeping the cluster secure remains the same-to prevent any unessential traffic from reaching the cluster while ensuring the most efficient communication between the nodes in the cluster.

Because MySQL Cluster requires large numbers of ports to be open for communications between nodes, the recommended option is to use a segregated network. This represents the simplest way to prevent unwanted traffic from reaching the cluster.

In this section, we discuss how the MySQL privilege system works in relation to MySQL Cluster and the implications of this for keeping a MySQL Cluster secure.

Standard MySQL privileges apply to MySQL Cluster tables. This includes all MySQL privilege types (SELECT privilege, UPDATE privilege, DELETE privilege, and so on) granted on the database, table, and column level. As with any other MySQL Server, user and privilege information is stored in the mysql system database. The SQL statements used to grant and revoke privileges on NDB tables, databases containing such tables, and columns within such tables are identical in all respects with the GRANT and REVOKE statements used in connection with database objects involving any (other) MySQL storage engine. The same thing is true with respect to the CREATE USER and DROP USER statements.

It is important to keep in mind that, by default, the MySQL grant tables use the MyISAM storage engine. Because of this, those tables are not normally duplicated or shared among MySQL servers acting as SQL nodes in a MySQL Cluster. In other words, changes in users and their privileges do not automatically propagate between SQL nodes by default. In MySQL Cluster NDB 7.2 (and later), you can enable automatic distribution of MySQL users and privileges across MySQL Cluster SQL nodes; see Section 17.5.14, "Distributed MySQL Privileges for MySQL Cluster", for details.

Conversely, because there is no way in MySQL to deny privileges (privileges can either be revoked or not granted in the first place, but not denied as such), there is no special protection for NDB tables on one SQL node from users that have privileges on another SQL node; (This is true even if you are not using automatic distribution of user privileges. The definitive example of this is the MySQL root account, which can perform any action on any database object. In combination with empty [mysqld] or [api] sections of the config.ini file, this account can be especially dangerous. To understand why, consider the following scenario:

If these conditions are true, then anyone, anywhere can start a MySQL Server with --ndbcluster --ndb-connectstring=management_host and access this MySQL Cluster. Using the MySQL root account, this person can then perform the following actions:

In sum, you cannot have a safe MySQL Cluster if it is directly accessible from outside your local network.

Prior to MySQL Cluster NDB 7.2, you should never convert the system tables in the mysql database to use the NDB storage engine. There are a number of reasons why you should not do this, but the most important reason is this: Many of the SQL statements that affect mysql tables storing information about user privileges, stored routines, scheduled events, and other database objects cease to function if these tables are changed to use any storage engine other than MyISAM. This is a consequence of various MySQL Server internals. Beginning with MySQL Cluster NDB 7.2, you can use a stored procedure provided for this purpose (see Section 17.5.14, "Distributed MySQL Privileges for MySQL Cluster"), but you are strongly advised not to attempt convert the system tables manually.

Otherwise, if you need to synchronize mysql system tables between SQL nodes, you can use standard MySQL replication to do so, or employ a script to copy table entries between the MySQL servers.

Summary. The most important points to remember regarding the MySQL privilege system with regard to MySQL Cluster are listed here:

In this section, we discuss MySQL standard security procedures as they apply to running MySQL Cluster.

In general, any standard procedure for running MySQL securely also applies to running a MySQL Server as part of a MySQL Cluster. First and foremost, you should always run a MySQL Server as the mysql system user; this is no different from running MySQL in a standard (non-Cluster) environment. The mysql system account should be uniquely and clearly defined. Fortunately, this is the default behavior for a new MySQL installation. You can verify that the mysqld process is running as the system user mysql by using the system command such as the one shown here:

shell> ps aux | grep mysqlroot 10467  0.0  0.1   3616  1380 pts/3 S 11:53   0:00 \  /bin/sh ./mysqld_safe --ndbcluster --ndb-connectstring=localhost:1186mysql 10512  0.2  2.5  58528 26636 pts/3 Sl   11:53   0:00 \  /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql \  --datadir=/usr/local/mysql/var --user=mysql --ndbcluster \  --ndb-connectstring=localhost:1186 --pid-file=/usr/local/mysql/var/mothra.pid \  --log-error=/usr/local/mysql/var/mothra.errjon  10579  0.0  0.0   2736   688 pts/0 S+   11:54   0:00 grep mysql

If the mysqld process is running as any other user than mysql, you should immediately shut it down and restart it as the mysql user. If this user does not exist on the system, the mysql user account should be created, and this user should be part of the mysql user group; in this case, you should also make sure that the MySQL data directory on this system (as set using the --datadir option for mysqld) is owned by the mysql user, and that the SQL node's my.cnf file includes user=mysql in the [mysqld] section. Alternatively, you can start the MySQL server process with --user=mysql on the command line, but it is preferable to use the my.cnf option, since you might forget to use the command-line option and so have mysqld running as another user unintentionally. The mysqld_safe startup script forces MySQL to run as the mysql user.

As mentioned in the previous section (see Section 17.5.11.2, "MySQL Cluster and MySQL Privileges"), you should always set a root password for the MySQL Server as soon as you have it running. You should also delete the anonymous user account that is installed by default. You can accomplish these tasks using the following statements:

shell> mysql -u rootmysql> UPDATE mysql.user -> SET Password=PASSWORD('secure_password') -> WHERE User='root';mysql> DELETE FROM mysql.user -> WHERE User='';mysql> FLUSH PRIVILEGES;

Be very careful when executing the DELETE statement not to omit the WHERE clause, or you risk deleting all MySQL users. Be sure to run the FLUSH PRIVILEGES statement as soon as you have modified the mysql.user table, so that the changes take immediate effect. Without FLUSH PRIVILEGES, the changes do not take effect until the next time that the server is restarted.

MySQL Cluster Disk Data storage is implemented using a number of Disk Data objects. These include the following:

Undo log files and data files are actual files in the file system of each data node; by default they are placed in ndb_node_id_fs in the DataDir specified in the MySQL Cluster config.ini file, and where node_id is the data node's node ID. It is possible to place these elsewhere by specifying either an absolute or relative path as part of the filename when creating the undo log or data file. Statements that create these files are shown later in this section.

MySQL Cluster tablespaces and log file groups are not implemented as files.

Assuming that you have already set up a MySQL Cluster with all nodes (including management and SQL nodes), the basic steps for creating a MySQL Cluster table on disk are as follows:

Each of these tasks can be accomplished using SQL statements in the mysql client or other MySQL client application, as shown in the example that follows.

Performance note. The performance of a cluster using Disk Data storage is greatly improved if Disk Data files are kept on a separate physical disk from the data node file system. This must be done for each data node in the cluster to derive any noticeable benefit.

You may use absolute and relative file system paths with ADD UNDOFILE and ADD DATAFILE. Relative paths are calculated relative to the data node's data directory. You may also use symbolic links; see Section 17.5.12.2, "Using Symbolic Links with Disk Data Objects", for more information and examples.

A log file group, a tablespace, and any Disk Data tables using these must be created in a particular order. The same is true for dropping any of these objects:

For example, to drop all the objects created so far in this section, you would use the following statements:

mysql> DROP TABLE dt_1;mysql> ALTER TABLESPACE ts_1 -> DROP DATAFILE 'data_2.dat' -> ENGINE NDBCLUSTER;mysql> ALTER TABLESPACE ts_1 -> DROP DATAFILE 'data_1.dat' -> ENGINE NDBCLUSTER;mysql> DROP TABLESPACE ts_1 -> ENGINE NDBCLUSTER;mysql> DROP LOGFILE GROUP lg_1 -> ENGINE NDBCLUSTER;

These statements must be performed in the order shown, except that the two ALTER TABLESPACE ... DROP DATAFILE statements may be executed in either order.

You can obtain information about data files used by Disk Data tables by querying the FILES table in the INFORMATION_SCHEMA database. An extra "NULL row" provides additional information about undo log files. For more information and examples, see Section 20.29.1, "The INFORMATION_SCHEMA FILES Table".

It is also possible to view information about allocated and free disk space for each Disk Data table or table partition using the ndb_desc utility. For more information, see Section 17.4.9, "ndb_desc - Describe NDB Tables".

The performance of a MySQL Cluster that uses Disk Data storage can be greatly improved by separating data node file systems from undo log files and tablespace data files and placing these on different disks. In early versions of MySQL Cluster, there was no direct support for this in MySQL Cluster, but it was possible to achieve this separation using symbolic links as described in this section. MySQL Cluster NDB 7.2 supports the data node configuration parameters FileSystemPathDD, FileSystemPathDataFiles, and FileSystemPathUndoFiles, which make the use of symbolic links for this purpose unnecessary. For more information about these parameters, see Disk Data file system parameters.

Each data node in the cluster creates a file system in the directory named ndb_node_id_fs under the data node's DataDir as defined in the config.ini file. In this example, we assume that each data node host has 3 disks, aliased as /data0, /data1, and /data2, and that the cluster's config.ini includes the following:

[ndbd default]DataDir= /data0

Our objective is to place all Disk Data log files in /data1, and all Disk Data data files in /data2, on each data node host.

To accomplish this, perform the following steps:

The following items apply to Disk Data storage requirements:

Performance of Disk Data tables can be improved by minimizing the number of disk seeks by making sure that DiskPageBufferMemory is of sufficient size. You can query the diskpagebuffer table to help determine whether the value for this parameter needs to be increased.

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.

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:

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:

In this section, we list the basic steps required to add new data nodes to a MySQL Cluster. This procedure applies whether you are using ndbd or ndbmtd binaries for the data node processes. For a more detailed example, see Section 17.5.13.3, "Adding MySQL Cluster Data Nodes Online: Detailed Example".

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

You can add all the nodes desired, then issue several CREATE NODEGROUP commands in succession to add the new node groups to the cluster.

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 = 100MIndexMemory = 100MNoOfReplicas = 2DataDir = /usr/local/mysql/var/mysql-cluster[ndbd]Id = 1HostName = 192.168.0.1[ndbd]Id = 2HostName = 192.168.0.2[mgm]HostName = 192.168.0.10Id = 10[api]Id=20HostName = 192.168.0.20[api]Id=21HostName = 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> SHOWConnected to Management Server at: 192.168.0.10:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=1 @192.168.0.1  (5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=2 @192.168.0.2  (5.5.29-ndb-7.2.10, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=10   @192.168.0.10  (5.5.29-ndb-7.2.10)[mysqld(API)]   2 node(s)id=20   @192.168.0.20  (5.5.29-ndb-7.2.10)id=21   @192.168.0.21  (5.5.29-ndb-7.2.10)

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.

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 = 100MIndexMemory = 100MNoOfReplicas = 2DataDir = /usr/local/mysql/var/mysql-cluster[ndbd]Id = 1HostName = 192.168.0.1[ndbd]Id = 2HostName = 192.168.0.2[ndbd]Id = 3HostName = 192.168.0.3[ndbd]Id = 4HostName = 192.168.0.4[mgm]HostName = 192.168.0.10Id = 10[api]Id=20HostName = 192.168.0.20[api]Id=21HostName = 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:

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> SHOWConnected to Management Server at: 192.168.0.10:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=1 @192.168.0.1  (5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=2 @192.168.0.2  (5.5.29-ndb-7.2.10, 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.5.29-ndb-7.2.10)[mysqld(API)]   2 node(s)id=20   @192.168.0.20  (5.5.29-ndb-7.2.10)id=21   @192.168.0.21  (5.5.29-ndb-7.2.10)

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 RESTARTNode 1: Node shutdown initiatedNode 1: Node shutdown completed, restarting, no start.Node 1 is being restartedndb_mgm> Node 1: Start initiated (version 7.2.10)Node 1: Started (version 7.1.26)ndb_mgm> 2 RESTARTNode 2: Node shutdown initiatedNode 2: Node shutdown completed, restarting, no start.Node 2 is being restartedndb_mgm> Node 2: Start initiated (version 7.2.10)ndb_mgm> Node 2: Started (version 7.2.10)

You can verify that all existing data nodes were restarted using the updated configuration by checking the ndbinfo.nodes table in the mysql client.

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 shutdown081208 20:19:56 mysqld_safe mysqld from pid file/usr/local/mysql/var/tonfisk.pid endedshell> 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 databasesfrom /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

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> SHOWConnected to Management Server at: 192.168.0.10:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=1 @192.168.0.1  (5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=2 @192.168.0.2  (5.5.29-ndb-7.2.10, Nodegroup: 0)id=3 @192.168.0.3  (5.5.29-ndb-7.2.10, no nodegroup)id=4 @192.168.0.4  (5.5.29-ndb-7.2.10, no nodegroup)[ndb_mgmd(MGM)] 1 node(s)id=10   @192.168.0.10  (5.5.29-ndb-7.2.10)[mysqld(API)]   2 node(s)id=20   @192.168.0.20  (5.5.29-ndb-7.2.10)id=21   @192.168.0.21  (5.5.29-ndb-7.2.10)

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,4Nodegroup 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> SHOWConnected to Management Server at: 192.168.0.10:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=1 @192.168.0.1  (5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=2 @192.168.0.2  (5.5.29-ndb-7.2.10, Nodegroup: 0)id=3 @192.168.0.3  (5.5.29-ndb-7.2.10, Nodegroup: 1)id=4 @192.168.0.4  (5.5.29-ndb-7.2.10, Nodegroup: 1)[ndb_mgmd(MGM)] 1 node(s)id=10   @192.168.0.10  (5.5.29-ndb-7.2.10)[mysqld(API)]   2 node(s)id=20   @192.168.0.20  (5.5.29-ndb-7.2.10)id=21   @192.168.0.21  (5.5.29-ndb-7.2.10)

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 MEMORYNode 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: 1Fragment type: 9K Value: 6Min load factor: 78Max load factor: 80Temporary table: noNumber of attributes: 6Number of primary keys: 1Length of frm data: 340Row Checksum: 1Row GCI: 1SingleUserMode: 0ForceVarPart: 1FragmentCount: 2TableStatus: Retrieved-- Attributes --id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCRcountry_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORYtype Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORYip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORYaddresses Bigunsigned NULL AT=FIXED ST=MEMORYdate Bigunsigned NULL AT=FIXED ST=MEMORY-- Indexes --PRIMARY KEY(id) - UniqueHashIndexPRIMARY(id) - OrderedIndex-- Per partition info --Partition   Row count   Commit count  Frag fixed memory   Frag varsized memory0   26086   26086 1572864 5570561   26329   26329 1605632 557056NDBT_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: 16777217Fragment type: 9K Value: 6Min load factor: 78Max load factor: 80Temporary table: noNumber of attributes: 6Number of primary keys: 1Length of frm data: 341Row Checksum: 1Row GCI: 1SingleUserMode: 0ForceVarPart: 1FragmentCount: 4TableStatus: Retrieved-- Attributes --id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCRcountry_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORYtype Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORYip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORYaddresses Bigunsigned NULL AT=FIXED ST=MEMORYdate Bigunsigned NULL AT=FIXED ST=MEMORY-- Indexes --PRIMARY KEY(id) - UniqueHashIndexPRIMARY(id) - OrderedIndex-- Per partition info --Partition   Row count   Commit count  Frag fixed memory   Frag varsized memory0   12981   52296 1572864 5570561   13236   52515 1605632 5570562   13105   13105 819200  2949123   13093   13093 819200  294912NDBT_ProgramExit: 0 - OK

In addition, 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';

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 MEMORYNode 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)

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. 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 = 100MIndexMemory = 100MNoOfReplicas = 2DataDir = /usr/local/mysql/var/mysql-cluster[ndbd]Id = 1HostName = 192.168.0.1[ndbd]Id = 2HostName = 192.168.0.2[ndbd]Id = 3HostName = 192.168.0.3Nodegroup = 65536   [ndbd]Id = 4HostName = 192.168.0.4Nodegroup = 65536   [mgm]HostName = 192.168.0.10Id = 10[api]Id=20HostName = 192.168.0.20[api]Id=21HostName = 192.168.0.21

The data nodes to be brought online at a later time (nodes 3 and 4) can be configured with NodeGroup = 65536, in which case nodes 1 and 2 can each be started as shown here:

shell> ndbd -c 192.168.0.10 --initial

The data nodes configured with NodeGroup = 65536 are treated by the management server as though you had started nodes 1 and 2 using --nowait-nodes=3,4 after waiting for a period of time determined by the setting for the StartNoNodeGroupTimeout data node configuration parameter. By default, this is 15 seconds (15000 milliseconds).

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