Chapter 6. MySQL Cluster Programs

mysqld is the traditional MySQL server process. To be used with MySQL Cluster, mysqld needs to be built with support for the NDBCLUSTER storage engine, as it is in the precompiled binaries available from http://dev.mysql.com/downloads/. If you build MySQL from source, you must invoke configure with the --with-ndbcluster option to enable NDB Cluster storage engine support.

If the mysqld binary has been built with Cluster support, the NDBCLUSTER storage engine is still disabled by default. You can use either of two possible options to enable this engine:

An easy way to verify that your server is running with the NDBCLUSTER storage engine enabled is to issue the SHOW ENGINES statement in the MySQL Monitor (mysql). You should see the value YES as the Support value in the row for NDBCLUSTER. If you see NO in this row or if there is no such row displayed in the output, you are not running an NDB-enabled version of MySQL. If you see DISABLED in this row, you need to enable it in either one of the two ways just described.

To read cluster configuration data, the MySQL server requires at a minimum three pieces of information:

Node IDs can be allocated dynamically, so it is not strictly necessary to specify them explicitly.

The mysqld parameter ndb-connectstring is used to specify the connectstring either on the command line when starting mysqld or in my.cnf. The connectstring contains the host name or IP address where the management server can be found, as well as the TCP/IP port it uses.

In the following example, ndb_mgmd.mysql.com is the host where the management server resides, and the management server listens for cluster messages on port 1186:

shell> mysqld --ndbcluster --ndb-connectstring=ndb_mgmd.mysql.com:1186

See Section 3.4.3, “The MySQL Cluster Connectstring”, for more information on connectstrings.

Given this information, the MySQL server will be a full participant in the cluster. (We often refer to a mysqld process running in this manner as an SQL node.) It will be fully aware of all cluster data nodes as well as their status, and will establish connections to all data nodes. In this case, it is able to use any data node as a transaction coordinator and to read and update node data.

You can see in the mysql client whether a MySQL server is connected to the cluster using SHOW PROCESSLIST. If the MySQL server is connected to the cluster, and you have the PROCESS privilege, then the first row of the output is as shown here:

mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db:
Command: Daemon
   Time: 1
  State: Waiting for event from ndbcluster
   Info: NULL

ndbd is the process that is used to handle all the data in tables using the NDB Cluster storage engine. This is the process that empowers a data node to accomplish distributed transaction handling, node recovery, checkpointing to disk, online backup, and related tasks.

In a MySQL Cluster, a set of ndbd processes cooperate in handling data. These processes can execute on the same computer (host) or on different computers. The correspondences between data nodes and Cluster hosts is completely configurable.

The following list describes command options specific to the MySQL Cluster data node program ndbd.

For options common to all NDBCLUSTER programs, see Section 6.23, “Options Common to MySQL Cluster Programs”.

ndbd generates a set of log files which are placed in the directory specified by DataDir in the config.ini configuration file.

These log files are listed below. node_id is the node's unique identifier. Note that node_id represents the node's unique identifier. For example, ndb_2_error.log is the error log generated by the data node whose node ID is 2.

It is recommended not to use a directory mounted through NFS because in some environments this can cause problems whereby the lock on the .pid file remains in effect even after the process has terminated.

To start ndbd, it may also be necessary to specify the host name of the management server and the port on which it is listening. Optionally, one may also specify the node ID that the process is to use.

shell> ndbd --connect-string="nodeid=2;host=ndb_mgmd.mysql.com:1186"

See Section 3.4.3, “The MySQL Cluster Connectstring”, for additional information about this issue. Section 6.2, “ndbd — The MySQL Cluster Data Node Daemon”, describes other options for ndbd.

When ndbd starts, it actually initiates two processes. The first of these is called the “angel process”; its only job is to discover when the execution process has been completed, and then to restart the ndbd process if it is configured to do so. Thus, if you attempt to kill ndbd via the Unix kill command, it is necessary to kill both processes, beginning with the angel process. The preferred method of terminating an ndbd process is to use the management client and stop the process from there.

The execution process uses one thread for reading, writing, and scanning data, as well as all other activities. This thread is implemented asynchronously so that it can easily handle thousands of concurrent actions. In addition, a watch-dog thread supervises the execution thread to make sure that it does not hang in an endless loop. A pool of threads handles file I/O, with each thread able to handle one open file. Threads can also be used for transporter connections by the transporters in the ndbd process. In a multi-processor system performing a large number of operations (including updates), the ndbd process can consume up to 2 CPUs if permitted to do so.

For a machine with many CPUs it is possible to use several ndbd processes which belong to different node groups; however, such a configuration is still considered experimental and is not supported for MySQL 5.1 in a production setting. See Chapter 12, Known Limitations of MySQL Cluster.

