MySQL Cluster Architecture, Configuration, and Deployment Guide

MySQL Cluster is a technology that applies an in‑memory database cluster in a shared‑nothing architecture, allowing systems to achieve high scalability on inexpensive hardware.

The cluster is designed as a distributed system with no single point of failure; each component has its own memory and disk, and shared storage solutions such as NFS or SAN are not supported, giving an advertised availability of 99.999%.

In practice, MySQL Cluster integrates the NDB memory‑based storage engine with the standard MySQL server. A typical deployment includes a MySQL server, one or more data nodes, a management server, and a proprietary data access program (API node). The relationship among these components is illustrated in the diagram below:

Storage Engine

MySQL Cluster uses a dedicated in‑memory storage engine (NDB), which provides fast access without disk I/O bottlenecks. Because it is memory‑based, the database size is limited by the total RAM of the cluster, requiring each MySQL server running NDB to have sufficient memory (e.g., 4 GB, 8 GB, or 16 GB). The NDB engine is distributed and can be configured on multiple servers to achieve reliability and scalability; with two storage nodes, the cluster can provide redundancy and eliminate single‑point‑of‑failure issues.

The engine has the following drawbacks:

Database size is limited by the total memory of the cluster.

Power loss may cause data loss unless proper persistence is verified.

Overall performance depends on network speed because nodes communicate over the network.

In some scenarios the speed can be slower than disk‑based engines.

However, it also offers several advantages:

Nodes can be distributed across different geographic locations, providing a true distributed database solution.

Scalability is excellent; adding nodes expands the cluster.

Redundancy is strong; each node holds a complete copy of the data, so any single node failure does not interrupt service.

High availability can be achieved at lower cost compared to traditional HA solutions that require shared storage and specialized software; sufficient memory alone is enough for NDB.

Architecture

MySQL Cluster consists of three different types of services, each provided by a dedicated daemon process (node):

The management (MGM) node – used for cluster management; typically only one instance is started, and its failure does not affect the cluster’s operation. It runs via ndb_mgmd using a config.ini file.

The storage or database (DB) node – stores the data. At least one DB node is required; two or more provide high‑availability. It is started with ndbd, using the --initial flag on the first start, e.g.,

bin/ndbd –ndb-connectstring=ndb_mgmd.mysqlcluster.net –initial

.

The client (API) node – a regular mysqld process that accesses the NDB storage engine. Adding API nodes increases concurrent access and overall throughput. API nodes can run on web servers or dedicated machines and may even share a host with DB nodes.

These three node types can be distributed across different hosts; more API nodes generally improve cluster performance.

MySQL Cluster Exploration and Practice

1. Prepare three machines and download the latest MySQL Cluster source (e.g., mysql-cluster-gpl-7.1.5.tar.gz) with the build option --with-plugins=innobase,ndbcluster.

2. Assign IP addresses:

Management node (ndb_mgmd): 192.168.207.153

Data node (ndbd): 192.168.208.3

Data node (ndbd): 192.168.208.9

SQL node (mysqld): 192.168.208.3

SQL node (mysqld): 192.168.208.9

3. Create a config.ini file under a mysql-cluster directory:

[NDBD DEFAULT]
NoOfReplicas=2       # two data nodes replicate each other
DataMemory=200M
IndexMemory=100M
[TCP DEFAULT]
portnumber=2202
[NDB_MGMD]
id=1
hostname=192.168.207.153
datadir=/home/taozi/mysql/mysql-cluster
[NDBD]
id=2
hostname=192.168.208.3
datadir=/home/taozi/mysql/data
[NDBD]
id=3
hostname=192.168.208.9
datadir=/home/taozi/mysql/data
[MySQLD]
id=4
hostname=192.168.208.3
[MySQLD]
id=5
hostname=192.168.208.9
[MySQLD]
id=6

4. Start the management node on its server:

~/mysql/bin/ndb_mgmd -f ~/mysql/mysql-cluster/config.ini

5. On each data node server, start the data node service (first time use --initial):

~/mysql/bin/ndbd          # first start: ~/mysql/bin/ndbd --initial

6. On each SQL node, edit my.cnf to include:

[MYSQL_CLUSTER]
ndb-connectstring=192.168.207.153
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.207.153

7. Start the MySQL service on the SQL nodes:

/home/taozi/mysql/bin/mysqld_safe --ledir=/home/taozi/mysql/bin \
--log-error=/home/taozi/mysql/data/t.err --datadir=/home/taozi/mysql/data \
--socket=/home/taozi/mysql/tmp/mysql.sock --pid-file=/home/taozi/mysql/data/mysqld.pid &

8. Return to the management node and verify the cluster status: ~/mysql/bin/ndb_mgm -e show The output shows the management node, two data nodes, and three API nodes (one may be not connected yet).

9. On an SQL node, create a test table:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=gbk;

Data will be automatically synchronized across the data nodes; you can verify the files under ~/mysql/data/ndb_2_fs and ~/mysql/data/ndb_3_fs or query the database directly.

10. To shut down the cluster, stop the SQL nodes (stop MySQL), then stop the management node with:

~/mysql/bin/ndb_mgm -e shutdown
rm ~/mysql/mysql-cluster/ndb_1_config.bin.1   # optional, needed if config.ini changes

Notes:

1. If shutting down one ndbd process also stops the other, adjust NoOfReplicas.
2. Do not run "ndbd --initial" on all data nodes simultaneously; it would erase data.
3. SQL nodes can be used like non‑clustered MySQL instances.
4. After modifying config.ini, delete the binary config file before restarting ndb_mgmd.
5. NDB Cluster does not support automatic database discovery; after creating a database on one data node, issue CREATE DATABASE on each SQL node.
6. Check logs in ~/mysql/mysql-cluster for errors during startup.
7. In config.ini, the order of options under [mysqld], [ndbd], and [ndb_mgmd] matters; Id must be followed by HostName.
8. Empty nodes (no HostName) can be used for dynamic IPs; reserve at least three empty nodes for backup connections.