JavaScript Editor Source code editor     What Is Ajax 

Main Page

Chapter 15. MySQL Cluster

Table of Contents

15.1. MySQL Cluster Overview
15.1.1. MySQL Cluster Core Concepts
15.1.2. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions
15.2. Simple Multi-Computer How-To
15.2.1. Hardware, Software, and Networking
15.2.2. Multi-Computer Installation
15.2.3. Multi-Computer Configuration
15.2.4. Initial Startup
15.2.5. Loading Sample Data and Performing Queries
15.2.6. Safe Shutdown and Restart
15.3. MySQL Cluster Configuration
15.3.1. Building MySQL Cluster from Source Code
15.3.2. Installing the Cluster Software
15.3.3. Quick Test Setup of MySQL Cluster
15.3.4. Configuration File
15.3.5. Overview of Cluster Configuration Parameters
15.3.6. Configuring Parameters for Local Checkpoints
15.4. Upgrading and Downgrading MySQL Cluster
15.4.1. Performing a Rolling Restart of the Cluster
15.4.2. Cluster Upgrade and Downgrade Compatibility
15.5. Process Management in MySQL Cluster
15.5.1. MySQL Server Process Usage for MySQL Cluster
15.5.2. ndbd — The Storage Engine Node Process
15.5.3. ndb_mgmd — The Management Server Process
15.5.4. ndb_mgm — The Management Client Process
15.5.5. Command Options for MySQL Cluster Processes
15.6. Management of MySQL Cluster
15.6.1. Summary of MySQL Cluster Start Phases
15.6.2. Commands in the MySQL Cluster Management Client
15.6.3. Event Reports Generated in MySQL Cluster
15.6.4. Single User Mode
15.6.5. Quick Reference: MySQL Cluster SQL Statements
15.7. On-line Backup of MySQL Cluster
15.7.1. Cluster Backup Concepts
15.7.2. Using The Management Client to Create a Backup
15.7.3. ndb_restore — Restore a Cluster Backup
15.7.4. Configuration for Cluster Backup
15.7.5. Backup Troubleshooting
15.8. Cluster Utility Programs
15.8.1. ndb_config — Extract NDB Configuration Information
15.8.2. ndb_cpcd — Automate Testing for NDB Development
15.8.3. ndb_delete_all — Delete All Rows from NDB Table
15.8.4. ndb_desc — Describe NDB Tables
15.8.5. ndb_drop_index — Drop Index from NDB Table
15.8.6. ndb_drop_table — Drop NDB Table
15.8.7. ndb_error_reporter — NDB Error-Reporting Utility
15.8.8. ndb_print_backup_file — Print NDB Backup File Contents
15.8.9. ndb_print_schema_file — Print NDB Schema File Contents
15.8.10. ndb_print_sys_file — Print NDB System File Contents
15.8.11. ndb_select_all — Print Rows from NDB Table
15.8.12. ndb_select_count — Print Row Counts for NDB Tables
15.8.13. ndb_show_tables — Display List of NDB Tables
15.8.14. — NDBCluster Size Requirement Estimator
15.8.15. ndb_waiter — Wait for Cluster to Reach a Given Status
15.9. Using High-Speed Interconnects with MySQL Cluster
15.9.1. Configuring MySQL Cluster to use SCI Sockets
15.9.2. Understanding the Impact of Cluster Interconnects
15.10. Known Limitations of MySQL Cluster
15.10.1. Non-Compliance In SQL Syntax
15.10.2. Limits and Differences from Standard MySQL Limits
15.10.3. Limits Relating to Transaction Handling
15.10.4. Error Handling
15.10.5. Limits Associated with Database Objects
15.10.6. Unsupported Or Missing Features
15.10.7. Limitations Relating to Performance
15.10.8. Issues Exclusive to MySQL Cluster
15.10.9. Limitations Relating to Multiple Cluster Nodes
15.10.10. Previous MySQL Cluster Issues Resolved in MySQL 5.0
15.11. MySQL Cluster Development Roadmap
15.11.1. MySQL Cluster Changes in MySQL 5.0
15.11.2. MySQL 5.1 Development Roadmap for MySQL Cluster
15.12. MySQL Cluster Glossary

MySQL Cluster is a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. It uses the NDB Cluster storage engine to enable running several MySQL servers in a cluster. This storage engine is available in MySQL 5.0 binary releases and in RPMs compatible with most modern Linux distributions.

MySQL 5.0 is currently available and supported on a number of platforms, including Linux, Solaris, Mac OS X, BSD, HP-UX, and many other Unix-style operating systems on a variety of hardware. For exact levels of support available for on specific combinations of operating system versions, operating system distributions, and hardware platforms, please refer to the Cluster Supported Platforms list maintained by the MySQL Support Team on the MySQL AB Web site.

MySQL Cluster is not currently supported on Microsoft Windows. We are working to make Cluster available on all operating systems supported by MySQL, including Windows, and will update the information provided here as this work continues.