ndbmtd is a multi-threaded version of ndbd, the process that is used to handle all the data in tables using the NDBCLUSTER storage engine. ndbmtd is intended for use on host computers having multiple CPU cores. Except where otherwise noted, ndbmtd functions in the same way as ndbd; therefore, in this section, we concentrate on the ways in which ndbmtd differs from ndbd, and you should consult Section 6.2, “ndbd — The MySQL Cluster Data Node Daemon”, for additional information about running MySQL Cluster data nodes that apply to both the single-threaded and multi-threaded versions of the data node process.

Command-line options and configuration parameters used with ndbd also apply to ndbmtd. For more information about these options and parameters, see Section 6.24.2, “Program Options for ndbd and ndbmtd”, and Section 3.4.6, “Defining MySQL Cluster Data Nodes”, respectively.

ndbmtd is also file system-compatible with ndbd. In other words, a data node running ndbd can be stopped, the binary replaced with ndbmtd, and then restarted without any loss of data. (However, when doing this, you must make sure that MaxNoOfExecutionThreads is set to an apppriate value before restarting the node if you wish for ndbmtd to run in multi-threaded fashion.) Similarly, an ndbmtd binary can be replaced with ndbd simply by stopping the node and then starting ndbd in place of the multi-threaded binary. It is not necessary when switching between the two to start the data node binary using --initial.

Using ndbmtd differs from using ndbd in two key respects:

These differences are discussed in more detail in the next few paragraphs.

Number of execution threads.  The MaxNoOfExecutionThreads configuration parameter is used to determine the number of local query handler (LQH) threads spawned by ndbmtd. Although this parameter is set in [ndbd] or [ndbd default] sections of the config.ini file, it is exclusive to ndbmtd and does not apply to ndbd.

This parameter takes an integer value from 2 to 8 inclusive. Generally, you should set this to the number of CPU cores on the data node host, as shown in the following table:

(It is possible to set this parameter to other values within the permitted range, but these are automatically rounded as shown in the Value Used column of the next table in this section.)

The multi-threaded data node process always spawns at least 4 threads:

Setting this parameter to a value between 4 and 8 inclusive causes additional LQH threads to be used by ndbmtd (up to a maximum of 4 LQH threads), as shown in the following table:

Setting this parameter outside the permitted range of values causes the management server to abort on startup with the error Error line number: Illegal value value for parameter MaxNoOfExecutionThreads.

In MySQL Cluster NDB 6.4.0 through 6.4.3, the default value for this parameter was undefined, although the default behavior for ndbmtd was to use 1 LQH thread, as though MaxNoOfExecutionThreads had been set to 2. Beginning with MySQL Cluster NDB 7.0.4, this parameter has an explcit default value of 2, thus guaranteeing this default behavior.

In MySQL Cluster NDB 7.0, it is not possible to cause ndbmtd to use more than 1 TC thread, although we plan to introduce this capability in a future MySQL Cluster release series.

Like ndbd, ndbmtd generates a set of log files which are placed in the directory specified by DataDir in the config.ini configuration file. Except for trace files, these are generated in the same way and have the same names as those generated by ndbd.

In the event of a critical error, ndbmtd generates trace files describing what happened just prior to the error' occurrence. These files, which can be found in the data node's DataDir, are useful for analysis of problems by the MySQL Cluster Development and Support teams. One trace file is generated for each ndbmtd thread. The names of these files follow the pattern ndb_node_id_trace.log.trace_id_tthread_id, where node_id is the data node's unique node ID in the cluster, trace_id is a trace sequence number, and thread_id is the thread ID. For example, in the event of the failure of an ndbmtd process running as a MySQL Cluster data node having the node ID 3 and with MaxNoOfExecutionThreads equal to 4, four trace files are generated in the data node's data directory; if the is the first time this node has failed, then these files are named ndb_3_trace.log.1_t1, ndb_3_trace.log.1_t2, ndb_3_trace.log.1_t3, and ndb_3_trace.log.1_t4. Internally, these trace files follow the same format as ndbd trace files.

The ndbd exit codes and messages that are generated when a data node process shuts down prematurely are also used by ndbmtd. See ndbd Error Messages, for a listing of these.

The management server is the process that reads the cluster configuration file and distributes this information to all nodes in the cluster that request it. It also maintains a log of cluster activities. Management clients can connect to the management server and check the cluster's status.

The following list includes options that are specific to ndb_mgmd. For options common to all NDB programs, see Section 6.23, “Options Common to MySQL Cluster Programs”.

