Chapter 17. MySQL Cluster NDB 7.2

NDBCLUSTER (also known as NDB) is an in-memory storage engine offering high-availability and data-persistence features.

The NDBCLUSTER storage engine can be configured with a range of failover and load-balancing options, but it is easiest to start with the storage engine at the cluster level. MySQL Cluster's NDB storage engine contains a complete set of data, dependent only on other data within the cluster itself.

The "Cluster" portion of MySQL Cluster is configured independently of the MySQL servers. In a MySQL Cluster, each part of the cluster is considered to be a node.

There are three types of cluster nodes, and in a minimal MySQL Cluster configuration, there will be at least three nodes, one of each of these types:

For a brief introduction to the relationships between nodes, node groups, replicas, and partitions in MySQL Cluster, see Section 17.1.2, "MySQL Cluster Nodes, Node Groups, Replicas, and Partitions".

Configuration of a cluster involves configuring each individual node in the cluster and setting up individual communication links between nodes. MySQL Cluster is currently designed with the intention that data nodes are homogeneous in terms of processor power, memory space, and bandwidth. In addition, to provide a single point of configuration, all configuration data for the cluster as a whole is located in one configuration file.

The management server manages the cluster configuration file and the cluster log. Each node in the cluster retrieves the configuration data from the management server, and so requires a way to determine where the management server resides. When interesting events occur in the data nodes, the nodes transfer information about these events to the management server, which then writes the information to the cluster log.

In addition, there can be any number of cluster client processes or applications. These include standard MySQL clients, NDB-specific API programs, and management clients. These are described in the next few paragraphs.

Standard MySQL clients. MySQL Cluster can be used with existing MySQL applications written in PHP, Perl, C, C++, Java, Python, Ruby, and so on. Such client applications send SQL statements to and receive responses from MySQL servers acting as MySQL Cluster SQL nodes in much the same way that they interact with standalone MySQL servers.

MySQL clients using a MySQL Cluster as a data source can be modified to take advantage of the ability to connect with multiple MySQL servers to achieve load balancing and failover. For example, Java clients using Connector/J 5.0.6 and later can use jdbc:mysql:loadbalance:// URLs (improved in Connector/J 5.1.7) to achieve load balancing transparently; for more information about using Connector/J with MySQL Cluster, see Using Connector/J with MySQL Cluster.

NDB client programs. Client programs can be written that access MySQL Cluster data directly from the NDBCLUSTER storage engine, bypassing any MySQL Servers that may connected to the cluster, using the NDB API, a high-level C++ API. Such applications may be useful for specialized purposes where an SQL interface to the data is not needed. For more information, see The NDB API.

NDB-specific Java applications can also be written for MySQL Cluster using the MySQL Cluster Connector for Java. This MySQL Cluster Connector includes ClusterJ, a high-level database API similar to object-relational mapping persistence frameworks such as Hibernate and JPA that connect directly to NDBCLUSTER, and so does not require access to a MySQL Server. Support is also provided in MySQL Cluster NDB 7.1 and later for ClusterJPA, an OpenJPA implementation for MySQL Cluster that leverages the strengths of ClusterJ and JDBC; ID lookups and other fast operations are performed using ClusterJ (bypassing the MySQL Server), while more complex queries that can benefit from MySQL's query optimizer are sent through the MySQL Server, using JDBC. See Java and MySQL Cluster, and The ClusterJ API and Data Object Model, for more information.

The Memcache API for MySQL Cluster, implemented as the loadable ndbmemcache storage engine for memcached version 1.6 and later, is available beginning with MySQL Cluster NDB 7.2.2. This API can be used to provide a persistent MySQL Cluster data store, accessed using the memcache protocol.

The standard memcached caching engine is included in the MySQL Cluster NDB 7.2 distribution (7.2.2 and later). Each memcached server has direct access to data stored in MySQL Cluster, but is also able to cache data locally and to serve (some) requests from this local cache.

For more information, see ndbmemcache-Memcache API for MySQL Cluster.

Management clients. These clients connect to the management server and provide commands for starting and stopping nodes gracefully, starting and stopping message tracing (debug versions only), showing node versions and status, starting and stopping backups, and so on. An example of this type of program is the ndb_mgm management client supplied with MySQL Cluster (see Section 17.4.5, "ndb_mgm - The MySQL Cluster Management Client"). Such applications can be written using the MGM API, a C-language API that communicates directly with one or more MySQL Cluster management servers. For more information, see The MGM API.

