What's the difference in using Cluster vs. using replication?
In a replication setup, 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, then 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 storage nodes are kept in synch, and a transaction committed by any one storage node is committed for all storage nodes. In the event of a storage node failure, all remaining storage nodes will remain in a consistent state.
In short, whereas MySQL replication is asynchronous, MySQL Cluster is synchronous.
Do I need to do any special networking to run 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 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 MySQL Cluster Interconnects for more information about SCI.
How many computers do I need to run a 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 storage nodes. The purpose of the two storage nodes is to provide redundancy; the management node must run on a separate machine in order to guarantee continued arbitration services in the event that one of the storage nodes fails.
What do the different computers do in a cluster?
A MySQL Cluster has both a physical and logical organisation, 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. Ideally, there will be one node per cluster host, although it is possible to run multiple nodes on a single host. There are three types of nodes, each corresponding to a specific role within the cluster. These are:
management node (MGM node): Provides management services for the cluster as a whole, including startup, shutdown, backups, and configuration data for the other nodes. The management node server is implemented as the application ndb_mgmd; the management client used to control MySQL Cluster via the MGM node is ndb_mgm.
storage node (data node): Stores and replicates data. Storage node functionality is handled by an instance of the NDB storage node process ndbd.
SQL node: This is simply an instance of MySQL Server (mysqld) started with the --ndb-cluster option.
With which operating systems can I use Cluster?
As of MySQL 4.1.10, MySQL Cluster is officially supported on Linux, Mac OS X, and Solaris. We are working to add Cluster support for other platforms, including Windows (Windows support expected in MySQL 5.0), and our goal is eventually to offer Cluster on all platforms for which MySQL itself is supported.
It may be possible to run Cluster processes on other operating systems (including Windows), but Cluster on any but the three mentioned here should be considered alpha software and not for production use.
What are the hardware requirements for running MySQL Cluster?
Cluster should run on any platform for which NDB-enabled binaries are available. Naturally, faster CPUs and more memory will improve performance, and 64-bit CPUs will likely be more effective than 32-bit processors. There must be sufficent memory on machines used for storage nodes to hold each node's share of the database (see How much RAM does Cluster require? for more info). Nodes can communicate via a standard TCP/IP network and hardware. For SCI support, special networking hardware is required.
Since MySQL Cluster uses TCP/IP, does that mean I can run it over the Internet, with one or more nodes in a remote location?
It is 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.
It is very doubtful in any case 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. We neither test nor warrant its performance using anything slower.
Do I have to learn a new programming or query language to use Cluster?
No. While some specialised commands are used to manage and configure the cluster itself, only standard (My)SQL queries and commands are required for:
creating, altering, and dropping tables
inserting, updating, and deleting table data
creating, changing, and dropping primary and unique indexes
configuring and managing SQL nodes (MySQL servers)
How do I find out what an error or warning message means when using Cluster?
There are two ways in which this can be done:
From within the MySQL Monitor, use SHOW ERRORS or SHOW WARNINGS immediately upon being notified of the error or warning condition. These can also be displayed in MySQL Query Browser.
From a system shell prompt, use perror --ndb
Is MySQL Cluster transaction-safe? What table types does Cluster support?
Yes. MySQL Cluster is enabled for tables created with the NDB storage engine, which supports transactions. NDB is the only MySQL storage engine which supports clustering.
What does "NDB" mean?
This stands for "Network Database".
Which version(s) of the MySQL software support Cluster? Do I have to compile from source?
Cluster is supported in the MySQL-max binaries from version
4.1.3 onwards. You can determine whether or not your server
binary has NDB support using either of the commands
SHOW VARIABLES LIKE 'have_%'; or
SHOW ENGINES;. (See
Section 5.1.2, «
mysqld-max, la version étendue du serveur
mysqld » for more information.)
Linux users, please note that NDB is
not included in the RPMs; you
should use the binaries supplied as
archives in the
area instead. You can also obtain NDB support by
-max binaries from source,
but it is not necessary to do so simply to use MySQL Cluster.
How much RAM do I need? Is it possible to use disk memory at all?
Currently, Cluster is in-memory only. This means that all table data (including indexes) is stored in RAM. Therefore, if your data takes up 1 gigabyte of space and you wish to replicate it once in the cluster, you'll need 2 gigabytes of memory to do so. This in addition to the memory required by the operating system and any applications running on the cluster computers.
You can use the following formula for obtaining a rough estimate of how much RAM is needed for each storage node in the cluster:
(SizeofDatabase * NumberOfReplicas * 1.1 ) / NumberOfStorageNodes
To calculate the memory requirements more exactly requires determining, for each table in the cluster database, the storage space required per row (see Section 11.5, « Capacités des colonnes » for details), and multiplying this by the number of rows. You must also remember to account for any column indexes as follows:
In MySQL 4.1, each primary key or hash index created for
an NDBCluster table requires 25 bytes storage, plus the
size of the key, per record. In MySQL 5.0, this amount is
reduced to 21-25 bytes per record. These indexes use
Each ordered index requires 10 bytes storage per record,
Creating a primary key or unique index also creates an
ordered index, unless this index is created with
USING HASH. In other words, if created
USING HASH, a primary key or
unique index on a Cluster table will take up 35 bytes
(plus the size of the key) per record in MySQL 4.1, and
31-35 bytes per record in MySQL 5.0.
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. This is due to the fact that less memory is
required (since no ordered indexes are created), and that
less CPU must be utilised (since fewer indexes must be
read and possibly updated).
It is especially important to keep in mind that
every MySQL Cluster table
must have a primary key, that the NDB storage engine will
create a primary key automatically if none is defined, and
that this primary key is created without
We often see questions from users who report that, when they're trying to populate a Cluster database, the loading process terminates prematurely and an error message like this one is observed:
ERROR 1114: The table 'my_cluster_table' is full
When this occurs, 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 NDB.
It is also worth noting that all storage nodes should have the same amount of RAM, as no storage node in a cluster can use more memory than the least amount available to any individual storage node. In other words, if there are three computers hosting Cluster storage nodes, with two of these having three gigabytes of RAM available to store Cluster data, and one having only one GB RAM, then each storage node can devote only one GB for Cluster.
In the event of a catstrophic failure - say, for instance, the whole city lost power AND my UPS failed - would I lose all my data?
All committed transactions are logged. Therefore, while 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 minimising the number of operations per transaction.
Is it possible to use FULLTEXT indexes with Cluster?
FULLTEXT indexing is not currently (MySQL
4.1.9) supported by the NDB storage engine. We are working to
add this capability in a future release.
Can I run multiple nodes on a single computer?
It is possible but not advisable. One of the chief reasons to run a cluster is to provide redundancy; in order to enjoy 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. Given that MySQL Cluster can be run on commodity hardware loaded with a low-cost or even no-cost operating system, it is well worth the expense of an extra machine or two in order 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 200 MHz Pentium CPU and sufficient RAM for the operating system plus a small amount of overhead for the ndb_mgmd and ndb_mgm processes.
Can I add nodes to a cluster without restarting it?
Not at present. A simple restart is all that is required for adding new MGM or SQL nodes to a Cluster. When adding storage nodes the process is more complex and requires the following steps:
Making a complete backup of all Cluster data
Complete shutting down the cluster and all cluster node processes
Restarting the cluster, using the --initial startup option
Restoring all cluster data from the backup
In future, we hope to implement "hot" reconfiguration capability for MySQL Cluster in order to minimize if not eliminate requirements for restarting the cluster when adding new nodes.
Are there any limitations that I should be aware of when using Cluster?
NDB tables in MySQL 4.1 are subject to the following limitations:
Not all character sets and collations are supported. (For a complete listing of those that are supported, see Section C.10.6, « MySQL Cluster-4.1.6, 10 octobre 2004 »).
FULLTEXT indexes and prefix indexes are
not supported. Only complete columns may be indexed.
Chapitre 18, Données spatiales avec MySQL are not supported.
Only complete rollbacks for transactions are supported. Partial rollbacks and rollbacks to save points are not supported.
The maximum number of attributes allowed per table is 128, and attribute names cannot be any longer than 31 characters. For each table, the maximum combined length of the table and database names is 122 characters.
The maximum size for a table row is 8 kilobytes, not counting BLOBs. There is no set limit for the number of rows per table; table size limits depend on a number of factors, in particular on the amount of RAM available to each data node.
The NDB engine does not support foreign key constraints. As with MyISAM tables, these are ignored.
Query caching is not supported.
We expect to lift many of these restrictions in MySQL 5.0. For additional information on current limitations, see Section 16.8, « Cluster Limitations in MySQL 4.1 ».
How do I import an existing MySQL database into a cluster?
You can import databases into MySQL Cluster much as you would with any other version of MySQL. Other than the limitation mentioned in the previous question, 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 the option ENGINE=NDB or ENGINE=NDBCLUSTER.
How do cluster nodes communicate with one another?
Cluster nodes can communicate via any of three different protocols: 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. 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 Section 16.7, « Utilisation d'interconnexions haute vitesse avec MySQL Cluster » for more about using SCI as a transport mechanism in MySQL Cluster.
What is an arbitrator?
If one or more nodes in a cluster fail, it is possible that not all cluster nodes will not be able to "see" one another. In fact, it is possible that two sets of 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 nodes tries to behave as though it is the entire cluster.
When cluster nodes go down, there are two possibilities. If more than 50% of the remaining nodes can communicate with each other, then we have what is sometimes called a "majority rules" situation, and this set of nodes is considered to be the cluster. The arbitrator comes into play when there is an even number of nodes: in such a case, the set of nodes to which the arbitrator belongs is considered to be the cluster, and nodes not belonging to this set are shut down.
The above is somewhat simplified; a more complete explanation taking into account node groups follows below:
When all nodes in at least one node group are alive, network
partitioning is not an issue, because no one portion of the
cluster can form a functional cluster. 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
recognise 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
arbirtrator instead. The arbitrator accepts the first set of
cluster nodes to contact it, and tells the remaining set to
die. Arbitrator selection is controlled by the
ArbitrationRank configuration parameter for
MySQL Server and management server nodes. (See
Section 126.96.36.199, « Définition du serveur de gestion du cluster » for details.)
It should also be noted that the role of arbitrator does not
in and of itself impose any heavy demands upon the host so
designated, and thus the artitrator host does not need to be
particularly fast or to have extra memory especially for this
What column types are supported by MySQL Cluster?
MySQL Cluster supports all of the usual MySQL column types,
with the exception of those associated with MySQL's
Chapitre 18, Données spatiales avec MySQL. In addition, there are
some differences with regard to indexes when used with NDB
tables. Note: In MySQL 4.1
and 5.0, Cluster tables (that is, tables created with
ENGINE=NDBCLUSTER) have only fixed-width
rows. This means that (for example) each record containing a
VARCHAR(255) column will will require 256
bytes of storage for that column, regardless of the size of
the data stored therein. This issue is expected to be fixed in
See Section 16.8, « Cluster Limitations in MySQL 4.1 » for more information about these issues.
How do I start and stop MySQL Cluster?
It is necessary to start each node in the cluster separately, in the following order:
Start the management node with the ndb_mgmd command.
Start each storage node with the ndbd command.
Start each MySQL server (SQL node) using mysqld_safe --user=mysql &.
Each of these commands must be run from a shell on the machine housing the affected node. You can verify the the cluster is running by starting the MGM management client ndb_mgm on the machine housing the MGM node.
What happens to cluster data when the cluster is shut down?
The data held in memory by the cluster's storage nodes is written to disk, and is reloaded in memory the next time that the cluster is started.
To shut down the cluster, enter the following in a shell on the machine hosting the MGM node:
ndb_mgm -e shutdown
This will cause the ndb_mgm, ndb_mgm, and any ndbd processes to terminate gracefully. MySQL servers running as Cluster SQL nodes can be stopped using mysqladmin shutdown.
For more information, see Section 16.6.1, « Commandes du client de gestion du Cluster » and Section 16.3.6, « Arrêt et redémarrage du cluster ».
Is it helpful to have more than one management node for a cluster?
It can be helpful as a fail-safe. Only one MGM node controls the cluster at any given time, but it is possible to configure one MGM as primary, and one or more additional management nodes to take over in the evnt that the primary MGM node fails.
Can I mix different kinds of hardware and operating systems in a Cluster?
Yes, so long as all machines and operating systems are the same endian. It is also possible to use different MySQL Cluster releases on different nodes (for example, 4.1.8 on some nodes and 4.1.9 on others); however, we recommend this be done only as part of a rolling upgrade procedure.
Can I run two storage nodes on a single host? Two SQL nodes?
Yes, it is possible to do this. In the case of multiple storage nodes, each node must use a different data directory. If you want to run multiple SQL nodes on one machine, then each instance of mysqld must use a different TCP/IP port.
Can I use hostnames with MySQL Cluster?
Yes, it's possible to use DNS and DHCP for cluster hosts. However, if your application requires "five nines" availability, we recommend using fixed IP addresses. This is because making communication between Cluster hosts dependent on such services introduces additional points of failure, and the fewer of these, the better.
This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.