CentOS 6: Install MySQL Cluster – The Simple Way

In this post I am going to install a MySQL cluster as refer to the architecture as below:

 

 

MySQL cluster is consists of 3 types of node:

  • Data node (mysql-data1 & mysql-data2)
  • Sql daemon node (mysql-mysqld1 & mysql-mysqld2)
  • Management node (mysql-management)

Data node will hold the database and it will replicated automatically to all data nodes. Sql daemon node is the interface between database and client. It will serve the query they got from data nodes, similar like a “gateway”. Management node is required in order to monitor and manage the whole cluster. Recommended minimum setup for high availability and scalability will be 5 servers as what I have highlights in the picture above. I will be  using CentOS 6.3 64bit for all servers.

All Servers

1. SELINUX must be disabled on all server. Change the SELINUX configuration file at /etc/sysconfig/selinux:

SELINUX=disabled

2. Firewall is disabled on all servers:

$ service iptables stop
$ chkconfig iptables off
$ setenforce 0

3. Entries under /etc/hosts for all servers should be as below:

web-server         192.168.1.21
mysql-mysqld1      192.168.1.51 
mysql-mysqld2      192.168.1.52
mysql-management   192.168.1.53
mysql-data1        192.168.1.54
mysql-data2        192.168.1.55

Management Node

1. Download and install MySQL Cluster (management & tools) package from here:

$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-management-7.0.35-1.rhel5.x86_64.rpm
$ rpm -Uhv MySQL-Cluster-gpl-tools-7.0.34-1.rhel5.x86_64.rpm

2. Create the mysql-cluster directory and configuration file config.ini:

$ mkdir -p /var/lib/mysql-cluster
$ vim /var/lib/mysql-cluster/config.ini

And add following line:

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster
 
[ndb_mgmd]
HostName=mysql-management
 
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster
 
[ndbd]
HostName=mysql-data1
 
[ndbd]
HostName=mysql-data2
 
[mysqld]
HostName=mysql-mysqld1
 
[mysqld]
HostName=mysql-mysqld2

Data Nodes

1. Following steps should be executed on both data nodes (mysql-data1 and mysql-data2). Download and install the MySQL storage package from here:

$ cd /usr/local/src
$ wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm 
$ rpm -Uhv MySQL-Cluster-gpl-storage-7.0.35-1.rhel5.x86_64.rpm

2. Add following line under /etc/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=mysql-management
 
[mysql_cluster]
ndb-connectstring=mysql-management

SQL Nodes

1. Following steps should be executed on both SQL nodes (mysql-mysqld1 and mysql-mysqld2). Remove mysql-libs using yum:

$ yum remove mysql-libs -y

2. Install required package using yum:

$ yum install libaio -y

3. Download the MySQL client, shared and server package from MySQL download site here:

$ cd /usr/local/src
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-client-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/
$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-shared-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/wget http://www.mysql.com/get/Downloads/MySQL-Cluster-7.2/MySQL-Cluster-server-gpl-7.2.8-1.el6.x86_64.rpm/from/http://cdn.mysql.com/

4. Install all packages:

$ rpm -Uhv MySQL-Cluster-*

5. Add following line into /etc/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=mysql-management
default_storage_engine=ndbcluster
 
[mysql_cluster]
ndb-connectstring=mysql-management

Start the Cluster

1. To start the cluster, we must follow this order:

Management Node > Data Node > SQL Node

2. So, login to management node (mysql-management) and execute following command:

$ ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.63 ndb-7.0.35
2012-11-22 07:36:55 [MgmtSrvr] INFO -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2012-11-22 07:36:55 [MgmtSrvr] INFO -- Sucessfully created config directory

3.  Next is start the ndbd service in Data Node (mysql-data1):

$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 2

4. Next is start the ndbd service in Data Node (mysql-data2):