It is not strictly necessary to specify a connectstring when starting the management server. However, if you are using more than one management server, a connectstring should be provided and each node in the cluster should specify its node ID explicitly.

See Section 3.4.3, “The MySQL Cluster Connectstring”, for information about using connectstrings. Section 6.4, “ndb_mgmd — The MySQL Cluster Management Server Daemon”, describes other options for ndb_mgmd.

The following files are created or used by ndb_mgmd in its starting directory, and are placed in the DataDir as specified in the config.ini configuration file. In the list that follows, node_id is the unique node identifier.

The ndb_mgm management client process is actually not needed to run the cluster. Its value lies in providing a set of commands for checking the cluster's status, starting backups, and performing other administrative functions. The management client accesses the management server using a C API. Advanced users can also employ this API for programming dedicated management processes to perform tasks similar to those performed by ndb_mgm.

To start the management client, it is necessary to supply the host name and port number of the management server:

shell> ndb_mgm [host_name [port_num]]

For example:

shell> ndb_mgm ndb_mgmd.mysql.com 1186

The default host name and port number are localhost and 1186, respectively.

The following list includes options that are specific to ndb_mgm. For options common to all NDB programs, see Section 6.23, “Options Common to MySQL Cluster Programs”.

Additional information about using ndb_mgm can be found in Section 6.24.4, “Program Options for ndb_mgm”, and Section 7.2, “Commands in the MySQL Cluster Management Client”.

This tool extracts current configuration information for data nodes, SQL nodes, and API nodes from a cluster management node (and possibly its config.ini file). Beginning with MySQL Cluster NDB 6.3.25 and MySQL Cluster NDB 7.0.6, it can also provide an offline dump (in text or XML format) of all configuration parameters which can be used, along with their default, maximum, and minimum values and other information (see the discussion of the --configinfo and --xml options later in this section).

Usage:

ndb_config options

The options available for this utility differ somewhat from those used with the other utilities, and so are listed in their entirety in the next section, followed by some examples.

Options:

Examples:

This utility is found in the libexec directory. It is part of an internal automated test framework used in testing and debugging MySQL Cluster. Because it can control processes on remote systems, it is not advisable to use ndb_cpcd in a production cluster.

The source files for ndb_cpcd may be found in the directory storage/ndb/src/cw/cpcd, in the MySQL Cluster source tree.

ndb_delete_all deletes all rows from the given NDB table. In some cases, this can be much faster than DELETE or even TRUNCATE.

Usage:

ndb_delete_all -c connect_string tbl_name -d db_name

This deletes all rows from the table named tbl_name in the database named db_name. It is exactly equivalent to executing TRUNCATE db_name.tbl_name in MySQL.

Additional Options:

ndb_desc provides a detailed description of one or more NDB tables.

Usage:

ndb_desc -c connect_string tbl_name -d db_name [-p]

Sample Output:

MySQL table creation and population statements:

USE test;
CREATE TABLE fish (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    PRIMARY KEY pk (id),
    UNIQUE KEY uk (name)
) ENGINE=NDBCLUSTER;
INSERT INTO fish VALUES
    ('','guppy'), ('','tuna'), ('','shark'),
    ('','manta ray'), ('','grouper'), ('','puffer');

Output from ndb_desc:

shell> ./ndb_desc -c localhost fish -d test -p
-- fish --
Version: 16777221
Fragment type: 5
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 2
Number of primary keys: 1
Length of frm data: 268
Row Checksum: 1
Row GCI: 1
TableStatus: Retrieved
-- Attributes --
id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
name Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
uk(name) - OrderedIndex
PRIMARY(id) - OrderedIndex
uk$unique(name) - UniqueHashIndex
-- Per partition info --
Partition  Row count  Commit count  Frag fixed memory  Frag varsized memory
2          2          2             65536              327680
1          2          2             65536              327680
3          2          2             65536              327680
NDBT_ProgramExit: 0 - OK

Additional Options:

ndb_drop_index drops the specified index from an NDB table. It is recommended that you use this utility only as an example for writing NDB API applications — see the Warning later in this section for details.

Usage:

ndb_drop_index -c connect_string table_name index -d db_name

The statement shown above drops the index named index from the table in the database.

Additional Options: None that are specific to this application.

shell> ./ndb_drop_index -c localhost dogs ix -d ctest1
Dropping index dogs/idx...OK
NDBT_ProgramExit: 0 - OK
shell> ./mysql -u jon -p ctest1
Enter password: *******
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.1.12-beta-20060817
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW TABLES;
+------------------+
| Tables_in_ctest1 |
+------------------+
| a                |
| bt1              |
| bt2              |
| dogs             |
| employees        |
| fish             |
+------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM dogs;
ERROR 1296 (HY000): Got error 4243 'Index not found' from NDBCLUSTER

