Tuesday, January 6, 2009

A MySQL Cluster in your hand

The MySQL Cluster product seems to intimidate a lot of folks with its complexity. The North Texas MySQL Meetup had a presentation at the January meeting titled 'A Gentle Introduction to MySQL Cluster' where a cluster was setup on a single laptop. The presentation was full of information that needs to be shared to encourage others to try out this valuable piece of software.

The following covers how to set up a cluster on a single computer. The cluster will consist of two data nodes, a SQL node, and a management node. In this case the SQL node and the management node will be on one system. The data nodes will be virtual systems running on the same host. This is not the optimal performance configuration but it will let those new to MySQL Cluster try out the product with a minimal investment in equipment and time.

You will need two free software products -- Virtualbox and MySQl Cluster. You will also need a CDROM/DVD/ISO-image of your favorite supported operating system. In this example Virtualbox was installed on a MacBook and a copy of the Ubuntu desktop ISO was downloaded.

The latest MySQL Cluster binaries is 6.3 as this is written. For those of you not used to Cluster, the version number is out of sync with the MySQL Server GA release number so do not panic that 5.1 was just GA-ed and now 6.3 is on the loose.

The MacBook's IP is 192.168.15.101 in the examples while '103 and '104 are the virtual systems.

Install Virtualbox and then install at least two virtual systems. These two system will be the data nodes. In the example below, Ubuntu was installed, the virtual images were set to use the host's network card, and the software updates for Ubuntu where installed.

A new directory was created, /var/lib/mysql-cluster, and ownership was set to user mysql. Then the MySQL Cluster software binaries were downloaded. The software was installed in /usr/local and the instructions for installing a binary were followed.

Now repeat all that for the second data node and modify the following for your /etc/my.cnf


my.cnf for data node
[mysqld]

ndbcluster
ndb-connectstring=192.168.15.101



[mysql_cluster]

ndb-connectstring=192.168.15.101

Install the MySql Cluster software on your SQL node, in this case the MacBook. Create a directory owned by mysql named /var/lib/mysql-cluster. Grab your favorite my.cnf from a previous installation or use one of the examples from the support-files directory and add the following.

my.cnf excerpt for SQL node
[mysqld]

ndbcluster
ndb-connectstring=192.168.15.101

.

.

.

[ndb_mgmd]

configfile=/var/lib/mysql-cluster/config.ini

You need put the following in you /var/lib/mysql-cluster and match it to your network addresses.

config.ini
[NDB_MGMD]

Id=1

Hostname=192.168.15.101

Datadir=/var/lib/mysql-cluster



[NDBD DEFAULT]

NoOfReplicas=1



[NDBD]

Id=2

Hostname=192.168.15.104



[NDBD]

Id=3

Hostname=192.168.15.103




[MYSQLD]



[MYSQLD]



[MYSQLD]



[MYSQLD]


On the SQL/Management node, run ndb_mgmd and then open a new terminal window to run ndb_mgm. On each of the data nodes, run ndbd --initial (you can drop the --initial after the first successful start) then start mysqld with mysqld_safe --user=mysql&. Back to the SQL Node and start mysqld just like you did on the data nodes. In your window where you left ndb_mgm running, enter show and you should see something like


bash-3.2$ /usr/local/mysql/bin/ndb
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.15.101:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.15.104 (mysql-5.1.30 ndb-6.3.20, Nodegroup: 0, Master)
id=3 @192.168.15.103 (mysql-5.1.30 ndb-6.3.20, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.15.101 (mysql-5.1.30 ndb-6.3.20)

[mysqld(API)] 4 node(s)
id=4 @192.168.15.103 (mysql-5.1.30 ndb-6.3.20)
id=5 @192.168.15.104 (mysql-5.1.30 ndb-6.3.20)
id=6 @192.168.15.101 (mysql-5.1.30 ndb-6.3.20)
id=7 (not connected, accepting connect from any host)


ndb_mgm>

Note that in this case Node #2 is the Master data node and in Nodegroup #0 while Node #3 is in Nodegroup #1. There are lots of good articles online on MySQL Cluster but I really recommend the MySQl 5.1 Cluster Certification Guide as a reference book.

So do not be afraid of the configuration complexity or worry that you need several computer systems with corresponding network gear. Give MySQL Cluster a try!

2 comments:

Raymond Lee said...

Hi dave,

I plan to setup a Mysql cluster as what you desc .

May i know how is the connection for this 3 instance. Are u using NAT/Host interface.

I just got myself screw up the wireless adaptor because i put it in a network bridge and i intent to use the LAN as to outside work and wireless to inside world.

How should it work ?

Dave Stokes said...

Raymond,

By using VirtualBox, it is easy to tell all the instances piggy-back on the host's NIC.

Be careful with the bridging as the cluster traffic has no encryption. And it does consume a lot of bandwidth which can cause contention problems.