In the following section, we answer questions that are frequently asked about MySQL Cluster and the NDBCLUSTER storage engine.

Questions

Questions and Answers

22.10.1: How do I handle MySQL users in a MySQL Cluster having multiple MySQL servers?

MySQL user accounts and privileges are not automatically propagated between different MySQL servers accessing the same MySQL Cluster. Therefore, you must make sure that these are copied between the SQL nodes yourself. You can do this manually, or automate the task with scripts.

22.10.2: Can I run multiple nodes on a single computer?

It is possible but not always advisable. One of the chief reasons to run a cluster is to provide redundancy. To obtain the full benefits of this redundancy, each node should reside on a separate machine. If you place multiple nodes on a single machine and that machine fails, you lose all of those nodes. For this reason, if you do run multiple data nodes on a single machine, it is extremely important that they be set up in such a way that the failure of this machine does not cause the loss of all the data nodes in a given node group.

Given that MySQL Cluster can be run on commodity hardware loaded with a low-cost (or even no-cost) operating system, the expense of an extra machine or two is well worth it to safeguard mission-critical data. It also worth noting that the requirements for a cluster host running a management node are minimal. This task can be accomplished with a 300 MHz Pentium or equivalent CPU and sufficient RAM for the operating system, plus a small amount of overhead for the ndb_mgmd and ndb_mgm processes.

It is acceptable to run multiple cluster data nodes on a single host that has multiple CPUs, cores, or both. Beginning with MySQL Cluster NDB 6.4, there is also a special multi-threaded version of the data node binary intended for use on such systems. For more information, see ndbmtd.

It is also possible in some cases to run data nodes and SQL nodes concurrently on the same machine; how well such an arrangement performs is dependent on a number of factors such as number of cores and CPUs as well as the amount of disk and memory available to the data node and SQL node processes, and you must take these factors into account when planning such a configuration.

22.10.3: Which versions of the MySQL software support Cluster? Do I have to compile from source?

Beginning with MySQL 5.1.24, MySQL Cluster is no longer supported in standard MySQL Server 5.1 releases. Instead, MySQL Cluster is now released as a separate product. Currently, three MySQL Cluster release series are available:

However, you should use MySQL NDB Cluster NDB 6.3 or 7.0 for new deployments, and if you are already using MySQL 5.1 with clustering support, to upgrade to one of these MySQL Cluster release series. For an overview of improvements made in MySQL Cluster NDB 6.2, 6.3, and 7.0, see Features Added in MySQL Cluster NDB 6.2, Features Added in MySQL Cluster NDB 6.3, and Features Added in MySQL Cluster NDB 7.0, respectively.

You can determine whether your MySQL Server has NDBCLUSTER support using either of the statements SHOW VARIABLES LIKE 'have_%' or SHOW ENGINES.

22.10.4: What do “NDB” and “NDBCLUSTER” mean?

“NDB” stands for “Network Database”. NDB and NDBCLUSTER are both names for the storage engine that enables clustering support in MySQL. Either name is equally correct; both names appear in our documentation, and either name can be used in the ENGINE option of a CREATE TABLE statement for creating a MySQL Cluster table.

22.10.5: How much RAM do I need to use MySQL Cluster? Is it possible to use disk memory at all?

In MySQL 5.1, Cluster is in-memory only. This means that all table data (including indexes) is stored in RAM. Therefore, if your data takes up 1 GB of space and you want to replicate it once in the cluster, you need 2 GB of memory to do so (1 GB per replica). This is in addition to the memory required by the operating system and any applications running on the cluster computers.

If a data node's memory usage exceeds what is available in RAM, then the system will attempt to use swap space up to the limit set for DataMemory. However, this will at best result in severely degraded performance, and may cause the node to be dropped due to slow response time (missed heartbeats). We do not recommend on relying on disk swapping in a production environment for this reason. In any case, once the DataMemory limit is reached, any operations requiring additional memory (such as inserts) will fail.

We have implemented disk data storage for MySQL Cluster in MySQL 5.1 and later but we have no plans to add this capability in MySQL 5.1. See MySQL Cluster Disk Data Tables, for more information.

You can use the following formula for obtaining a rough estimate of how much RAM is needed for each data node in the cluster:

(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes

To calculate the memory requirements more exactly requires determining, for each table in the cluster database, the storage space required per row (see Section 10.5, “Data Type Storage Requirements”, for details), and multiplying this by the number of rows. You must also remember to account for any column indexes as follows:

Note that creating MySQL Cluster tables with USING HASH for all primary keys and unique indexes will generally cause table updates to run more quickly — in some cases by a much as 20 to 30 percent faster than updates on tables where USING HASH was not used in creating primary and unique keys. This is due to the fact that less memory is required (because no ordered indexes are created), and that less CPU must be utilized (because fewer indexes must be read and possibly updated). However, it also means that queries that could otherwise use range scans must be satisfied by other means, which can result in slower selects.

When calculating Cluster memory requirements, you may find useful the ndb_size.pl utility which is available in recent MySQL 5.1 releases. This Perl script connects to a current (non-Cluster) MySQL database and creates a report on how much space that database would require if it used the NDBCLUSTER storage engine. For more information, see ndb_size.pl.

It is especially important to keep in mind that every MySQL Cluster table must have a primary key. The NDB storage engine creates a primary key automatically if none is defined; this primary key is created without USING HASH.

There is no easy way to determine exactly how much memory is being used for storage of Cluster indexes at any given time; however, warnings are written to the Cluster log when 80% of available DataMemory or IndexMemory is in use, and again when use reaches 85%, 90%, and so on.

22.10.6: Is MySQL Cluster transaction-safe? What isolation levels are supported?

Yes. For tables created with the NDB storage engine, transactions are supported. Currently, MySQL Cluster supports only the READ COMMITTED transaction isolation level.

22.10.7: Are there any limitations that I should be aware of when using MySQL Cluster?

Limitations on NDB tables in MySQL 5.1 (including MySQL Cluster NDB 6.x) include the following:

For a complete listing of limitations in MySQL Cluster, see Known Limitations of MySQL Cluster. For information about limitations in MySQL Cluster 5.0 that are lifted in MySQL 5.1, MySQL Cluster NDB 6.x, or MySQL Cluster NDB 7.0, see Previous MySQL Cluster Issues Resolved in MySQL 5.1 and MySQL Cluster NDB 6.x.

22.10.8: How do I start and stop MySQL Cluster?

It is necessary to start each node in the cluster separately, in the following order:

Each of these commands must be run from a system shell on the machine housing the affected node. (You do not have to be physically present at the machine — a remote login shell can be used for this purpose.) You can verify that the cluster is running by starting the NDB management client ndb_mgm on the machine housing the management node and issuing the SHOW or ALL STATUS command.

To shut down a running cluster, issue the command SHUTDOWN in the management client. Alternatively, you may enter the following command in a system shell:

shell> ndb_mgm -e "SHUTDOWN"

(The quotation marks in this example are optional, since there are no spaces in the command string following the -e option; in addition, the SHUTDOWN command, like other management client commands, is not case-sensitive.)

Either of these commands causes the ndb_mgm, ndb_mgm, and any ndbd processes to terminate gracefully. MySQL servers running as SQL nodes can be stopped using mysqladmin shutdown.

For more information, see Commands in the MySQL Cluster Management Client, and Safe Shutdown and Restart of MySQL Cluster.

22.10.9: What file systems can I use with MySQL Cluster? What about network file systems or network shares?

Generally, any file system that is native to the host operating system should work well with MySQL Cluster. If you find that a given file system works particularly well (or not so especially well) with MySQL Cluster, we invite you to discuss your findings in the MySQL Cluster Forums.

For Windows, we recommend that you use NTFS file systems for MySQL Cluster, just as we do for standard MySQL. We do not test MySQL Cluster with FAT or VFAT file systems. Because of this, we do not recommend their use with MySQL or MySQL Cluster.

MySQL Cluster is implemented as a shared-nothing solution; the idea behind this is that the failure of a single piece of hardware should not cause the failure of multiple cluster nodes, or possibly even the failure of the cluster as a whole. For this reason, the use of network shares or network file systems is not supported for MySQL Cluster. This also applies to shared storage devices such as SANs.

22.10.10: What is the difference between using MySQL Cluster vs using MySQL replication?

In traditional MySQL replication, a master MySQL server updates one or more slaves. Transactions are committed sequentially, and a slow transaction can cause the slave to lag behind the master. This means that if the master fails, it is possible that the slave might not have recorded the last few transactions. If a transaction-safe engine such as InnoDB is being used, a transaction will either be complete on the slave or not applied at all, but replication does not guarantee that all data on the master and the slave will be consistent at all times. In MySQL Cluster, all data nodes are kept in synchrony, and a transaction committed by any one data node is committed for all data nodes. In the event of a data node failure, all remaining data nodes remain in a consistent state.

In short, whereas standard MySQL replication is asynchronous, MySQL Cluster is synchronous.

We have implemented (asynchronous) replication for Cluster in MySQL 5.1 and later. MySQL Cluster Replication (also sometimes known as “geo-replication”) includes the capability to replicate both between two MySQL Clusters, and from a MySQL Cluster to a non-Cluster MySQL server. However, we do not plan to backport this functionality to MySQL 5.1. See MySQL Cluster Replication.

22.10.11: What are the hardware requirements for running MySQL Cluster?

MySQL Cluster should run on any platform for which NDB-enabled binaries are available. For data nodes and API nodes, faster CPUs and more memory are likely to improve performance, and 64-bit CPUs are likely to be more effective than 32-bit processors. There must be sufficient memory on machines used for data nodes to hold each node's share of the database (see How much RAM do I Need? for more information). For a computer which is used only for running the MySQL Cluster management server, the requirements are minimal; a common desktop PC (or the equivalent) is generally sufficient for this task. Nodes can communicate via the standard TCP/IP network and hardware. They can also use the high-speed SCI protocol; however, special networking hardware and software are required to use SCI (see Using High-Speed Interconnects with MySQL Cluster).

22.10.12: Can I run MySQL Cluster nodes inside virtual machines (such as those created by VMWare, Parallels, or Xen)?

This is possible but not recommended for a production environment.

We have found that running MySQL Cluster processes inside a virtual machine can give rise to issues with timing and disk subsystems that have a strong negative impact on the operation of the cluster. The behavior of the cluster is often unpredictable in these cases.

If an issue can be reproduced outside the virtual environment, then we may be able to provide assistance. Otherwise, we cannot support it at this time.

22.10.13: Can I use host names with MySQL Cluster?

Yes, it is possible to use DNS and DHCP for cluster hosts. However, if your application requires “five nines” availability, you should use fixed (numeric) IP addresses, since making communication between Cluster hosts dependent on services such as DNS and DHCP introduces additional potential points of failure.

22.10.14: Can I run two data nodes on a single host? Two SQL nodes?

Yes, it is possible to do this. In the case of multiple data nodes, it is advisable (but not required) for each node to use a different data directory. If you want to run multiple SQL nodes on one machine, each instance of mysqld must use a different TCP/IP port. However, in MySQL 5.1, running more than one cluster node of a given type per machine is generally not encouraged or supported for production use.

We also advise against running data nodes and SQL nodes together on the same host, since the ndbd and mysqld processes may compete for memory.

22.10.15: In the event of a catastrophic failure — say, for instance, the whole city loses power and my UPS fails — would I lose all my data?

All committed transactions are logged. Therefore, although it is possible that some data could be lost in the event of a catastrophe, this should be quite limited. Data loss can be further reduced by minimizing the number of operations per transaction. (It is not a good idea to perform large numbers of operations per transaction in any case.)

22.10.16: How do I continue to send queries in the event that one of the SQL nodes fails?

MySQL Cluster does not provide any sort of automatic failover between SQL nodes. Your application must be prepared to handlethe loss of SQL nodes and to fail over between them.

22.10.17: How do cluster nodes communicate with one another?

Cluster nodes can communicate via any of three different transport mechanisms: TCP/IP, SHM (shared memory), and SCI (Scalable Coherent Interface). Where available, SHM is used by default between nodes residing on the same cluster host; however, this is considered experimental. SCI is a high-speed (1 gigabit per second and higher), high-availability protocol used in building scalable multi-processor systems; it requires special hardware and drivers. See Using High-Speed Interconnects with MySQL Cluster, for more about using SCI as a transport mechanism for MySQL Cluster.

22.10.18: Can I add data nodes to a MySQL Cluster without restarting it?

Beginning with MySQL Cluster NDB 6.4, it is possible to add new data nodes to a running MySQL Cluster without taking it offline. For more information, see Adding MySQL Cluster Data Nodes Online.

For other types of MySQL Cluster nodes, a rolling restart is all that is required (see Performing a Rolling Restart of a MySQL Cluster).

In MySQL Cluster NDB 6.3 and earlier, it was not possible to add new data nodes without shutting down and restarting the MySQL Cluster.

22.10.19: What happened to MySQL Cluster NDB 6.4?

Because of the number and impact of new features introduced into MySQL Cluster following the General Availability of MySQL Cluster NDB 6.3, it was decided that the following release series represented a “major” new release series rather than a “minor” one, and should be known as MySQL Cluster 7.0.

The earliest development versions of MySQL Cluster NDB 7.0 were originally designated “MySQL Cluster 6.4”, and the first four releases in this series were identified as MySQL Cluster NDB 6.4.0 through NDB 6.4.3. MySQL Cluster NDB 7.0.4 is the fifth MySQL Cluster NDB 7.0 release; it is the successor to MySQL Cluster NDB 6.4.3.

For more information about MySQL Cluster NDB 7.0, see Features Added in MySQL Cluster NDB 7.0, and Changes in MySQL Cluster NDB 7.0.

22.10.20: Do I have to learn a new programming or query language to use MySQL Cluster?

No. Although some specialized commands are used to manage and configure the cluster itself, only standard (My)SQL statements are required for the following operations:

Some specialized configuration parameters and files are required to set up a MySQL Cluster — see MySQL Cluster Configuration Files, for information about these.

A few simple commands are used in the MySQL Cluster management client (ndb_mgm) for tasks such as starting and stopping cluster nodes. See Commands in the MySQL Cluster Management Client.

22.10.21: Do I need to do any special networking to run MySQL Cluster? How do computers in a cluster communicate?

MySQL Cluster is intended to be used in a high-bandwidth environment, with computers connecting via TCP/IP. Its performance depends directly upon the connection speed between the cluster's computers. The minimum connectivity requirements for MySQL Cluster include a typical 100-megabit Ethernet network or the equivalent. We recommend you use gigabit Ethernet whenever available.

The faster SCI protocol is also supported, but requires special hardware. See Using High-Speed Interconnects with MySQL Cluster, for more information about SCI.

22.10.22: With which operating systems can I use Cluster?

MySQL Cluster is supported on most Unix-like operating systems, including Linux, Mac OS X, and Solaris. Beginning with MySQL Cluster NDB 6.4, it is also possible to run MySQL Cluster on Windows platforms on an experimental basis; we hope to release a GA version for Windows in MySQL Cluster NDB 7.1.

We are continuing to work on providing MySQL Cluster support for additional platforms; eventually we intend to offer MySQL Cluster on all platforms for which MySQL itself is supported.

For more detailed information concerning the level of support which is offered for MySQL Cluster on various operating system versions, OS distributions, and hardware platforms, please refer to http://www.mysql.com/support/supportedplatforms/cluster.html.

22.10.23: When I run the SHOW command in the MySQL Cluster management client, I see a line of output that looks like this:

id=2    @10.100.10.32  (Version: 5.1.40, Nodegroup: 0, Master)

What is a “master node”, and what does it do? How do I configure a node so that it is the master?

The simplest answer is, “It's not something you can control, and it's nothing that you need to worry about in any case, unless you're a software engineer writing or analyzing the MySQL Cluster source code”.

If you don't find that answer satisfactory, here's a longer and more technical version:

A number of mechanisms in MySQL Cluster require distributed coordination among the data nodes. These distributed algorithms and protocols include global checkpointing, DDL (schema) changes, and node restart handling. To make this coordination simpler, the data nodes “elect” one of their number to be a “master”. There is no user-facing mechanism for influencing this selection, which is is completely automatic; the fact that it is automatic is a key part of MySQL Cluster's internal architecture.

When a node acts as a master for any of these mechanisms, it is usually the point of coordination for the activity, and the other nodes act as “servants”, carrying out their parts of the activity as directed by the master. If the node acting as master fails, then the remaining nodes elect a new master. Tasks in progress that were being coordinated by the old master may either fail or be continued by the new master, depending on the actual mechanism involved.

It is possible for some of these different mechanisms and protocols to have different master nodes, but in general the same master is chosen for all of them. The node indicated as the master in the output of SHOW in the management client is actually the DICT master (see The DBDICT Block, in the MySQL Cluster API Developer Guide, for more information), responsible for coordinating DDL and metadata activity.

MySQL Cluster is designed in such a way that the choice of master has no discernable effect outside the cluster itself. For example, the current master does not have significantly higher CPU or resource usage than the other data nodes, and failure of the master should not have a significantly different impact on the cluster than the failure of any other data node.

22.10.24: MySQL Cluster uses TCP/IP. Does this mean that I can run it over the Internet, with one or more nodes in remote locations?

It is very unlikely that a cluster would perform reliably under such conditions, as MySQL Cluster was designed and implemented with the assumption that it would be run under conditions guaranteeing dedicated high-speed connectivity such as that found in a LAN setting using 100 Mbps or gigabit Ethernet — preferably the latter. We neither test nor warrant its performance using anything slower than this.

Also, it is extremely important to keep in mind that communications between the nodes in a MySQL Cluster are not secure; they are neither encrypted nor safeguarded by any other protective mechanism. The most secure configuration for a cluster is in a private network behind a firewall, with no direct access to any Cluster data or management nodes from outside. (For SQL nodes, you should take the same precautions as you would with any other instance of the MySQL server.) For more information, see MySQL Cluster Security Issues.

22.10.25: How do I find out what an error or warning message means when using MySQL Cluster?

There are two ways in which this can be done:

22.10.26: What is an arbitrator?

If one or more data nodes in a cluster fail, it is possible that not all cluster data nodes will be able to “see” one another. In fact, it is possible that two sets of data nodes might become isolated from one another in a network partitioning, also known as a “split-brain” scenario. This type of situation is undesirable because each set of data nodes tries to behave as though it is the entire cluster.

When cluster data nodes go down, there are two possibilities. If more than 50% of the remaining data nodes can communicate with each other, we have what is sometimes called a “majority rules” situation, and this set of data nodes is considered to be the cluster. The arbitrator comes into play when there is an even number of data nodes: in such cases, the set of nodes to which the arbitrator belongs is considered to be the cluster, and any data or management nodes not belonging to this set are shut down.

The preceding information is somewhat simplified. A more complete explanation taking node groups into account follows:

When all data nodes in at least one node group are alive, network partitioning is not an issue, because no single subset of the cluster can form a functional cluster on its own. The real problem arises when no single node group has all its nodes alive, in which case network partitioning (the “split-brain” scenario) becomes possible. Then an arbitrator is required. All cluster nodes recognize the same node as the arbitrator, which is normally the management server; however, it is possible to configure any of the MySQL Servers in the cluster to act as the arbitrator instead. The arbitrator accepts the first set of cluster nodes to contact it, and tells the remaining set to shut down. Arbitrator selection is controlled by the ArbitrationRank configuration parameter for MySQL Server and management server nodes. (See Defining a MySQL Cluster Management Server, for details.)

The role of arbitrator does not in and of itself impose any heavy demands upon the host so designated, and thus the arbitrator host does not need to be particularly fast or to have extra memory especially for this purpose.

22.10.27: What data types are supported by MySQL Cluster?

22.10.28: What happens to MySQL Cluster data when the cluster is shut down?

The data that was held in memory by the cluster's data nodes is written to disk, and is reloaded into memory the next time that the cluster is started.

22.10.29: Is it a good idea to have more than one management node for a MySQL Cluster?

It can be helpful as a fail-safe. Only one management node controls the cluster at any given time, but it is possible to configure one management node as primary, and one or more additional management nodes to take over in the event that the primary management node fails.

See MySQL Cluster Configuration Files, for information on how to configure MySQL Cluster management nodes.

22.10.30: What do the different computers do in a MySQL Cluster?

A MySQL Cluster has both a physical and logical organization, with computers being the physical elements. The logical or functional elements of a cluster are referred to as nodes, and a computer housing a cluster node is sometimes referred to as a cluster host. There are three types of nodes, each corresponding to a specific role within the cluster. These are:

22.10.31: Is it possible to use FULLTEXT indexes with MySQL Cluster?

FULLTEXT indexing is not supported by any storage engine other than MyISAM. We are working to add this capability to MySQL Cluster tables in a future release.

22.10.32: I am trying to populate a MySQL Cluster database. The loading process terminates prematurely and I get an error message like this one: ERROR 1114: The table 'my_cluster_table' is full Why is this happening?

The cause is very likely to be that your setup does not provide sufficient RAM for all table data and all indexes, including the primary key required by the NDB storage engine and automatically created in the event that the table definition does not include the definition of a primary key.

It is also worth noting that all data nodes should have the same amount of RAM, since no data node in a cluster can use more memory than the least amount available to any individual data node. For example, if there are four computers hosting Cluster data nodes, and three of these have 3GB of RAM available to store Cluster data while the remaining data node has only 1GB RAM, then each data node can devote at most 1GB to MySQL Cluster data and indexes.

22.10.33: What storage engines are supported by MySQL Cluster?

Clustering with MySQL is supported only by the NDB storage engine. That is, in order for a table to be shared between nodes in a MySQL Cluster, the table must be created using ENGINE=NDB (or the equivalent option ENGINE=NDBCLUSTER).

It is possible to create tables using other storage engines (such as MyISAM or InnoDB) on a MySQL server being used with a MySQL Cluster, but these non-NDB tables do not participate in clustering; each such table is strictly local to the individual MySQL server instance on which it is created.

22.10.34: How do I import an existing MySQL database into a MySQL Cluster?

You can import databases into MySQL Cluster much as you would with any other version of MySQL. Other than the limitations mentioned elsewhere in this FAQ, the only other special requirement is that any tables to be included in the cluster must use the NDB storage engine. This means that the tables must be created with ENGINE=NDB or ENGINE=NDBCLUSTER.

It is also possible to convert existing tables that use other storage engines to NDBCLUSTER using one or more ALTER TABLE statement. However, the definition of the table must be compatible with the NDBCLUSTER storage engine prior to making the conversion. In MySQL 5.1, an additional workaround is also required; see Known Limitations of MySQL Cluster, for details.

22.10.35: Does MySQL Cluster support IPv6?

Beginning with MySQL Cluster NDB 6.4, IPv6 is supported for connections between SQL nodes (MySQL servers). However, connections between all other types of nodes must use IPv4.

In practical terms, this means that you can use IPv6 for replication between MySQL Clusters, but connections between nodes in the same MySQL Cluster must use IPv4. For more information, see Known Issues in MySQL Cluster Replication.

22.10.36: Can I mix different kinds of hardware and operating systems in one MySQL Cluster?

Yes, as long as all machines and operating systems have the same “endianness” (all big-endian or all little-endian). We are working to overcome this limitation in a future MySQL Cluster release.

It is also possible to use software from different MySQL Cluster releases on different nodes. However, we support this only as part of a rolling upgrade procedure (see Performing a Rolling Restart of a MySQL Cluster).

22.10.37: How many computers do I need to run a MySQL Cluster, and why?

A minimum of three computers is required to run a viable cluster. However, the minimum recommended number of computers in a MySQL Cluster is four: one each to run the management and SQL nodes, and two computers to serve as data nodes. The purpose of the two data nodes is to provide redundancy; the management node must run on a separate machine to guarantee continued arbitration services in the event that one of the data nodes fails.

To provide increased throughput and high availability, you should use multiple SQL nodes (MySQL Servers connected to the cluster). It is also possible (although not strictly necessary) to run multiple management servers.