In such a case, your only option for making the table available to MySQL again is to drop the table and re-create it. You can use either the SQL statementDROP TABLE or the ndb_drop_table utility (see Section 6.11, “ndb_drop_table — Drop an NDB Table”) to drop the table.

ndb_drop_table drops the specified NDB table. (If you try to use this on a table created with a storage engine other than NDB, it fails with the error 723: No such table exists.) This operation is extremely fast — in some cases, it can be an order of magnitude faster than using DROP TABLE on an NDB table from MySQL.

Usage:

ndb_drop_table -c connect_string tbl_name -d db_name

Additional Options: None.

ndb_error_reporter creates an archive from data node and management node log files that can be used to help diagnose bugs or other problems with a cluster. It is highly recommended that you make use of this utility when filing reports of bugs in MySQL Cluster.

Usage:

ndb_error_reporter path/to/config-file [username] [--fs]

This utility is intended for use on a management node host, and requires the path to the management host configuration file (config.ini). Optionally, you can supply the name of a user that is able to access the cluster's data nodes via SSH, in order to copy the data node log files. ndb_error_reporter then includes all of these files in archive that is created in the same directory in which it is run. The archive is named ndb_error_report_YYYYMMDDHHMMSS.tar.bz2, where YYYYMMDDHHMMSS is a datetime string.

If the --fs is used, then the data node file systems are also copied to the management host and included in the archive that is produced by this script. As data node file systems can be extremely large even after being compressed, we ask that you please do not send archives created using this option to MySQL AB unless you are specifically requested to do so.

ndb_print_backup_file obtains diagnostic information from a cluster backup file.

Usage:

ndb_print_backup_file file_name

file_name is the name of a cluster backup file. This can be any of the files (.Data, .ctl, or .log file) found in a cluster backup directory. These files are found in the data node's backup directory under the subdirectory BACKUP-#, where # is the sequence number for the backup. For more information about cluster backup files and their contents, see Section 7.3.1, “MySQL Cluster Backup Concepts”.

Like ndb_print_schema_file and ndb_print_sys_file (and unlike most of the other NDB utilities that are intended to be run on a management server host or to connect to a management server) ndb_print_backup_file must be run on a cluster data node, since it accesses the data node file system directly. Because it does not make use of the management server, this utility can be used when the management server is not running, and even when the cluster has been completely shut down.

Additional Options: None.

ndb_print_schema_file obtains diagnostic information from a cluster schema file.

Usage:

ndb_print_schema_file file_name

file_name is the name of a cluster schema file. For more information about cluster schema files, see Cluster Data Node FileSystemDir Files.

Like ndb_print_backup_file and ndb_print_sys_file (and unlike most of the other NDB utilities that are intended to be run on a management server host or to connect to a management server) ndb_schema_backup_file must be run on a cluster data node, since it accesses the data node file system directly. Because it does not make use of the management server, this utility can be used when the management server is not running, and even when the cluster has been completely shut down.

Additional Options: None.

ndb_print_sys_file obtains diagnostic information from a MySQL Cluster system file.

Usage:

ndb_print_sys_file file_name

file_name is the name of a cluster system file (sysfile). Cluster system files are located in a data node's data directory (DataDir); the path under this directory to system files matches the pattern ndb_#_fs/D#/DBDIH/P#.sysfile. In each case, the # represents a number (not necessarily the same number). For more information, see Cluster Data Node FileSystemDir Files.

Like ndb_print_backup_file and ndb_print_schema_file (and unlike most of the other NDB utilities that are intended to be run on a management server host or to connect to a management server) ndb_print_backup_file must be run on a cluster data node, since it accesses the data node file system directly. Because it does not make use of the management server, this utility can be used when the management server is not running, and even when the cluster has been completely shut down.

Additional Options: None.

Reads a redo log file, checking it for errors, printing its contents in a human-readable format, or both. ndbd_redo_log_reader is intended for use primarily by MySQL developers and support personnel in debugging and diagnosing problems.

This utility was made available as part of default builds beginning with MySQL Cluster NDB 6.1.3. It remains under development, and its syntax and behavior are subject to change in future releases. For this reason, it should be considered experimental at this time.

The C++ source files for ndbd_redo_log_reader can be found in the directory /storage/ndb/src/kernel/blocks/dblqh/redoLogReader.

Usage:

ndbd_redo_log_reader file_name [options]

file_name is the name of a cluster REDO log file. REDO log files are located in the numbered directories under the data node's data directory (DataDir); the path under this directory to the REDO log files matches the pattern ndb_#_fs/D#/LCP/#/T#F#.Data. In each case, the # represents a number (not necessarily the same number). For more information, see Cluster Data Node FileSystemDir Files.