Oracle also makes available MySQL Cluster Manager, which provides an advanced command-line interface simplifying many complex MySQL Cluster management tasks, such restarting a MySQL Cluster with a large number of nodes. The MySQL Cluster Manager client also supports commands for getting and setting the values of most node configuration parameters as well as mysqld server options and variables relating to MySQL Cluster. MySQL Cluster Manager 1.1 provides support for adding data nodes online. See the MySQL Cluster Manager User Manual, for more information.

Event logs. MySQL Cluster logs events by category (startup, shutdown, errors, checkpoints, and so on), priority, and severity. A complete listing of all reportable events may be found in Section 17.5.6, "Event Reports Generated in MySQL Cluster". Event logs are of the two types listed here:

Checkpoint. Generally speaking, when data is saved to disk, it is said that a checkpoint has been reached. More specific to MySQL Cluster, a checkpoint is a point in time where all committed transactions are stored on disk. With regard to the NDB storage engine, there are two types of checkpoints which work together to ensure that a consistent view of the cluster's data is maintained. These are shown in the following list:

This section discusses the manner in which MySQL Cluster divides and duplicates data for storage.

A number of concepts central to an understanding of this topic are discussed in the next few paragraphs.

(Data) Node. An ndbd process, which stores a replica -that is, a copy of the partition (see below) assigned to the node group of which the node is a member.

Each data node should be located on a separate computer. While it is also possible to host multiple ndbd processes on a single computer, such a configuration is not supported.

It is common for the terms "node" and "data node" to be used interchangeably when referring to an ndbd process; where mentioned, management nodes (ndb_mgmd processes) and SQL nodes (mysqld processes) are specified as such in this discussion.

Node Group. A node group consists of one or more nodes, and stores partitions, or sets of replicas (see next item).

The number of node groups in a MySQL Cluster is not directly configurable; it is a function of the number of data nodes and of the number of replicas (NoOfReplicas configuration parameter), as shown here:

[number_of_node_groups] = number_of_data_nodes / NoOfReplicas

Thus, a MySQL Cluster with 4 data nodes has 4 node groups if NoOfReplicas is set to 1 in the config.ini file, 2 node groups if NoOfReplicas is set to 2, and 1 node group if NoOfReplicas is set to 4. Replicas are discussed later in this section; for more information about NoOfReplicas, see Section 17.3.2.6, "Defining MySQL Cluster Data Nodes".

You can add new node groups (and thus new data nodes) online, to a running MySQL Cluster; see Section 17.5.13, "Adding MySQL Cluster Data Nodes Online", for more information.

Partition. This is a portion of the data stored by the cluster. There are as many cluster partitions as nodes participating in the cluster. Each node is responsible for keeping at least one copy of any partitions assigned to it (that is, at least one replica) available to the cluster.

A replica belongs entirely to a single node; a node can (and usually does) store several replicas.

NDB and user-defined partitioning. MySQL Cluster normally partitions NDBCLUSTER tables automatically. However, in MySQL 5.1 and later MySQL Cluster releases, it is possible to employ user-defined partitioning with NDBCLUSTER tables. This is subject to the following limitations:

For more information relating to MySQL Cluster and user-defined partitioning, see Section 17.1.6, "Known Limitations of MySQL Cluster", and Section 18.5.2, "Partitioning Limitations Relating to Storage Engines".

Replica. This is a copy of a cluster partition. Each node in a node group stores a replica. Also sometimes known as a partition replica. The number of replicas is equal to the number of nodes per node group.

The following diagram illustrates a MySQL Cluster with four data nodes, arranged in two node groups of two nodes each; nodes 1 and 2 belong to node group 0, and nodes 3 and 4 belong to node group 1. Note that only data (ndbd) nodes are shown here; although a working cluster requires an ndb_mgm process for cluster management and at least one SQL node to access the data stored by the cluster, these have been omitted in the figure for clarity.

The data stored by the cluster is divided into four partitions, numbered 0, 1, 2, and 3. Each partition is stored-in multiple copies-on the same node group. Partitions are stored on alternate node groups as follows:

What this means regarding the continued operation of a MySQL Cluster is this: so long as each node group participating in the cluster has at least one node operating, the cluster has a complete copy of all data and remains viable. This is illustrated in the next diagram.

