Ensuring That MySQL Cluster Works

On all your nodes, you should now check that you have indeed installed support for MySQL Cluster, whether you have installed from a binary or compiled from source. To do this, you issue a SHOW TABLE TYPES command in the MySQL client:

[root@host]# mysql
mysql> show storage engines;
+------------+--------+---------------------------------------------------------
|Engine |Support |Comment
+------------+--------+---------------------------------------------------------
|MyISAM |DEFAULT |Default engine as of MySQL 3.23 with great performance
|MEMORY |YES |Hash based, stored in memory, useful for temporary tables
|HEAP |YES |Alias for MEMORY
|MERGE |YES |Collection of identical MyISAM tables
|MRG_MYISAM |YES |Alias for MERGE
|ISAM |NO |Obsolete storage engine, now replaced by MyISAM
|MRG_ISAM |NO |Obsolete storage engine, now replaced by MERGE
|InnoDB |YES |Supports transactions, row-level locking, and foreign key
|INNOBASE |YES |Alias for INNODB
|BDB |YES |Supports transactions and page-level locking
|BERKELEYDB |YES |Alias for BDB
|NDBCLUSTER |DISABLED |Clustered, fault-tolerant, memory-based tables
|NDB |YES |Alias for NDBCLUSTER
|EXAMPLE |YES |Example storage engine
|ARCHIVE |YES |Archive storage engine
|CSV |YES |CSV storage engine
|FEDERATED |YES |Federated MySQL storage engine
|BLACKHOLE |YES |/dev/null storage engine (anything you write to it disapp
+------------+--------+---------------------------------------------------------
18 rows in set, 1 warning (0.00 sec)

If you have the words NDBCLUSTER and YES or DISABLED on the same line, you are done, and you can continue with the process described in the next section. Otherwise, you need to go back and try to determine what you did wrong: Either you failed to install the MySQL-Max binary, or you failed to add support for ndbcluster in your compile string.

Configuring and Starting a Cluster

After you have installed the relevant packages on each node in your cluster, you need to log in to the server on which you are going to run your management node and start to define your cluster.

First, you need to check whether the directory /var/lib/mysql-cluster exists, and if it does not, you need to create it. Within it, using your favorite text editor, you create a file called config.ini in which you define the nodes in your cluster (this is where all settings go for all nodes). All the other nodes need is a one-line configuration line called a connect string, which goes in my.cnf and tells the nodes where they can find the management node.

You should be aware that you can put config.ini wherever you like. What matters is that when you start the management daemon, you must be in the folder that contains config.ini. Some people therefore put it in /etc/. You still need to create the directory /var/lib/mysql-cluster, which your nodes will use to store data that they need to store to disk.

You can also define your settings within config.ini in my.cnf; however, this is not recommended if you want to keep your cluster configuration away from your database configuration, which in most cases is a good idea.

Editing config.ini

Let's start with the basics. A config.ini file is made up of different sections and looks exactly like a traditional Windows .ini file, with sections separated by titles in square brackets.

There are two separate parts to a config.ini file. First, you define defaults and settings across the cluster in the following sections:

• [NDBD DEFAULT] contains defaults for all storage nodes.
• [TCP DEFAULT] contains networking settings for all nodes (ports and so on).
• [MYSQLD DEFAULT] contains defaults for all SQL nodes.
• [NDB_MGMD DEFAULT] contains defaults for all management nodes (typically, you only have one management node, so this is rather pointless).

Then you specify the settings for each particular node, with one section per node:

[NDB_MGMD] Management node
[NDBD] Storage node
[MYSQLD] SQL node

You'll learn all the different options that you can put in each section in Chapter2, "Configuration". At this point, we look at the settings you must define to get your cluster working. Next, we cover each section of the config.ini file.

[NDBD_DEFAULT]

NoOfReplicas defines the number of copies of each piece of data the cluster will hold. Clearly, if you want redundancy, NoOfReplicas must be greater than or equal to 2. If you have a fixed number of storage nodes, it is worth remembering that your total number of storage nodes should be a power of 2 and a multiple of NoOfReplicasso if you set NoOfReplicas to 2, you should have 2, 4, or 8 (and so on) storage nodes. This is a not mandatory (your cluster will still work if you don't) but will gain you significant increases in performance due to the method of partitioning that the current versions of MySQL Cluster (4.1 and 5.0) use. It is expected that this will become less important in MySQL Cluster 5.1. Note that there is an exception: If NumberOfReplicas is equal to the total number of storage nodes, that is fine (even if the total number of storage nodes is not a power of 2).

NoOfReplicas also specifies the size of node groups. A node group is a set of nodes that all store the same information. Node groups are formed implicitly. The first node group is formed by the set of data nodes with the lowest node IDs, the next node group by the set of the next-lowest node identities, and so on. It is important to configure the cluster in such a manner that nodes in the same node groups are not placed on the same computer. (You will see how to do this in the next section.) In this situation, a single hardware failure would cause the entire cluster to crash. (This book's introduction provides a graphical illustration and further description of this concept.)

DataDir defines the folder that the node will use when it flushes its data to disk. This data on disk is used to recover from a cluster crash where no node in one or more node groups is left alive. We strongly suggest that you set this to /var/lib/mysql-cluster.

[NDB_MGMD](Management), [NDBD](Storage), and [MYSQLD](SQL)

You must define at least one management node, one SQL node, and two storage nodes in order for your cluster to work. All three of the sections [NDB_MGMD], [NDBD], and [MYSQLD] require the parameter Hostname, which defines either the hostname or the IP address of the node. We strongly suggest that you use IP addresses, and when troubleshooting, you should certainly use IP addresses. Many problems are caused by using hostnames unless both the forward and reverse DNS are in perfect order, which on many hosts they are not. If you use hostnames, you should try to define them in /etc/hosts to reduce your reliance on DNS lookups.

An Example of a config.ini File

If you have three separate servers and want to run storage nodes on two of them, SQL nodes on all three, and the management node on the server that does not have a storage node, you produce a configuration like this, where 10.0.0.1, 10.0.0.2, and 10.0.0.3 are the IP addresses of the servers:

[NDBD DEFAULT]
NoOfReplicas=2
DataDir= /var/lib/mysql-cluster
# Management Node
[NDB_MGMD]
HostName=10.0.0.1
DataDir= /var/lib/mysql-cluster

# Storage Nodes
# One entry for each node
[NDBD]
HostName=10.0.0.2
[NDBD]
HostName=10.0.0.3

# SQL Nodes
# One entry for each node
[MYSQLD]
HostName=10.0.0.1
[MYSQLD]
HostName=10.0.0.2
[MYSQLD]
HostName=10.0.0.3

We use this sample configuration for the rest of this chapter, and if you are able to get your hands on three servers, we strongly recommend that you start out using this configuration.

Note

We use the same three IP addresses for the rest of this chapter, so if you have different ones on your servers, we recommend that you now write down a list of the IP addresses in the book (10.0.0.1, 10.0.0.2, and 10.0.0.3) and the IP addresses of your servers. You can then just substitute the former for the latter each time that you want to try something.

Of course, if you do have three completely spare servers, you can actually set their IP addresses to 10.0.0.1, 10.0.0.2, and 10.0.0.3, and then you will avoid any chance of confusion when following the examples in this book.

You should now be able to produce a simple cluster with a slightly different format than shown in the preceding exampleperhaps with three replicas and three storage nodes[2] or two replicas and four storage nodes. However you want to set up your first cluster, write out your config.ini file and check it before advancing to the next step.

[2] All you would have to in do this example is change NumberOfReplicass to 3 and add another [NDBD] section with HostName=10.0.0.1 in it.

You only need to define ID if you are planning on running multiple storage nodes on the same physical machines. If you do not define this parameter, the management daemon will assign IDs in the order in which they connect.

If you define IDs for storage nodes, it is good practice to also define an ID for the management node, typically 1. You do this by including an ID line in the [NDB_MGMD] section.

You can choose a number between 1 and 64 for this parameter; node groups are formed from consecutive IDs, so if you have four storage nodes and NumberOfReplicas is equal to 2 (that is, each node group contains two nodes), the first two IDs will form Node Group 1, and the second two will form Node Group 2.

If you have two physical servers, you need to make sure that you define the IDs such that storage nodes on the same physical machine are in different node groups, as in this example:

Server 1, First Storage node: ID # 2 (nodegroup 1)
Server 1, Second Storage node: ID # 4 (nodegroup 2)
Server 2, First Storage node: ID # 3 (nodegroup 1)
Server 2, Second Storage node: ID # 5 (nodegroup 2)

This means that Server 1 has a node in both node groups on it, which is good because each node group holds only 50% of the data, so you need members of both node groups to actually have a complete set of data in the cluster. This means that a failure of either server does not remove all nodes in one node group, so your cluster can survive.

Starting a Management Node

You now need to start the management daemon. This is the program that runs in the background and controls your cluster.

To start the management daemon, you log in as root to the server you want to run your management node, change directory to /var/lib/mysql-cluster, and start the ndb_mgmd daemon:

[user@mgm] su -
[root@mgm] cd /var/lib/mysql-cluster
[root@mgm] ndb_mgmd

Alternatively, you can start the ndb_mgmd daemon from any folder and pass the location of the configuration file to the daemon:

[root@mgm] ndb_mgmd -f /var/lib/mysql-cluster/config.ini

Note that this example assumes that you have the MySQL bin directory in your path; if you do not, you need to add /usr/local/mysql/bin/ (or wherever you installed your binaries) in front of ndb_mgmd and in front of every other MySQL binary in this book. Refer to the end of section "Installing MySQL Cluster from a Binary Tarball," earlier in this chapter, for instructions on how to add to your path.

If the output from the ndb_mgmd daemon is nothing at all, you are okay. If you get an error, you almost certainly made a mistake in your config.ini file, so you need to go back and compare it with the preceding example, and if you are still stuck, refer to Chapter2, which explains exactly what to put where in a config.ini file.

The next step is to enter the management console, which is the client to the management noderather like the mysql binary is a client to the MySQL server. The management console is called ndb_mgm. You log in to the management node and issue the command SHOW, which asks the management daemon to send it details about the current status of the cluster:

[user@mgm] ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: 10.0.0.1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 10.0.0.2)
id=3 (not connected, accepting connect from 10.0.0.3)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.1 (Version: 5.0.11)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 10.0.0.1)
id=5 (not connected, accepting connect from 10.0.0.2)
id=6 (not connected, accepting connect from 10.0.0.3)

If, on the other hand, you get an error like this when you attempt to use SHOW, your management daemon has failed to start properly:

ndb_mgm> SHOW
Unable to connect with connect string: nodeid=0,127.0.0.1:1186

If this is what you get, you should turn to Chapter6, "Troubleshooting," for ideas on solving this sort of issue.

Starting Storage Nodes

In order to get your cluster to work, you need to connect your storage nodes. The process is identical on all storage nodes, so you can complete it with one and then repeat the same process for all your other storage nodes. Essentially, all you need to do is tell the daemon where to find the management server and then start the ndbd daemon (which is the storage daemon).

To tell the daemon where to find the management server, you can either put some lines in /etc/my.cnf or you can pass it as a command-line parameter. We suggest that you use my.cnf, but we illustrate both methods here. Note that it is not a bad idea to add these lines to /etc/my.cnf on the management server as well, even if it is not acting as a storage node. If you do not, it will just connect to the local machine (127.0.0.1), which normally works (we relied on it working earlier), but when it becomes possible to bind the management daemon to one IP address, you may no longer be able to rely on it working without an IP address (even if it is just the primary IP of the local machine) in my.cnf.

To use my.cnf, you create the file /etc/my.cnf, if it does not already exist, and add the following:

[mysql_cluster]
ndb-connectstring=10.0.0.1

Then you need to start the storage node daemon, ndbd:

[root@storage] ndbd --initial

This should return no output if it works.

The other way to do it does not involve a my.cnf file. When you start ndbd, you just pass the connect string to it, like this:

[root@storage] ndbd --connect-string=10.0.0.1 --initial

The only way to tell if this method works is to return to the management console (by using ndb_mgm on the management server) and see if the status has changed. If you do this after starting one storage node, you get output like this:

ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 10.0.0.2)
id=3 @10.0.0.3 (Version: 5.0.10, starting, Nodegroup: 0, Master)

If it has not changed and you still get all lines reporting "not connected," then something has gone wrong. Refer to Chapter6 for further help.

When you have all your storage nodes started, you should see that the output from SHOW in ndb_mgm changes to Started:

ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @10.0.0.2 (Version: 5.0.11, Nodegroup: 0, Master)
id=3 @10.0.0.3 (Version: 5.0.11, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.1 (Version: 5.0.11)

This means that your cluster is working. However, you need to connect at least one SQL node before you can start using it.

Connecting SQL Nodes

The requirement for a SQL node is that it must have MySQL-Max rather than just MySQL. Apart from that, you can use any recent version of MySQL to connect to your new cluster. All you need to do is add two lines to the [mysqld] section of my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=10.0.0.1

In this example, 10.0.0.1 is the IP address or hostname (not recommended) of your management node.

You then simply have to restart MySQL, with a command such as service mysql restart.

If MySQL fails to come up, you should check the error logtypically /var/lib/mysql/hostname.err. If you have an error such as the following:

[ERROR] /usr/sbin/mysqld: unknown variable 'ndb-connectstring=10.0.0.1'

you do not have the MySQL-Max package installed or you did not include support for NDB during your compilation process if you compiled from source. Refer to the earlier section in this chapter "Installing MySQL-Max" for further advice.

An Example of a Working Cluster

If you have created the config.ini file described in this chapter, with three servers and two storage nodes, one management node, and three SQL nodes, you should get the following output from a SHOW command:

[user@mgm]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Connected to Management Server at: 10.0.0.1:1186
Cluster Configuration
---------------------

[ndbd(NDB)] 2 node(s)
id=2 @10.0.0.2 (Version: 5.0.11, Nodegroup: 0, Master)
id=3 @10.0.0.3 (Version: 5.0.11, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.0.1 (Version: 5.0.11)

[mysqld(API)] 3 node(s)
id=4 @10.0.0.1 (Version: 5.0.11)
id=5 @10.0.0.2 (Version: 5.0.11)
id=6 @10.0.0.3 (Version: 5.0.11)

Notice how the storage nodes (called "NDB nodes" in the management client) have a Nodegroup parameter. Both storage nodes are in Node Group 0; you should have been expecting this because you have NumberOfReplicas defined as two, and you have two storage nodes; therefore, you have one node group. If you added another two storage nodes, you would have two node groups.

Creating a Table

Now that you have MySQL Cluster working, you can start to create some tables. To do so, you log in to the MySQL client on any one of the SQL nodes (using your mysql root user and password if you have created one), and then you create a database and table and insert and select a row:

[user@any]# mysql
mysql> create database clustertest;
Query OK, 1 row affected (0.03 sec)
mysql> use clustertest;
Database changed
mysql> create table ctest (i INT) ENGINE = NDBCLUSTER;
Query OK, 0 rows affected (4.27 sec)

mysql> INSERT into ctest () VALUES (1);
Query OK, 1 row affected (0.63 sec)

mysql> SELECT i from ctest;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.05 sec)

You can see that this has all worked. If the cluster is not actually working properly, you may get an error such as ERROR 1015 (HY000): Can't lock file (errno: 4009) or ERROR 1050 (42S01): Table 'ctest' already exists while attempting to create the table or while selecting or inserting the row. In this case, you need to go back to ndb_mgm and ensure that all nodes in the cluster are correctly started, referring to Chapter6 for help, if needed.

The final thing you are likely to want to check is that all your SQL nodes are actually working, so if you insert one row on one node, it does appear on all the others. You can do this by logging in to each storage node and inserting another row before selecting all rows. However, before you try to do this, you should read the next section on auto-discovery of databases.