Additional Options:

The name of the file to be read may be followed by one or more of the options listed here:

Like ndb_print_backup_file and ndb_print_schema_file (and unlike most of the NDB utilities that are intended to be run on a management server host or to connect to a management server) ndbd_redo_log_reader must be run on a cluster data node, since it accesses the data node file system directly. Because it does not make use of the management server, this utility can be used when the management server is not running, and even when the cluster has been completely shut down.

The cluster restoration program is implemented as a separate command-line utility ndb_restore, which can normally be found in the MySQL bin directory. This program reads the files created as a result of the backup and inserts the stored information into the database.

ndb_restore must be executed once for each of the backup files that were created by the START BACKUP command used to create the backup (see Section 7.3.2, “Using The MySQL Cluster Management Client to Create a Backup”). This is equal to the number of data nodes in the cluster at the time that the backup was created.

Typical options for this utility are shown here:

ndb_restore [-c connectstring] -n
node_id [-s] [-m] -b backup_id -r --backup_path=/path/to/backup/files [-e]

The -c option is used to specify a connectstring which tells ndb_restore where to locate the cluster management server. (See Section 3.4.3, “The MySQL Cluster Connectstring”, for information on connectstrings.) If this option is not used, then ndb_restore attempts to connect to a management server on localhost:1186. This utility acts as a cluster API node, and so requires a free connection “slot” to connect to the cluster management server. This means that there must be at least one [api] or [mysqld] section that can be used by it in the cluster config.ini file. It is a good idea to keep at least one empty [api] or [mysqld] section in config.ini that is not being used for a MySQL server or other application for this reason (see Section 3.4.7, “Defining SQL and Other API Nodes in a MySQL Cluster”).

You can verify that ndb_restore is connected to the cluster by using the SHOW command in the ndb_mgm management client. You can also accomplish this from a system shell, as shown here:

shell> ndb_mgm -e "SHOW"

-n is used to specify the node ID of the data node on which the backups were taken.

