Linux Admin Zone

MySQL Cluster is used to provide high-availability, high-redundancy for the distributed computing environment. You might know that beginning with MySQL 5.1.24, support for the NDBCLUSTER storage engine was removed from the standard MySQL server binaries built by MySQL. Therefore, here I’m using MySQL Cluster edition instead of MySQL Community edition. I’m using 3 servers (1 Management and 2 data) to setup cluster in CentOS 5.4 Linux box. The steps mentioned here can be used for quickly deploying a cluster and worked out for me but no guarantee that this will work for you, So its always advisable to go through official MySQL guide as well for production environment. In case you are also riding on cloud computing wave, I’ve already blogged way to setup MySQL cluster in Amazon EC2 environment.

1 server will play role of Management server (Example IP: 10.17.250.130) having AMD Opetron 2Ghz with 2 GB of RAM. The load on this server will not be any significant, so a server with low configuration will do the job decently. You can install some monitoring software here to monitor you cluster.

2 data/sql servers (Example IPs: 10.12.248.66, 10.8.75.70) having Intel Xeon 5335 4×2.00GHz with 16 GB of RAM each. These servers will store/replicate data.

I want to say here that we can also set both cluster management service and MySQL service in a single server, even management service with 2 mysql instance in a single box (for more info refer: how to run multiple instances of MySQL in single server) but that’s definitely NOT recommended and apparently have no use here. Please note that I’m using only IP addresses while referencing the hosts, you can use the host name as well if you want.

Step 1. Configuration of Management Server

a) MySQL Cluster rpms are available on download site for RedHat linux (on which CentOS is based), so download the rpms for our servers. I’ve downloaded all rpms except which provides debugging etc.

# cd /usr/src
# wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm/from/ftp://mysql.mirror.rafal.ca/pub/mysql/

.. download all others in same way in /usr/src directory. I’ve downloaded the following rpms:
MySQL-Cluster-gpl-management-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-server-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-tools-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-extra-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-shared-7.0.9-0.rhel5.x86_64.rpm

Now before installing, make sure you should remove any prior installation of MySQL in the box, if any. Then install the rpms:

# rpm -ivh MySQL-Cluster-gpl-*

Installation should go smooth. We need to create config.ini in directory /var/lib/mysql-cluster which will acts are configuration file for cluster. Add following configuration directives in the file. Make sure to write IP addresses etc as per your own environment:

# cd /var/lib/mysql-cluster
# vi config.ini
[NDBD DEFAULT]
NoOfReplicas=2
LockPagesInMainMemory=1
 
DataMemory=12288M
IndexMemory=1536M
 
ODirect=1
 
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
RedoBuffer=32M
# MaxNoOfLocalScans=64
MaxNoOfTables=1024
MaxNofOfOrderedIndexes=256
 
[MYSQLD DEFAULT]
 
[NDB_MGMD DEFAULT]
 
[TCP DEFAULT]
# Managment Server
SendBufferMemory=8M
ReceiveBufferMemory=8M
 
[NDB_MGMD]
# the IP of THIS SERVER
HostName=10.17.250.130
 
[NDBD]
# the IP of the FIRST SERVER (Data Node)
HostName=10.12.248.66
DataDir= /var/lib/mysql-cluster
 
[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=10.8.75.70
DataDir=/var/lib/mysql-cluster
 
[MYSQLD]
[MYSQLD]

Please note that values in DataMemory, IndexMemory variables are based in available physical memory in data nodes, in this case 16 GB. To understand the purpose and ideal value of various variable, please check MySQL cluster documentation.

b) Start the management service mentioning the config file we just created above:

# ndb_mgmd -f /var/lib/mysql-cluster/config.ini 
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.39 ndb-7.0.9b
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- The default config directory '//mysql-cluster' does not exist. Trying to create it...
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- Sucessfully created config directory
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- Reading cluster configuration from '/var/lib/mysql-cluster/config.ini'

Management server configuration is done. We will revisit here after setting up data nodes.

Step 2. Configure data/storage nodes of cluster. You should implement below steps in both of data nodes while taking care of IP address etc.

a) Check that there should be no prior installation of mysql in the server, if there’s any, remove it.

# rpm -qa | grep mysql 
mysql-5.0.77-4.el5_4.1
 
# yum remove mysql

b) Install cluster (copy rpms you downloaded in management node to here). Again, I’ve installed everything just for the sake of ease.

# cd /usr/src
# rpm -ivh MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-extra-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-shared-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-server-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-tools-7.0.9-0.rhel5.x86_64.rpm

c) Time to create my.cnf for MySQL service, this is very basic config file having required entries for Cluster. We have to optimize this as we go forward.