In this example, where the cluster consists of two node groups of two nodes each, any combination of at least one node in node group 0 and at least one node in node group 1 is sufficient to keep the cluster "alive" (indicated by arrows in the diagram). However, if both nodes from either node group fail, the remaining two nodes are not sufficient (shown by the arrows marked out with an X); in either case, the cluster has lost an entire partition and so can no longer provide access to a complete set of all cluster data.

One of the strengths of MySQL Cluster is that it can be run on commodity hardware and has no unusual requirements in this regard, other than for large amounts of RAM, due to the fact that all live data storage is done in memory. (It is possible to reduce this requirement using Disk Data tables-see Section 17.5.12, "MySQL Cluster Disk Data Tables", for more information about these.) Naturally, multiple and faster CPUs can enhance performance. Memory requirements for other MySQL Cluster processes are relatively small.

The software requirements for MySQL Cluster are also modest. Host operating systems do not require any unusual modules, services, applications, or configuration to support MySQL Cluster. For supported operating systems, a standard installation should be sufficient. The MySQL software requirements are simple: all that is needed is a production release of MySQL Cluster. It is not strictly necessary to compile MySQL yourself merely to be able to use MySQL Cluster. We assume that you are using the binaries appropriate to your platform, available from the MySQL Cluster software downloads page at http://dev.mysql.com/downloads/cluster/.

For communication between nodes, MySQL Cluster supports TCP/IP networking in any standard topology, and the minimum expected for each host is a standard 100 Mbps Ethernet card, plus a switch, hub, or router to provide network connectivity for the cluster as a whole. We strongly recommend that a MySQL Cluster be run on its own subnet which is not shared with machines not forming part of the cluster for the following reasons:

Network communication and latency. MySQL Cluster requires communication between data nodes and API nodes (including SQL nodes), as well as between data nodes and other data nodes, to execute queries and updates. Communication latency between these processes can directly affect the observed performance and latency of user queries. In addition, to maintain consistency and service despite the silent failure of nodes, MySQL Cluster uses heartbeating and timeout mechanisms which treat an extended loss of communication from a node as node failure. This can lead to reduced redundancy. Recall that, to maintain data consistency, a MySQL Cluster shuts down when the last node in a node group fails. Thus, to avoid increasing the risk of a forced shutdown, breaks in communication between nodes should be avoided wherever possible.

The failure of a data or API node results in the abort of all uncommitted transactions involving the failed node. Data node recovery requires synchronization of the failed node's data from a surviving data node, and re-establishment of disk-based redo and checkpoint logs, before the data node returns to service. This recovery can take some time, during which the Cluster operates with reduced redundancy.

Heartbeating relies on timely generation of heartbeat signals by all nodes. This may not be possible if the node is overloaded, has insufficient machine CPU due to sharing with other programs, or is experiencing delays due to swapping. If heartbeat generation is sufficiently delayed, other nodes treat the node that is slow to respond as failed.

This treatment of a slow node as a failed one may or may not be desirable in some circumstances, depending on the impact of the node's slowed operation on the rest of the cluster. When setting timeout values such as HeartbeatIntervalDbDb and HeartbeatIntervalDbApi for MySQL Cluster, care must be taken care to achieve quick detection, failover, and return to service, while avoiding potentially expensive false positives.

Where communication latencies between data nodes are expected to be higher than would be expected in a LAN environment (on the order of 100 �s), timeout parameters must be increased to ensure that any allowed periods of latency periods are well within configured timeouts. Increasing timeouts in this way has a corresponding effect on the worst-case time to detect failure and therefore time to service recovery.

LAN environments can typically be configured with stable low latency, and such that they can provide redundancy with fast failover. Individual link failures can be recovered from with minimal and controlled latency visible at the TCP level (where MySQL Cluster normally operates). WAN environments may offer a range of latencies, as well as redundancy with slower failover times. Individual link failures may require route changes to propagate before end-to-end connectivity is restored. At the TCP level this can appear as large latencies on individual channels. The worst-case observed TCP latency in these scenarios is related to the worst-case time for the IP layer to reroute around the failures.

SCI support. It is also possible to use the high-speed Scalable Coherent Interface (SCI) with MySQL Cluster, but this is not a requirement. See Section 17.3.5, "Using High-Speed Interconnects with MySQL Cluster", for more about this protocol and its use with MySQL Cluster.

In this section, we discuss changes in the implementation of MySQL Cluster in MySQL MySQL Cluster NDB 7.2, as compared to MySQL Cluster NDB 7.1 and earlier releases. Changes and features most likely to be of interest are shown in the following table:

This section contains information about MySQL Cluster NDB 7.2 releases through 5.5.29-ndb-7.2.10, which is currently available for use in production beginning with MySQL Cluster NDB 7.2.4. MySQL Cluster NDB 7.1, MySQL Cluster NDB 7.0, and MySQL Cluster NDB 6.3 are previous GA release series; although these are still supported, we recommend that new deployments use MySQL Cluster NDB 7.2. For information about MySQL Cluster NDB 7.1 and previous releases, see MySQL Cluster NDB 6.1 - 7.1, in the MySQL 5.1 Manual.

MySQL Server offers a number of choices in storage engines. Since both NDBCLUSTER and InnoDB can serve as transactional MySQL storage engines, users of MySQL Server sometimes become interested in MySQL Cluster. They see NDB as a possible alternative or upgrade to the default InnoDB storage engine in MySQL 5.5. While NDB and InnoDB share common characteristics, there are differences in architecture and implementation, so that some existing MySQL Server applications and usage scenarios can be a good fit for MySQL Cluster, but not all of them.

In this section, we discuss and compare some characteristics of the NDB storage engine used by MySQL Cluster NDB 7.2 with InnoDB used in MySQL 5.5. The next few sections provide a technical comparison. In many instances, decisions about when and where to use MySQL Cluster must be made on a case-by-case basis, taking all factors into consideration. While it is beyond the scope of this documentation to provide specifics for every conceivable usage scenario, we also attempt to offer some very general guidance on the relative suitability of some common types of applications for NDB as opposed to InnoDB backends.

Recent MySQL Cluster NDB 7.2 releases use a mysqld based on MySQL 5.5, including support for InnoDB 1.1. While it is possible to use InnoDB tables with MySQL Cluster, such tables are not clustered. It is also not possible to use programs or libraries from a MySQL Cluster NDB 7.2 distribution with MySQL Server 5.5, or the reverse.

While it is also true that some types of common business applications can be run either on MySQL Cluster or on MySQL Server (most likely using the InnoDB storage engine), there are some important architectural and implementation differences. Section 17.1.5.1, "Differences Between the NDB and InnoDB Storage Engines", provides a summary of the these differences. Due to the differences, some usage scenarios are clearly more suitable for one engine or the other; see Section 17.1.5.2, "NDB and InnoDB Workloads". This in turn has an impact on the types of applications that better suited for use with NDB or InnoDB. See Section 17.1.5.3, "NDB and InnoDB Feature Usage Summary", for a comparison of the relative suitability of each for use in common types of database applications.

For information about the relative characteristics of the NDB and MEMORY storage engines, see When to Use MEMORY or MySQL Cluster.

See Chapter 14, Storage Engines, for additional information about MySQL storage engines.

In the sections that follow, we discuss known limitations in current releases of MySQL Cluster as compared with the features available when using the MyISAM and InnoDB storage engines. If you check the "Cluster" category in the MySQL bugs database at http://bugs.mysql.com, you can find known bugs in the following categories under "MySQL Server:" in the MySQL bugs database at http://bugs.mysql.com, which we intend to correct in upcoming releases of MySQL Cluster:

This information is intended to be complete with respect to the conditions just set forth. You can report any discrepancies that you encounter to the MySQL bugs database using the instructions given in Section 1.7, "How to Report Bugs or Problems". If we do not plan to fix the problem in MySQL Cluster NDB 6.X or 7.X, we will add it to the list.

See Section 17.1.6.11, "Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x" for a list of issues in MySQL Cluster in MySQL 5.1 that have been resolved in the current version.

This section covers installation of MySQL Cluster on Linux and other Unix-like operating systems. While the next few sections refer to a Linux operating system, the instructions and procedures given there should be easily adaptable to other supported Unix-like platforms.

MySQL Cluster NDB 7.2 is also available for Windows operating systems; for installation and setup instructions specific to Windows, see Section 17.2.2, "Installing MySQL Cluster on Windows".

Each MySQL Cluster host computer must have the correct executable programs installed. A host running an SQL node must have installed on it a MySQL Server binary (mysqld). Management nodes require the management server daemon (ndb_mgmd); data nodes require the data node daemon (ndbd or ndbmtd). It is not necessary to install the MySQL Server binary on management node hosts and data node hosts. It is recommended that you also install the management client (ndb_mgm) on the management server host.

Installation of MySQL Cluster on Linux can be done using precompiled binaries from Oracle (downloaded as a .tar.gz archive), with RPM packages (also available from Oracle), or from source code. All three of these installation methods are described in the section that follow.