The first time you run the ndb_restore restoration program, you also need to restore the metadata. In other words, you must re-create the database tables — this can be done by running it with the -m option. Note that the cluster should have an empty database when starting to restore a backup. (In other words, you should start ndbd with --initial prior to performing the restore. You should also remove manually any Disk Data files present in the data node's DataDir.)

It is possible to restore data without restoring table metadata. Prior to MySQL 5.1.17, ndb_restore did not perform any checks of table schemas; if a table was altered between the time the backup was taken and when ndb_restore was run, ndb_restore would still attempt to restore the data to the altered table.

Beginning with MySQL 5.1.17, the default behavior is for ndb_restore to fail with an error if table data do not match the table schema; this can be overridden using the --skip-table-check or -s option. Prior to MySQL 5.1.21, if this option is used, then ndb_restore attempts to fit data into the existing table schema, but the result of restoring a backup to a table schema that does not match the original is unspecified.

Beginning with MySQL Cluster NDB 6.3.8, ndb_restore supports limited attribute promotion in much the same way that it is supported by MySQL replication; that is, data backed up from a column of a given type can generally be restored to a column using a “larger, similar” type. For example, data from a CHAR(20) column can be restored to a column declared as VARCHAR(20), VARCHAR(30), or CHAR(30); data from a MEDIUMINT column can be restored to a column of type INT or BIGINT. See Replication of Columns Having Different Data Types, for a table of type conversions currently supported by attribute promotion.

Attribute promotion by ndb_restore must be enabled explicitly, as follows:

In addition to --promote-attributes, a --preserve-trailing-spaces option is also available for use with ndb_restore beginning with MySQL Cluster NDB 6.3.8. This option (short form -R) causes trailing spaces to be preserved when promoting a CHAR column to VARCHAR or a BINARY column to VARBINARY. Otherwise, any trailing spaces are dropped from column values when they are inserted into the new columns.

The -b option is used to specify the ID or sequence number of the backup, and is the same number shown by the management client in the Backup backup_id completed message displayed upon completion of a backup. (See Section 7.3.2, “Using The MySQL Cluster Management Client to Create a Backup”.)

-e adds (or restores) epoch information to the cluster replication status table. This is useful for starting replication on a MySQL Cluster replication slave. When this option is used, the row in the mysql.ndb_apply_status having 0 in the id column is updated if it already exists; such a row is inserted if it does not already exist. (See Section 9.9, “MySQL Cluster Backups With MySQL Cluster Replication”.)

The path to the backup directory is required; this is supplied to ndb_restore using the --backup_path option, and must include the subdirectory corresponding to the ID backup of the backup to be restored. For example, if the data node's DataDir is /var/lib/mysql-cluster, then the backup directory is /var/lib/mysql-cluster/BACKUP, and the backup files for the backup with the ID 3 can be found in /var/lib/mysql-cluster/BACKUP/BACKUP-3. The path may be absolute or relative to the directory in which the ndb_restore executable is located.

[backup_path=]/path/to/backup/files

It is possible to restore a backup to a database with a different configuration than it was created from. For example, suppose that a backup with backup ID 12, created in a cluster with two database nodes having the node IDs 2 and 3, is to be restored to a cluster with four nodes. Then ndb_restore must be run twice — once for each database node in the cluster where the backup was taken. However, ndb_restore cannot always restore backups made from a cluster running one version of MySQL to a cluster running a different MySQL version. See Section 5.2, “MySQL Cluster 5.1 and MySQL Cluster NDB 6.x/7.x Upgrade and Downgrade Compatibility”, for more information.

For more rapid restoration, the data may be restored in parallel, provided that there is a sufficient number of cluster connections available. That is, when restoring to multiple nodes in parallel, you must have an [api] or [mysqld] section in the cluster config.ini file available for each concurrent ndb_restore process. However, the data files must always be applied before the logs.

Formerly, when using ndb_restore to restore a backup made from a MySQL 5.0 cluster to a 5.1 cluster, VARCHAR columns were not resized and were recreated using the 5.0 fixed format. Beginning with MySQL 5.1.19, ndb_restore recreates such VARCHAR columns using MySQL Cluster 5.1's variable-width format. Also beginning with MySQL 5.1.19, this behavior can be overridden using the --no-upgrade option (short form: -u) when running ndb_restore.

Most of the options available for this program are shown in the following table:

Beginning with MySQL 5.1.18, several additional options are available for use with the --print_data option in generating data dumps, either to stdout, or to a file. These are similar to some of the options used with mysqldump, and are shown in the following table:

Beginning with MySQL 5.1.18, it is possible to restore selected databases, or to restore selected tables from a given database using the syntax shown here:

ndb_restore other_options db_name_1 [db_name_2[, db_name_3][, ...] | tbl_name_1[, tbl_name_2][, ...]]

In other words, you can specify either of the following to be restored:

Beginning with MySQL Cluster NDB 6.3.22 and 6.4.3, you can (and should) use instead the options --include-databases and --include-tables for restoring only specific databases or tables, respectively. --include-databases takes a comma-delimited list of databases to be restored. --include-tables takes a comma-delimited list of tables (in database.table format) to be restored. You can use these two options together. For example, the following causes all tables in databases db1 and db2, together with the tables t1 and t2 in database db3, to be restored (and no other databases or tables):

shell> ndb_restore [...] --include-databases=db1,db2 --include-tables=db3.t1,db3.t2

(For the sake of clarity and brevity, we have omitted other, possibly required, options in the example just shown.) When --include-databases, --include-tables, or both are used, only those databases or tables specified are restored; all other databases and tables are ignored by ndb_restore.

Also beginning with MySQL Cluster NDB 6.3.22 and 6.4.3, it is possible to exclude from being restored one or more databases, tables, or both using the ndb_restore options --exclude-databases and --exclude-tables. --exclude-databases takes a comma-delimited list of one or more databases which should not be restored. --exclude-tables takes a comma-delimited list of one or more tables, using database.table format, which should not be restored. You can use these two options together. For example, the following causes all tables in all databases except for databases db1 and db2, along with the tables t1 and t2 in database db3, not to be restored:

shell> ndb_restore [...] --exclude-databases=db1,db2 --exclude-tables=db3.t1,db3.t2

(Again, we have omitted other possibly necessary options in the interest of clarity and brevity from the example just shown.)

You should not use --include-databases or --include-tables together with --exclude-databases or --exclude-tables, since --include-databases and --include-tables exclude all databases and tables not explicitly named. Similarly, --exclude-databases and --exclude-tables include all databases and tables not listed in the arguments to these options.

Error reporting.  ndb_restore reports both temporary and permanent errors. In the case of temporary errors, it may able to recover from them. Beginning with MySQL 5.1.12, it reports Restore successful, but encountered temporary error, please look at configuration in such cases.

ndb_select_all prints all rows from an NDB table to stdout.

Usage:

ndb_select_all -c connect_string tbl_name -d db_name [> file_name]

Additional Options:

Sample Output:

Output from a MySQL SELECT statement:

mysql> SELECT * FROM ctest1.fish;
+----+-----------+
| id | name      |
+----+-----------+
|  3 | shark     |
|  6 | puffer    |
|  2 | tuna      |
|  4 | manta ray |
|  5 | grouper   |
|  1 | guppy     |
+----+-----------+
6 rows in set (0.04 sec)

Output from the equivalent invocation of ndb_select_all:

shell> ./ndb_select_all -c localhost fish -d ctest1
id      name
3       [shark]
6       [puffer]
2       [tuna]
4       [manta ray]
5       [grouper]
1       [guppy]
6 rows returned
NDBT_ProgramExit: 0 - OK

Note that all string values are enclosed by square brackets (“[...]”) in the output of ndb_select_all. For a further example, consider the table created and populated as shown here:

CREATE TABLE dogs (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    breed VARCHAR(50) NOT NULL,
    PRIMARY KEY pk (id),
    KEY ix (name)
)
TABLESPACE ts STORAGE DISK
ENGINE=NDBCLUSTER;
INSERT INTO dogs VALUES
    ('', 'Lassie', 'collie'),
    ('', 'Scooby-Doo', 'Great Dane'),
    ('', 'Rin-Tin-Tin', 'Alsatian'),
    ('', 'Rosscoe', 'Mutt');

This demonstrates the use of several additional ndb_select_all options:

shell> ./ndb_select_all -d ctest1 dogs -o ix -z --gci --disk
GCI     id name          breed        DISK_REF
834461  2  [Scooby-Doo]  [Great Dane] [ m_file_no: 0 m_page: 98 m_page_idx: 0 ]
834878  4  [Rosscoe]     [Mutt]       [ m_file_no: 0 m_page: 98 m_page_idx: 16 ]
834463  3  [Rin-Tin-Tin] [Alsatian]   [ m_file_no: 0 m_page: 34 m_page_idx: 0 ]
835657  1  [Lassie]      [Collie]     [ m_file_no: 0 m_page: 66 m_page_idx: 0 ]
4 rows returned
NDBT_ProgramExit: 0 - OK

ndb_select_count prints the number of rows in one or more NDB tables. With a single table, the result is equivalent to that obtained by using the MySQL statement SELECT COUNT(*) FROM tbl_name.

Usage:

ndb_select_count [-c connect_string] -ddb_name tbl_name[, tbl_name2[, ...]]

Additional Options: None that are specific to this application. However, you can obtain row counts from multiple tables in the same database by listing the table names separated by spaces when invoking this command, as shown under Sample Output.

Sample Output:

shell> ./ndb_select_count -c localhost -d ctest1 fish dogs
6 records in table fish
4 records in table dogs
NDBT_ProgramExit: 0 - OK

ndb_show_tables displays a list of all NDB database objects in the cluster. By default, this includes not only both user-created tables and NDB system tables, but NDB-specific indexes, internal triggers, and MySQL Cluster Disk Data objects as well.

Usage:

ndb_show_tables [-c connect_string]

Additional Options:

This is a Perl script that can be used to estimate the amount of space that would be required by a MySQL database if it were converted to use the NDBCLUSTER storage engine. Unlike the other utilities discussed in this section, it does not require access to a MySQL Cluster (in fact, there is no reason for it to do so). However, it does need to access the MySQL server on which the database to be tested resides.

Requirements:

ndb_size.pl can also be found in the MySQL sources in storage/ndb/tools. If this file is not present in your MySQL installation, you can obtain it from the MySQL Forge project page.

Usage:

perl ndb_size.pl db_name|ALL] [--hostname=host[:port]] [--socket=socket] [--user=user] \
    [--password=password] [--help|-h] [--format=(html|text)] [--loadqueries=file_name] [--savequeries=file_name]

By default, this utility attempts to analyze all databases on the server. You can specify a single database using the --database option; the default behavior can be made explicit by using ALL for the name of the database. You can also exclude one or more databases by using the --excludedbs with a comma-separated list of the names of the databases to be skipped. Similarly, you can cause specific tables to be skipped by listing their names, separated by commas, following the optional --excludetables option. A host name (and possibly a port as well) can be specified using --hostname; the default is localhost:3306. If necessary, you can specify a socket; the default is /var/lib/mysql.sock. A MySQL user name and password can be specified the corresponding options shown. It also possible to control the format of the output using the --format option; this can take either of the values html or text, with text being the default. An example of the text output is shown here:

shell> ndb_size.pl --database=test --socket=/tmp/mysql.sock
ndb_size.pl report for database: 'test' (1 tables)
--------------------------------------------------
Connected to: DBI:mysql:host=localhost;mysql_socket=/tmp/mysql.sock
Including information for versions: 4.1, 5.0, 5.1
test.t1
-------
DataMemory for Columns (* means varsized DataMemory):
              Column Name                 Type  Varsized   Key       4.1        5.0        5.1
          HIDDEN_NDB_PKEY               bigint             PRI         8          8          8
                       c2          varchar(50)         Y              52         52         4*
                       c1              int(11)                         4          4          4
                                                                      --         --         --
