Friday, December 27, 2013

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 -
SQL nodes -,
Data nodes -,

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 @ (Version: 5.5.34-ndb-7.2.15, Nodegroup: 0, *) 
id=3 @ (Version: 5.5.34-ndb-7.2.15, Nodegroup: 0) 

[ndb_mgmd(MGM)] 1 node(s) 
id=1 @ (Version: 5.5.34-ndb-7.2.15) 

[mysqld(API)] 2 node(s) 
id=4 @ (Version: 5.5.34-ndb-7.2.15)
id=5 @ (Version: 5.5.34-ndb-7.2.15)

This means our cluster setup is successful. :)



  1. I have an error in SQL nodes, when i execute scripts/mysql/mysql_install_db --user=mysql i got this error :
    ./bin/my_print_defaults: 1: ./bin/my_print_defaults: Syntax error :")" unexpected
    Neither host 'ubuntu' nor 'localhost' could be locked with ./bin/resolveip
    Please the configure hostname command to return a correct hostname. If you want to solve this at alater stage , restart this script with --force option

    We try with --force option but install of tables failed
    How can fix this error ?

    1. I have few questions for you.

      1) Can you ping to "ubuntu" and "localhost"?
      2) Do you have "resolveip" inside ./bin/? And is it executable?
      3) Try setting "--basedir" option and see. More info in [1]


  2. For those who get errors on scripts/mysql_install_db - you may have "libaio" packages missing.
    To fix that try the following: sudo apt-get install libaio1 libaio-dev

  3. Thank you for Sharing this tutorial. i followed your tutorial and i am having below error in management node,

    2015-07-29 02:07:59 [MgmtSrvr] ERROR -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line

    Please help me ASAP

    1. In Section 3, when you configure /var/lib/mysql-cluster/config.ini, under each hostname (and datadir) add a new line "NodeId=#" here #=1,2,3...etc.

  4. tested using ubuntu 17.04, and very simple to configure MySQL Cluster on Ubuntu 17.04.

  5. Nice tutorial. How could I access mysql cluser from a python code?