# vi /etc/my.cnf
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
 
ndbcluster
# IP address of the cluster management node
ndb-connectstring=10.17.250.130
default-storage-engine=NDBCLUSTER
 
#max_connections=341
#query_cache_size=16M
#thread_concurrency = 4
 
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=10.17.250.130

d) Start ndbd service with initial setup. The initial parameters needed only first time while you are starting the service to create cluster file system and other things, subsequently we just need to enter ‘ndbd’ to start this.

# ndbd --initial 
2010-02-27 02:40:51 [ndbd] INFO     -- Configuration fetched from '10.17.250.130:1186', generation: 1

e) Start MySQL server now:

# start mysql server
# /etc/init.d/mysql start

f) Dont forget to set password for root user.

# mysqladmin -u root password 'yourpassword'

If there’s any unfortunate event when you forget root password of MySQL, here’s a quick way to reset that.

Repeat Step 2 in second data node as well. After completion of this we have done setup of basic MySQL cluster and now its time to check this out.

Step 3. Testing the cluster and other important things to remember

a) Login to management node again and issue ndb_mgm command to open command prompt where we can issue management commands and check status of cluster by issuing ‘show’ command:

# 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	@10.12.248.66  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=3	@10.8.75.70  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)
 
[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.17.250.130  (mysql-5.1.39 ndb-7.0.9)
 
[mysqld(API)]	2 node(s)
id=4	@10.12.248.66  (mysql-5.1.39 ndb-7.0.9)
id=5	@10.8.75.70  (mysql-5.1.39 ndb-7.0.9)
 
ndb_mgm>

You can see that both ndb nodes are connected, 1 management node is there and then 2 mysqld/api nodes there. You can notice that both of data nodes are referred as ndb and mysqld/api nodes because we are running ndb service there as well as mysql service. This will facilitate that our data node also runs mysql service and we can connect to any of these nodes to manage our data. If you refer MySQL cluster documentation, you can see that they are stressing to have 1 mysqld/api node and then 2 data nodes, I dont see any advantage in that. Due to availability of faster hardware and lots of storage, I prefer to keep both data/api in same machine. This will also help in setting up load balancer later to distribute load among available data/mysqld nodes.

Coming back to ndb management prompt, there are several command we can issue here to check/view most stats. like “ALL REPORT MEMORY” shows memory usage from all machines.

ndb_mgm> all report memory
 
ndb_mgm> Node 2: Data usage is 0%(22 32K pages of total 2560)
Node 2: Index usage is 0%(16 8K pages of total 2336)
Node 3: Data usage is 0%(22 32K pages of total 2560)
Node 3: Index usage is 0%(16 8K pages of total 2336)
 
ndb_mgm>

there are many commands supported here, which can be found on MySQL cluster official documentation.

b) Testing data nodes and create/importing data

As you already know, cluster uses its own stroage engine, therefore you MUST create tables or alter them if you import from backup etc. to NDBCLUSTER storage engine otherwise tables will not replicate in cluster. (here’s solution of one issue where we want to ignore errors coming while backup restore.
)

Login to data node 1 and connect to mysql there:

# mysql -u root -p
 
mysql> create database testdb; 
Query OK, 1 row affected (0.21 sec)
 
mysql> use testdb; 
Database changed
 
mysql> create table t (seq int, name varchar(10)) engine = ndbcluster; 
Query OK, 0 rows affected (0.28 sec)
 
mysql> insert into t values (1,'Mohan'); 
Query OK, 1 row affected (0.00 sec)

Now login to node 2, access mysql and check data:

# mysql -u root -p
 
mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables; 
+------------------+
| Tables_in_testdb |
+------------------+
| t                |
+------------------+
1 row in set (0.03 sec)
 
mysql> select * from t; 
+------+-------+
| seq  | name  |
+------+-------+
|    1 | Mohan |
+------+-------+
1 row in set (0.00 sec)
 
mysql>

Cluster is working as expected. You can start making use of it.

One thing to note down that if you want to change/add new configs in management server, be sure to stop/start the ndb_mgm daemon and also restart ndbd and mysql services in all data nodes.

This setup is very basic and needs lots of optimization before deploying in production. One issue here is that the cluster doesn’t provide any fault tolerance as your application needs to be aware that two mysql nodes are available so in case of one node fails, you must connect to other node in meantime the first node get repaired.

To overcome such issues, and eliminate single point of failure, I’ve to setup 2 load balancer servers with single virtual IP among them to present single point of contact for application. Currently I’m testing that thing and will blog the stuff here very soon, till then stay tuned. Please let me know your valuable suggestions/queries regarding this article.