Fixed Size Columns DM/Row                                             64         64         12
   Varsize Columns DM/Row                                              0          0          4
DataMemory for Indexes:
               Index Name                 Type        4.1        5.0        5.1
                  PRIMARY                BTREE         16         16         16
                                                       --         --         --
       Total Index DM/Row                              16         16         16
IndexMemory for Indexes:
               Index Name        4.1        5.0        5.1
                  PRIMARY         33         16         16
                                  --         --         --
           Indexes IM/Row         33         16         16
Summary (for THIS table):
                                 4.1        5.0        5.1
    Fixed Overhead DM/Row         12         12         16
           NULL Bytes/Row          4          4          4
           DataMemory/Row         96         96         48  (Includes overhead, bitmap and indexes)
  Varsize Overhead DM/Row          0          0          8
   Varsize NULL Bytes/Row          0          0          4
       Avg Varside DM/Row          0          0         16
                 No. Rows          0          0          0
        Rows/32kb DM Page        340        340        680
Fixedsize DataMemory (KB)          0          0          0
Rows/32kb Varsize DM Page          0          0       2040
  Varsize DataMemory (KB)          0          0          0
         Rows/8kb IM Page        248        512        512
         IndexMemory (KB)          0          0          0
Parameter Minimum Requirements
------------------------------
* indicates greater than default
                Parameter          Default             4.1              5.0              5.1
          DataMemory (KB)            81920               0                0                0
       NoOfOrderedIndexes              128               1                1                1
               NoOfTables              128               1                1                1
         IndexMemory (KB)            18432               0                0                0
    NoOfUniqueHashIndexes               64               0                0                0
           NoOfAttributes             1000               3                3                3
             NoOfTriggers              768               5                5                5

For debugging purposes, the Perl arrays containing the queries run by this script can be read from the file specified using can be saved to a file using --savequeries; a file containing such arrays to be read in during script execution can be specified using --loadqueries. Neither of these options has a default value.

To produce output in HTML format, use the --format option and redirect the output to a file, as shown in this example:

shell> ndb_size.pl --database=test --socket=/tmp/mysql.sock --format=html > ndb_size.html

(Without the redirection, the output is sent to stdout.) This figure shows a portion of the generated ndb_size.html output file, as viewed in a Web browser:

The output from this script includes:

perl ndb_size.pl db_name hostname username password > file_name.html

ndb_waiter repeatedly (each 100 milliseconds) prints out the status of all cluster data nodes until either the cluster reaches a given status or the --timeout limit is exceeded, then exits. By default, it waits for the cluster to achieve STARTED status, in which all nodes have started and connected to the cluster. This can be overridden using the --no-contact and --not-started options (see Additional Options).

The node states reported by this utility are as follows:

Usage:

ndb_waiter [-c connect_string]

Additional Options:

Sample Output.  Shown here is the output from ndb_waiter when run against a 4-node cluster in which two nodes have been shut down and then started again manually. Duplicate reports (indicated by “...”) are omitted.

shell> ./ndb_waiter -c localhost
Connecting to mgmsrv at (localhost)
State node 1 STARTED
State node 2 NO_CONTACT
State node 3 STARTED
State node 4 NO_CONTACT
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 UNKNOWN
State node 3 STARTED
State node 4 NO_CONTACT
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 STARTING
State node 3 STARTED
State node 4 NO_CONTACT
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 STARTING
State node 3 STARTED
State node 4 UNKNOWN
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 STARTING
State node 3 STARTED
State node 4 STARTING
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 STARTED
State node 3 STARTED
State node 4 STARTING
Waiting for cluster enter state STARTED
...
State node 1 STARTED
State node 2 STARTED
State node 3 STARTED
State node 4 STARTED
Waiting for cluster enter state STARTED
NDBT_ProgramExit: 0 - OK

All MySQL Cluster programs (except for mysqld) take the options described in this section. Users of earlier MySQL Cluster versions should note that some of these options have been changed to make them consistent with one another as well as with mysqld. You can use the --help option with any MySQL Cluster program to view a list of the options which it supports.

The options in the following list are common to all MySQL Cluster executables.

For options specific to individual NDB programs, see Chapter 6, MySQL Cluster Programs.

See Section 4.2, “mysqld Command Options for MySQL Cluster”, for mysqld options relating to MySQL Cluster.

The next few sections contain summary tables providing basic information about command-line options used with MySQL Cluster programs.