This chapter represents a work in progress, and its contents are subject to revision as MySQL Cluster continues to evolve. Additional information regarding MySQL Cluster can be found on the MySQL AB Web site at

Additional resources

15.1. MySQL Cluster Overview

MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The shared-nothing architecture allows the system to work with very inexpensive hardware, and with a minimum of specific requirements for hardware or software.

MySQL Cluster is designed not to have any single point of failure. For this reason, each component is expected to have its own memory and disk, and the use of shared storage mechanisms such as network shares, network filesystems, and SANs is not recommended or supported.

MySQL Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB. In our documentation, the term NDB refers to the part of the setup that is specific to the storage engine, whereas “MySQL Cluster” refers to the combination of MySQL and the NDB storage engine.

A MySQL Cluster consists of a set of computers, each running a one or more processes which may include a MySQL server, a data node, a management server, and (possibly) a specialized data access programs. The relationship of these components in a cluster is shown here:

MySQL Cluster Components

All these programs work together to form a MySQL Cluster. When data is stored in the NDB Cluster storage engine, the tables are stored in the data nodes. Such tables are directly accessible from all other MySQL servers in the cluster. Thus, in a payroll application storing data in a cluster, if one application updates the salary of an employee, all other MySQL servers that query this data can see this change immediately.

The data stored in the data nodes for MySQL Cluster can be mirrored; the cluster can handle failures of individual data nodes with no other impact than that a small number of transactions are aborted due to losing the transaction state. Because transactional applications are expected to handle transaction failure, this should not be a source of problems.

15.1.1. MySQL Cluster Core Concepts

NDB is an in-memory storage engine offering high-availability and data-persistence features.

The NDB 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 currently configured independently of the MySQL servers. In a MySQL Cluster, each part of the cluster is considered to be a node.

Note: In many contexts, the term “node” is used to indicate a computer, but when discussing MySQL Cluster it means a process. It is possible to run any number of nodes on a single computer, for which we use the term cluster host.

(However, it should be noted MySQL does not currently support the use of multiple data nodes on a single computer in a production setting. See Section 15.10.9, “Limitations Relating to Multiple Cluster Nodes”.)

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:

  • Management node (MGM node): The role of this type of node is to manage the other nodes within the MySQL Cluster, performing such functions as providing configuration data, starting and stopping nodes, running backup, and so forth. Because this node type manages the configuration of the other nodes, a node of this type should be started first, before any other node. An MGM node is started with the command ndb_mgmd.

  • Data node: This type of node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you will need four data nodes. It is not necessary to have more than one replica. A data node is started with the command ndbd.

  • SQL node: This is a node that accesses the cluster data. In the case of MySQL Cluster, an SQL node is a traditional MySQL server that uses the NDB Cluster storage engine. An SQL node is typically started with the command mysqld --ndbcluster or by using mysqld with the ndbcluster option added to my.cnf.

    An SQL node is actually just a specialised type of API node, which designates any application which accesses Cluster data. One example of an API node is the ndb_restore utility that is used to restore a cluster backup. It is possible to write such applications using the NDB API.

Important: It is not realistic to expect to employ a three-node setup in a production environment. Such a configuration provides no redundancy; in order to benefit from MySQL Cluster's high-availability features, you must use multiple data and SQL nodes. The use of multiple management nodes is also highly recommended.

For a brief introduction to the relationships between nodes, node groups, replicas, and partitions in MySQL Cluster, see Section 15.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 (MGM node) 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 are of two types:

  • Standard MySQL clients: These are no different for MySQL Cluster than they are for standard (non-Cluster) MySQL. In other words, MySQL Cluster can be accessed from existing MySQL applications written in PHP, Perl, C, C++, Java, Python, Ruby, and so on.

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

15.1.2. MySQL Cluster Nodes, Node Groups, Replicas, and Partitions

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

Central to an understanding of this topic are the following concepts, listed here with brief definitions:

  • (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 (MGM) 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).

    Note: Currently, all node groups in a cluster must have the same number of nodes.

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

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

A MySQL Cluster, with 2 node groups having 2
          nodes each

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:

  • Partition 0 is stored on node group 0; a primary replica (primary copy) is stored on node 1, and a backup replica (backup copy of the partition) is stored on node 2.

  • Partition 1 is stored on the other node group (node group 1); this partition's primary replica is on node 3, and its backup replica is on node 4.

  • Partition 2 is stored on node group 0. However, the placing of its two replicas is reversed from that of Partition 0; for Partition 2, the primary replica is stored on node 2, and the backup on node 1.

  • Partition 3 is stored on node group 1, and the placement of its two replicas are reversed from those of partition 1. That is, its primary replica is located on node 4, with the backup on node 3.

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.

Nodes required to keep a 2x2 cluster

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.

JavaScript Editor Source code editor     What Is Ajax