Setting up a MySQL NDB cluster with 3 servers

What makes a MySQL cluster?

A MySQL cluster needs at least 1 of each of 3 types of 'nodes', any number of these nodes can be on any machine. So you can run an entire cluster from one server, I in fact do this on virtual machines to do tests on the NDBCLUSTER storage engine. These 3 nodes are as follows:

• Management Node - This is the node that communicates the cluster settings to the other nodes, it also controls backups. It spends a lot of time idle so is a good node to put on a load balancer hardware if you have one. In theory you can actually run a cluster without one once it has started up, but I wouldn't recommend it.
• Data Node - This node holds all the data for the cluster, the more of these you have, the less chance you have of loosing all your data. 2 is a good number to start with as if one fails the other takes over. The workload of database lookups is also shared by these nodes.
• SQL Node - This is the node that does all the query work, for the purposes of this article it is your standard MySQL daemon (compiled with NDB support) but it can be other things including the NDB restore utility.

Any data that you want to be in the cluster has to be in table using the NDBCLUSTER storage engine. This storage engine works a little bit like InnoDB in the fact the it is transactional, but it is probably the most restrictive engine in terms of what you can do with it. For example you can only have 1 autonumber column per table and that has to be on the primary key. Creating a table in the NDBCLUSTER storage engine automagically makes it available for all the nodes to see, the data nodes will internally make sure it is available for all nodes to see.

On to the hardware setup

As stated in the title we need 3 servers to do this, if you have 2 web servers and a load balancer that is a good place to start. You can make the load balancer the management node and each web server can have an SQL node and a data node. As this diagram shows

You will also need to make sure the servers are talking on a high performance switch, network latency can be a real killer for NDB cluster. Many people trying out NDB for the first time also try and site some of the servers in separate data centres, this will not work out well due to the latency and the risk of a “split brain” situation. If you need your data in different geographical locations at the same time MySQL 5.1 has extended the replication facility to work with the NDBCLUSTER storage engine as well.

On the software side

You need MySQL compiled with NDB support, packages for NDB can be picked up at MySQL AB's download site. This should give you a set of utilities and 2 extra daemons, one for use as a management node and one for use as a data node.

Configuration

First of all we need to configure the management node, to do this you should create a file called config.ini and put it somewhere sensible (I personally prefer /var/lib/mysql-cluster but /etc is also appropriate), then populate it with this code:

[NDB_MGMD]
Id=1
Hostname=192.168.0.1
LogDestination=CONSOLE;SYSLOG:facility=syslog;FILE:filename=/var/log/cluster-log
DataDir=/var/lib/mysql-cluster

[NDBD DEFAULT]
# 2 Replicas of the data so if one server fails the other has a copy of its data.
NoOfReplicas=2 
DataDir=/var/lib/mysql-cluster
# Data and Index memories need to be set upon startup 
DataMemory=512MB 
IndexMemory=64MB

# First data node
[NDBD] 
Id=2
Hostname=192.168.0.2

# Second data node
[NDBD]
Id=3
Hostname=192.168.0.3

# First SQL node
[MYSQLD] 
Id=4
Hostname=192.168.0.2
 
# Second SQL node
[MYSQLD] 
Id=5 
Hostname=192.168.0.3

This is basically telling the management node where to find all the other nodes so it can pass on this information when required. Next we need to configure the SQL and data nodes, both of which read their configurations from the standard my.cnf, to which you will need to make the following alterations:

[mysqld] 
ndbcluster 
ndb-connectstring=192.168.0.1
[mysql_cluster] 
ndb-connectstring=192.168.0.1

Starting your new cluster

You are now ready to kickstart your cluster, to do this simply go to the management node and run:

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

NDB comes with a really nifty command line utility called ndb_mgm which you should be able to run from any of the servers and it will tell you the status of the nodes. If you run this now and type “show” you should see one connected node in the ndb_mgmd section. Now you should be ready to start the data nodes. On each of the servers used for data nodes change directory to /var/lib/mysql-cluster/ and run:

ndbd --initial

ndb_mgm will show the node as starting, this may take some time before its ready. If you leave ndb_mgm running it will echo on screen when the data node is ready.

Finally you should be ready to start the SQL nodes, this is simply done by starting MySQL in the normal way. Although please note, SQL nodes will not join the cluster until ALL the data nodes have started fully.

What now?

Join the MySQL cluster mailing list, there are some great people there that will help you out with any problem, no matter how silly you think the question is.

Download my ndb_watch utility, it will alert you if a cluster node fails

Gotchas and workarounds

Making schema changes to an NDBCLUSTER table (that includes TRUNCATE table which basically drops the table and re-creates it) on one SQL node may not be seen correctly on other SQL nodes in MySQL 5.0, this basically comes down to the way that MySQL caches the table schemas and it will give you several weird and wonderful errors. The 'FLUSH TABLES' command is supposed to fix this, but I find certainly in 5.0.27 this doesn't work every time. The most effective way of re-reading the schemas I find is to do a mysqld reload (kill -HUP ).