$ ndbd
2012-11-22 07:37:24 [ndbd] INFO -- Angel connected to 'mysql-management:1186'
2012-11-22 07:37:24 [ndbd] INFO -- Angel allocated nodeid: 3

5. Next is start the mysql service in SQL node (mysql-mysqld1):

$ service mysql start

6. Next is start the mysql service in SQL node (mysql-mysqld2):

$ service mysql start

Monitor the Cluster

Monitoring the cluster will required you to login into management server. To check overall status of cluster:

$ ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.1.54 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0, Master)
id=3 @192.168.1.55 (mysql-5.1.63 ndb-7.0.35, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.53 (mysql-5.1.63 ndb-7.0.35)
 
[mysqld(API)] 2 node(s)
id=4 @192.168.1.51 (mysql-5.5.27 ndb-7.2.8)
id=5 @192.168.1.52 (mysql-5.5.27 ndb-7.2.8)

To check the Data nodes status:

$ ndb_mgm -e "all status"
Connected to Management Server at: localhost:1186
Node 2: started (mysql-5.1.63 ndb-7.0.35)
Node 3: started (mysql-5.1.63 ndb-7.0.35)

To check the memory usage of data nodes:

$ ndb_mgm -e "all report memory"
Connected to Management Server at: localhost:1186
Node 2: Data usage is 0%(23 32K pages of total 8192)
Node 2: Index usage is 0%(20 8K pages of total 16416)
Node 3: Data usage is 0%(23 32K pages of total 8192)
Node 3: Index usage is 0%(20 8K pages of total 16416)

Stopping the Cluster

1. To stop the cluster, we must follow this order:

SQL Node > Management Node / Data Node

2. Login to SQL node (mysql-mysqld1mysql-mysqld2) and run following command:

$ service mysql stop

3. Login to management node (mysql-management) and run following command:

$ ndb_mgm -e shutdown

 

Done! You should now able to create or import database on one of the SQL node. You can put a load balancer in front of the SQL node to take advantage on the performance and high availability.

Notes

You may notice that the distribution version I installed is “rhel5”. You can get the “el6” distribution package at this page: http://mirror.services.wisc.edu/mysql/Downloads/MySQL-Cluster-7.2/ and search for any “el6” package name.

25 thoughts on “CentOS 6: Install MySQL Cluster – The Simple Way

  1. Excellent post! I was just setting up cluster on CentOS, and this tutorial was much more effective than anything on the MySQL site. Thank you!

    Reply

  2. Excellent tutorial, definitely bookmarking. Just a little typo in the (MY)SQL node section, you said:

    1. Following steps should be executed on both data nodes (mysql-mysqld1 and mysql-mysqld2). Remove mysql-libs using yum:

    Should be SQL nodes not data nodes

    Reply

  3. Hey,

    Thanks for such a great article! Very helpful!

    I’m experiencing a problem here… my Mysql API always shows not connected. Any idea why? I have followed few tutorials and always get the same result:

    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @184.82.104.197 (mysql-5.1.67 ndb-7.1.26, starting, Nodegroup: 0)
    id=3 (not connected, accepting connect from mysql-data2.zzzvideos.com)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @184.82.104.194 (mysql-5.1.67 ndb-7.1.26)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from mysql-mysqld1.zzzvideos.com)
    id=5 (not connected, accepting connect from mysql-mysqld2.zzzvideos.com)

    Reply

    1. Do you see any error when starting ndbd? Have the firewall and SElinux turned off? Why one of your ndbd (id=2) is not connected to mgmd?

      Reply

    2. I run into this same problem too. I’ll get the ndbd connected but never been able to get the mysqld API connected.

      Reply

  4. hii i do all configutration like yours but i have see this error when i want to start cluster ,

    MgmtSrvr] ERROR –at line 23: unable to lookup/illegal hostname datanode1.sistem.org [TCP] starting at line 22

    Reply

    1. You need to specify the host definition in /etc/hosts in all servers. Example definition as below:
      192.168.100.101 datanode1.sistem.org
      192.168.100.102 datanode2.sistem.org

      Remember to copy this file into all hosts!

      Reply

  5. hii guys i do evertyıng same buti have take error when i want to start cluster;
    [mgmtsrvr] ERROR at line 23 : unable to lookup/illegal hostname datanode1.sistem.org [TCP] starting at line :22

    Reply

    1. i install dns server and it sloved than the cluster run succesfull thanks bro a lot 🙂

      Reply

      1. Yes, name definition is important. If your cluster is in local n do not have many hosts, you can just use /etc/hosts instead of setup a new DNS server for this purpose.

        Reply

    1. mysql-server is not a package from MySQL Cluster. The package name should be MySQL-Cluster-server instead.

      Reply

  6. Thank you very much for your post. However, my questions are:

    1. Is it necessary to have mysql-mysqld1 192.168.1.51, mysql-mysqld2 192.168.1.52, or just the nodes mysql data1 and mysql data 2. I want to have 2 nodes servers and one management node.

    2. Can you provide me a link or explanation how to install and configure Load balancer 1 and load balancer 2 to work with the management node?

    Thank you in advance for your assistance.

    Reply

    1. Hi Ravi,

      1. Data nodes will consume a lot of RAM, that is for sure. Mysqld+management node do not consume a lot of resources (compare to data) so they could run co-located with each other. If you are going to deploy for a test environment, you could use 2 nodes (data + mysqld) and 1 management. You will notice there will be some “racing” on host resources between data and mysqld processes.

      2. Did you mean you want to co-locate lb1 and lb2 within management node? How you are going to achieve HA for that if the node is down?

      Reply

  7. Hey there. Great tutorial. My question is – why would you separate the SQL Daemon Node from the SQL Data Node? What’s the reasoning behind that?

    Reply

  8. hello guys;
    i have installed mysql-cluster and it work true .but i want to
    manage mysql-cluster with gui like PHPMYADMIN.
    how can i manage mysql database ?
    Do you know any way?
    thanks…

    Reply

  9. hii guys;
    i created 5 node mysql cluster like yours.but i wanna to ask samething
    in this system which node hold mysql databases .
    i created database and i see mysql databeses , in mysql nodes.
    if mysql databses will be in mysql node, well what will do data nodes?

    Reply

    1. The mysql node is just a “gateway” or API to the data nodes. The database is actually located in the data nodes and is partitioned and replicated transparently.

      Reply

    2. The MySQL node is just an API (gateway) to the data nodes (NDB storage engine). All data are actually stored in data nodes transparently (with auto-partition and replication). So data nodes need to have a better specs compare to others mysql/management nodes. You can actually use NDB API to connect to data nodes directly, bypassing the MySQL node.

      Reply

      1. Any reason why mysqldb can’t be run on the data nodes? Needing 5 systems is basically a pain in the butt. Any way to run the management node on those two nodes as well? Aka, 2 systems overall, with a load balancer on the top? Does the management node need to be in here? Can I just run it somewhere else? What’s its purpose?

        I appreciate the cold instructions, but it leaves alot of questions about how/why/etc that inquiring minds wouldn’t mind knowing about 🙂

        Thanks!

        Reply

        1. For testing purposes, you may co-locate everything into 2 nodes, but you are going to have to use a very big server for that purpose. Take note that MySQL Cluster is memory-based database.

          Management/SQL node – Resource usage is not high so it can be co-located in a single server
          Data node – Resource usage is VERY high, especially RAM, CPU and required fast network. Basically, it is not recommended to co-locate any other cluster role on it

          Minimum number of nodes to run in production should be 4 (2 nodes for SQL+MGM and 2 nodes for Data). I recommend you to have a look at this blog post:
          http://johanandersson.blogspot.com/2012/12/recommended-mysql-cluster-setup.html

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *