How to Setup a MySQL Cluster in Ubuntu

A MySQL cluster has 3 types of nodes. Those are Management node (ndb_mgmd), SQL nodes (mysqld) and Data nodes (ndbd). The terms inside brackets are the names of servers that should be installed in each type of node. 

And we can use a management client for management node to monitor the status of the cluster. For that, it is recommended to use the management client (ndb_mgm) on the management server host.

1) Introduction to Node Types

Management Node

The management server is the process that reads the cluster configuration file and distributes this information to all nodes in the cluster that request it.

SQL Nodes 

SQL nodes are through which SQL clients connect to MySQL cluster.

Data Nodes

Data nodes handle all the data in tables using the NDB Cluster storage engine. They are responsible for distributed transaction handling, node recovery, checkpointing to disk, online backup etc.

In this post, we will be setting up a MySQL cluster with 5 nodes. (1 Management node, 2 SQL nodes and 2 Data nodes)

IPs of each node is as follows.

Management Node - 1.1.1.5

SQL nodes - 1.1.1.6, 1.1.1.7

Data nodes - 1.1.1.8, 1.1.1.9

2) Cluster Installation

First, download MySQL cluster binary distribution from here and extract it to each node's /var/tmp directory.

> cd /var/tmp 

> sudo tar -zxvf mysql-5.5.34-ndb-7.2.15-linux2.6-i686.tar.gz

Management Node

Go to extracted location and copy ndb_mgm and ndb_mgmd into a suitable directory such as /usr/local/bin.

> cd /var/tmp/mysql-5.5.34-ndb-7.2.15-linux2.6-i686

> sudo cp bin/ndb_mgm* /usr/local/bin

(You can safely delete the directory created by unpacking the downloaded archive, and the files it contains, from/var/tmp once ndb_mgm and ndb_mgmd have been copied to the executables' directory.)

Change location to the directory into which you copied the files, and then make both of them executable.

> cd /usr/local/bin

> sudo chmod +x ndb_mgm*

SQL Nodes

Following steps should be done in all SQL nodes. 

Check your /etc/passwd and /etc/group files to see whether there is already a mysql group and mysql user on the system. If they are not already present, create a new mysql user group, and then add a mysql user to this group.

> sudo groupadd mysql 

> sudo useradd -g mysql mysql 

Change location to the directory containing the archive, and create a symbolic link namedmysql to the mysql directory. 

> cd /var/tmp

> sudo ln -s mysql-5.5.34-ndb-7.2.15-linux2.6-i686 /usr/local/mysql 

Change location to the mysql directory and run the supplied script for creating the system databases.

> cd /usr/local/mysql 

> scripts/mysql_install_db --user=mysql 

Set the necessary permissions for the MySQL server and data directories. 

> sudo chown -R root . 

> sudo chown -R mysql data

> sudo chgrp -R mysql . 

Copy the MySQL startup script to the appropriate directory, make it executable, and set it to start when the operating system is booted up.

> cp support-files/mysql.server /etc/init.d/

> sudo chmod +x /etc/init.d/mysql.server

> sudo chkconfig --add mysql.server 

Data Nodes

Data Nodes require executable ndbd (single-threaded) or ndbmtd (multi-threaded) only.

Do the following steps on all Data nodes.

Go to extracted location and copy ndbd and  ndbmtd to a suitable path. 

> cd /var/tmp/mysql-5.5.34-ndb-7.2.15-linux2.6-i686

> cp bin/ndbd /usr/local/bin/ndbd

> cp bin/ndbmtd /usr/local/bin/ndbmtd 

(You can safely delete the directory created by unpacking the downloaded archive, and the files it contains, from/var/tmp once ndbd and ndbmtd have been copied to the executables' directory.)

Change location to the directory into which you copied the files, and then make both of them executable.

> cd /usr/local/bin 

> chmod +x ndb*

3) Cluster Configurations

Management Node

Create the directory in which the configuration file can be found and then create the file (config.ini) itself.

> sudo mkdir /var/lib/mysql-cluster

> cd /var/lib/mysql-cluster

> sudo vi config.ini 

SQL Nodes & Data Nodes

For these nodes, there should be my.cnf file configured in /etc/my.cnf.

> sudo vi /etc/my.cnf

4) Starting the Cluster 

The management node should be started first, followed by the data nodes, and then finally by SQL nodes.

Management Node

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

Note: Configuration file should be pointed (using -f ) in the initial start up only.   

Data Nodes

> ndbd

SQL Nodes 

> sudo service mysql.server start

4) Checking Cluster Status

For this we use Management Client (ndb_mgm), which we already have in Management node.

> ndb_mgm 

-- NDB Cluster -- Management Client -- 

ndb_mgm> SHOW 

Connected to Management Server at: localhost:1186 

Cluster Configuration 

--------------------- 

[ndbd(NDB)] 2 node(s) 

id=2 @1.1.1.8 (Version: 5.5.34-ndb-7.2.15, Nodegroup: 0, *) 

id=3 @1.1.1.9 (Version: 5.5.34-ndb-7.2.15, Nodegroup: 0) 

[ndb_mgmd(MGM)] 1 node(s) 

id=1 @1.1.1.5 (Version: 5.5.34-ndb-7.2.15) 

[mysqld(API)] 2 node(s) 

id=4 @1.1.1.6 (Version: 5.5.34-ndb-7.2.15)

id=5 @1.1.1.7 (Version: 5.5.34-ndb-7.2.15)

This means our cluster setup is successful. :)

References:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html