Set Up a MySQL Cluster

What is a MySQL Cluster?

A database is a store for your site's information. A cluster is a group of servers that work together as one. MySQL Cluster spreads data across several machines. If one machine fails, the others keep running. You can also add more machines when you grow. That is what scalable means.

Setting this up is advanced. Try it on test servers first. Take backups of any real data before you move it.

The three parts

• Management node: the boss. It holds the cluster settings and starts the others.
• Data nodes: they store the data. Use at least two so there is a copy.
• SQL nodes: they are the MySQL servers your apps talk to.

A small test setup uses three or four servers, for example one management node, two data nodes and one SQL node. They need to see each other on the network.

Steps

1. Prepare each server with a fixed private IP address. Write down the addresses.
2. Download MySQL Cluster (NDB Cluster) from the official MySQL website. Install the same version on every server.
3. On the management node, make a folder such as /var/lib/mysql-cluster.
4. In that folder, create a file named config.ini:

[ndbd default]
NoOfReplicas=2
DataMemory=256M

[ndb_mgmd]
HostName=10.0.0.1
DataDir=/var/lib/mysql-cluster

[ndbd]
HostName=10.0.0.2
DataDir=/var/lib/mysql-cluster/data

[ndbd]
HostName=10.0.0.3
DataDir=/var/lib/mysql-cluster/data

[mysqld]
HostName=10.0.0.4

This file lists each node and its address. NoOfReplicas=2 keeps two copies of all data. The addresses above are only examples.

5. Start the management node:

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

6. On each data node, create /etc/my.cnf with the management address:

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=10.0.0.1

7. Start the data node program on each data node with ndbd.
8. On the SQL node, use the same my.cnf, then start the MySQL service.
9. On the management node, check the status:

ndb_mgm -e show

All nodes should say they are connected.

Use it

Create tables with the NDB engine so the data lives in the cluster:

CREATE TABLE test (id INT PRIMARY KEY) ENGINE=NDBCLUSTER;

Keep it safe

• Cluster traffic is not encrypted by default. Keep it on a private network.
• Block the cluster ports with a firewall from the public internet.

Quick recap

• A cluster is several servers acting as one database.
• You need a management node, data nodes and SQL nodes.
• Write config.ini, start the nodes, then check with ndb_mgm -e show.
• Use ENGINE=NDBCLUSTER for clustered tables.
• Keep it on a private network.