Regardless of the method used, it is still necessary following installation of the MySQL Cluster binaries to create configuration files for all cluster nodes, before you can start the cluster. See Section 17.2.3, "Initial Configuration of MySQL Cluster".

MySQL-Cluster-component-producttype-ndbversion.distribution.architecture.rpmcomponent:= {server | client [| other]}producttype:= {gpl | advanced}ndbversion:= major.minor.releasedistribution:= {sles10 | rhel5 | el6}architecture:= {i386 | x86_64}

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.2.10-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.2.10-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-client-gpl-7.2.10-1.sles11.i386.rpm

shell> rpm -Uhv MySQL-Cluster-server-gpl-7.2.10-1.sles11.i386.rpm

MySQL Cluster NDB 7.2 binaries for Windows can be obtained from http://dev.mysql.com/downloads/cluster/. For information about installing MySQL Cluster on Windows from a binary release provided by Oracle, see Section 17.2.2.1, "Installing MySQL Cluster on Windows from a Binary Release".

It is also possible to compile and install MySQL Cluster from source on Windows using Microsoft Visual Studio. For more information, see Section 17.2.2.2, "Compiling and Installing MySQL Cluster from Source on Windows".

[mysqld]# Options for mysqld process:ndbcluster  # run NDB storage enginendb-connectstring=192.168.0.10  # location of management server

[mysql_cluster]# Options for data node process:ndb-connectstring=192.168.0.10  # location of management server

DataDir=C:\mysql\bin\cluster-logs

DataDir=C:\\mysql\\bin\\cluster-logs  # Escaped backslashes

DataDir=C:/mysql/bin/cluster-logs # Forward slashes

C:\> C:\mysql\bin\ndb_mgmd.exe --installInstalling service 'MySQL Cluster Management Server'   as '"C:\mysql\bin\ndbd.exe" "--service=ndb_mgmd"'Service successfully installed.

C:\> NET START ndb_mgmdThe MySQL Cluster Management Server service is starting.The MySQL Cluster Management Server service was started successfully.C:\> NET STOP ndb_mgmdThe MySQL Cluster Management Server service is stopping..The MySQL Cluster Management Server service was stopped successfully.

C:\> NET START 'MySQL Cluster Management Server'The MySQL Cluster Management Server service is starting.The MySQL Cluster Management Server service was started successfully.C:\> NET STOP  'MySQL Cluster Management Server'The MySQL Cluster Management Server service is stopping..The MySQL Cluster Management Server service was stopped successfully.

C:\> C:\mysql\bin\ndb_mgmd.exe --install=mgmd1Installing service 'MySQL Cluster Management Server'   as '"C:\mysql\bin\ndb_mgmd.exe" "--service=mgmd1"'Service successfully installed.

C:\> NET START mgmd1The MySQL Cluster Management Server service is starting.The MySQL Cluster Management Server service was started successfully.C:\> NET STOP mgmd1The MySQL Cluster Management Server service is stopping..The MySQL Cluster Management Server service was stopped successfully.

C:\> C:\mysql\bin\ndb_mgmd.exe --removeRemoving service 'MySQL Cluster Management Server'Service successfully removed.

C:\> C:\mysql\bin\ndb_mgmd.exe --remove=mgmd1Removing service 'mgmd1'Service successfully removed.

C:\> C:\mysql\bin\ndbd.exe --installInstalling service 'MySQL Cluster Data Node Daemon' as '"C:\mysql\bin\ndbd.exe" "--service=ndbd"'Service successfully installed.

C:\> NET START ndbdThe MySQL Cluster Data Node Daemon service is starting.The MySQL Cluster Data Node Daemon service was started successfully.C:\> NET STOP ndbdThe MySQL Cluster Data Node Daemon service is stopping..The MySQL Cluster Data Node Daemon service was stopped successfully.C:\> NET START 'MySQL Cluster Data Node Daemon'The MySQL Cluster Data Node Daemon service is starting.The MySQL Cluster Data Node Daemon service was started successfully.C:\> NET STOP 'MySQL Cluster Data Node Daemon'The MySQL Cluster Data Node Daemon service is stopping..The MySQL Cluster Data Node Daemon service was stopped successfully.

C:\> C:\mysql\bin\ndbd.exe --removeRemoving service 'MySQL Cluster Data Node Daemon'Service successfully removed.

C:\> C:\mysql\bin\ndbd.exe --install=dnode1Installing service 'dnode1' as '"C:\mysql\bin\ndbd.exe" "--service=dnode1"'Service successfully installed.C:\> NET START dnode1The MySQL Cluster Data Node Daemon service is starting.The MySQL Cluster Data Node Daemon service was started successfully.C:\> NET STOP dnode1The MySQL Cluster Data Node Daemon service is stopping..The MySQL Cluster Data Node Daemon service was stopped successfully.

C:\> C:\mysql\bin\ndbd.exe --remove=dnode1Removing service 'dnode1'Service successfully removed.

For our four-node, four-host MySQL Cluster, it is necessary to write four configuration files, one per node host.

Configuring the data nodes and SQL nodes. The my.cnf file needed for the data nodes is fairly simple. The configuration file should be located in the /etc directory and can be edited using any text editor. (Create the file if it does not exist.) For example:

shell> vi /etc/my.cnf

For each data node and SQL node in our example setup, my.cnf should look like this:

[mysqld]# Options for mysqld process:ndbcluster  # run NDB storage engine[mysql_cluster]# Options for MySQL Cluster processes:ndb-connectstring=192.168.0.10  # location of management server

After entering the preceding information, save this file and exit the text editor. Do this for the machines hosting data node "A", data node "B", and the SQL node.

Configuring the management node. The first step in configuring the management node is to create the directory in which the configuration file can be found and then to create the file itself. For example (running as root):

shell> mkdir /var/lib/mysql-clustershell> cd /var/lib/mysql-clustershell> vi config.ini

For our representative setup, the config.ini file should read as follows:

[ndbd default]# Options affecting ndbd processes on all data nodes:NoOfReplicas=2 # Number of replicasDataMemory=80M # How much memory to allocate for data storageIndexMemory=18M   # How much memory to allocate for index storage  # For DataMemory and IndexMemory, we have used the  # default values. Since the "world" database takes up  # only about 500KB, this should be more than enough for  # this example Cluster setup.[tcp default]# TCP/IP options:portnumber=2202   # This the default; however, you can use any  # port that is free for all the hosts in the cluster  # Note: It is recommended that you do not specify the port  # number at all and simply allow the default value to be used  # instead[ndb_mgmd]# Management process options:hostname=192.168.0.10   # Hostname or IP address of MGM nodedatadir=/var/lib/mysql-cluster  # Directory for MGM node log files[ndbd]# Options for data node "A": # (one [ndbd] section per data node)hostname=192.168.0.30   # Hostname or IP addressdatadir=/usr/local/mysql/data   # Directory for this data node's data files[ndbd]# Options for data node "B":hostname=192.168.0.40   # Hostname or IP addressdatadir=/usr/local/mysql/data   # Directory for this data node's data files[mysqld]# SQL node options:hostname=192.168.0.20   # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore)

After all the configuration files have been created and these minimal options have been specified, you are ready to proceed with starting the cluster and verifying that all processes are running. We discuss how this is done in Section 17.2.4, "Initial Startup of MySQL Cluster".

For more detailed information about the available MySQL Cluster configuration parameters and their uses, see Section 17.3.2, "MySQL Cluster Configuration Files", and Section 17.3, "MySQL Cluster Configuration". For configuration of MySQL Cluster as relates to making backups, see Section 17.5.3.3, "Configuration for MySQL Cluster Backups".

Starting the cluster is not very difficult after it has been configured. Each cluster node process must be started separately, and on the host where it resides. The management node should be started first, followed by the data nodes, and then finally by any SQL nodes:

If all has gone well, and the cluster has been set up correctly, the cluster should now be operational. You can test this by invoking the ndb_mgm management node client. The output should look like that shown here, although you might see some slight differences in the output depending upon the exact version of MySQL that you are using:

shell> ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> SHOWConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.30  (Version: 5.5.29-ndb-7.2.10, Nodegroup: 0, Master)id=3 @192.168.0.40  (Version: 5.5.29-ndb-7.2.10, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.10  (Version: 5.5.29-ndb-7.2.10)[mysqld(API)]   1 node(s)id=4 @192.168.0.20  (Version: 5.5.29-ndb-7.2.10)

The SQL node is referenced here as [mysqld(API)], which reflects the fact that the mysqld process is acting as a MySQL Cluster API node.

You should now be ready to work with databases, tables, and data in MySQL Cluster. See Section 17.2.5, "MySQL Cluster Example with Tables and Data", for a brief discussion.

Working with database tables and data in MySQL Cluster is not much different from doing so in standard MySQL. There are two key points to keep in mind:

If you are importing tables from an existing database using the output of mysqldump, you can open the SQL script in a text editor and add the ENGINE option to any table creation statements, or replace any existing ENGINE options. Suppose that you have the world sample database on another MySQL server that does not support MySQL Cluster, and you want to export the City table:

shell> mysqldump --add-drop-table world City > city_table.sql

The resulting city_table.sql file will contain this table creation statement (and the INSERT statements necessary to import the table data):

DROP TABLE IF EXISTS `City`;CREATE TABLE `City` (  `ID` int(11) NOT NULL auto_increment,  `Name` char(35) NOT NULL default '',  `CountryCode` char(3) NOT NULL default '',  `District` char(20) NOT NULL default '',  `Population` int(11) NOT NULL default '0',  PRIMARY KEY  (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);(remaining INSERT statements omitted)

You need to make sure that MySQL uses the NDBCLUSTER storage engine for this table. There are two ways that this can be accomplished. One of these is to modify the table definition before importing it into the Cluster database. Using the City table as an example, modify the ENGINE option of the definition as follows:

DROP TABLE IF EXISTS `City`;CREATE TABLE `City` (  `ID` int(11) NOT NULL auto_increment,  `Name` char(35) NOT NULL default '',  `CountryCode` char(3) NOT NULL default '',  `District` char(20) NOT NULL default '',  `Population` int(11) NOT NULL default '0',  PRIMARY KEY  (`ID`)) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);(remaining INSERT statements omitted)

This must be done for the definition of each table that is to be part of the clustered database. The easiest way to accomplish this is to do a search-and-replace on the file that contains the definitions and replace all instances of TYPE=engine_name or ENGINE=engine_name with ENGINE=NDBCLUSTER. If you do not want to modify the file, you can use the unmodified file to create the tables, and then use ALTER TABLE to change their storage engine. The particulars are given later in this section.

Assuming that you have already created a database named world on the SQL node of the cluster, you can then use the mysql command-line client to read city_table.sql, and create and populate the corresponding table in the usual manner:

shell> mysql world < city_table.sql

It is very important to keep in mind that the preceding command must be executed on the host where the SQL node is running (in this case, on the machine with the IP address 192.168.0.20).

To create a copy of the entire world database on the SQL node, use mysqldump on the noncluster server to export the database to a file named world.sql; for example, in the /tmp directory. Then modify the table definitions as just described and import the file into the SQL node of the cluster like this:

shell> mysql world < /tmp/world.sql

If you save the file to a different location, adjust the preceding instructions accordingly.

Running SELECT queries on the SQL node is no different from running them on any other instance of a MySQL server. To run queries from the command line, you first need to log in to the MySQL Monitor in the usual way (specify the root password at the Enter password: prompt):

shell> mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1 to server version: 5.5.29-ndb-7.2.10Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql>

We simply use the MySQL server's root account and assume that you have followed the standard security precautions for installing a MySQL server, including setting a strong root password. For more information, see Section 2.11.2, "Securing the Initial MySQL Accounts".

It is worth taking into account that Cluster nodes do not make use of the MySQL privilege system when accessing one another. Setting or changing MySQL user accounts (including the root account) effects only applications that access the SQL node, not interaction between nodes. See Section 17.5.11.2, "MySQL Cluster and MySQL Privileges", for more information.

If you did not modify the ENGINE clauses in the table definitions prior to importing the SQL script, you should run the following statements at this point:

mysql> USE world;mysql> ALTER TABLE City ENGINE=NDBCLUSTER;mysql> ALTER TABLE Country ENGINE=NDBCLUSTER;mysql> ALTER TABLE CountryLanguage ENGINE=NDBCLUSTER;

Selecting a database and running a SELECT query against a table in that database is also accomplished in the usual manner, as is exiting the MySQL Monitor:

mysql> USE world;mysql> SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5;+-----------+------------+| Name  | Population |+-----------+------------+| Bombay |   10500000 || Seoul | 9981619 || S�o Paulo | 9968485 || Shanghai  | 9696300 || Jakarta   | 9604900 |+-----------+------------+5 rows in set (0.34 sec)mysql> \qByeshell>

Applications that use MySQL can employ standard APIs to access NDB tables. It is important to remember that your application must access the SQL node, and not the management or data nodes. This brief example shows how we might execute the SELECT statement just shown by using the PHP 5.X mysqli extension running on a Web server elsewhere on the network:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"  "http://www.w3.org/TR/html4/loose.dtd"><html><head>  <meta http-equiv="Content-Type"   content="text/html; charset=iso-8859-1">  <title>SIMPLE mysqli SELECT</title></head><body><?php  # connect to SQL node:  $link = new mysqli('192.168.0.20', 'root', 'root_password', 'world');  # parameters for mysqli constructor are:  #   host, user, password, database  if( mysqli_connect_errno() ) die("Connect failed: " . mysqli_connect_error());  $query = "SELECT Name, Population FROM City ORDER BY Population DESC LIMIT 5";  # if no errors...  if( $result = $link->query($query) )  {?><table border="1" width="40%" cellpadding="4" cellspacing ="1">  <tbody>  <tr> <th width="10%">City</th> <th>Population</th>  </tr><? # then display the results... while($row = $result->fetch_object())  printf("<tr>\n  <td align=\"center\">%s</td><td>%d</td>\n</tr>\n",  $row->Name, $row->Population);?>  </tbody</table><?  # ...and verify the number of rows that were retrieved printf("<p>Affected rows: %d</p>\n", $link->affected_rows);  }  else # otherwise, tell us what went wrong echo mysqli_error();  # free the result set and the mysqli connection object  $result->close();  $link->close();?></body></html>

We assume that the process running on the Web server can reach the IP address of the SQL node.

In a similar fashion, you can use the MySQL C API, Perl-DBI, Python-mysql, or MySQL Connectors to perform the tasks of data definition and manipulation just as you would normally with MySQL.

To shut down the cluster, enter the following command in a shell on the machine hosting the management node:

shell> ndb_mgm -e shutdown

The -e option here is used to pass a command to the ndb_mgm client from the shell. (See Section 17.4.24, "Options Common to MySQL Cluster Programs - Options Common to MySQL Cluster Programs", for more information about this option.) The command causes the ndb_mgm, ndb_mgmd, and any ndbd or ndbmtd processes to terminate gracefully. Any SQL nodes can be terminated using mysqladmin shutdown and other means. On Windows platforms, assuming that you have installed the SQL node as a Windows service, you can use NET STOP MYSQL.

To restart the cluster on Unix platforms, run these commands:

On Windows platforms, assuming that you have installed all MySQL Cluster processes as Windows services using the default service names (see Section 17.2.2.4, "Installing MySQL Cluster Processes as Windows Services"), you can restart the cluster as follows:

In a production setting, it is usually not desirable to shut down the cluster completely. In many cases, even when making configuration changes, or performing upgrades to the cluster hardware or software (or both), which require shutting down individual host machines, it is possible to do so without shutting down the cluster as a whole by performing a rolling restart of the cluster. For more information about doing this, see Section 17.5.5, "Performing a Rolling Restart of a MySQL Cluster".

This section provides information about MySQL Cluster software and table file compatibility between different MySQL Cluster NDB 7.2 releases with regard to performing upgrades and downgrades as well as compatibility matrices and notes. You are expected already to be familiar with installing and configuring a MySQL Cluster prior to attempting an upgrade or downgrade. See Section 17.3, "MySQL Cluster Configuration".

Versions supported. The following versions of MySQL Cluster are supported for upgrades to MySQL Cluster NDB 7.2 (7.2.4 and later):

For information about upgrades and downgrades in previous MySQL Cluster release series, see Upgrade and Downgrade Compatibility: MySQL Cluster NDB 6.x, and Upgrade and downgrade compatibility: MySQL Cluster NDB 7.x.

NDB API, ClusterJ, and other applications used with recent releases of MySQL Cluster NDB 6.3 and later should continue to work with MySQL Cluster NDB 7.2.4 and later without rewriting or recompiling.

In MySQL Cluster NDB 7.2, the default values for a number of node configuration parameters have changed. See Improved default values for data node configuration parameters, for a listing of these.

In MySQL Cluster NDB 7.2.7 and later, the size of the hash map is 3840 LDM threads, an increase from 240 in previous versions. When upgrading a MySQL Cluster from MySQL Cluster NDB 7.2.6 and earlier to MySQL Cluster NDB 7.2.9 or later, you can modify existing tables online to take advantage of the new size: following the upgrade, increase the number of fragments by (for example) adding new data nodes to the cluster, and then execute ALTER ONLINE TABLE ... REORGANIZE PARTITION on any tables that were created in the older version. Following this, these tables can use the larger hash map size.