Building a MySQL Cluster

I am now building a Mail Handling System for a customer and one of the customer non functional requirement is high availibility. So I made a MySQL cluster. Now I can run at the same time two instances of this Mail Handling System in different sites. Now one site can fail and the other site can still work on. The MySQL cluster software make sure that the data is always stored, available and in sync on two nodes. Off course you can use Oracle Data guard or RAC but this is much easier and a lot cheaper.
I was suprised how easy it is to make a cluster. This blog entry will show you the steps.
I will use three x86 sun solaris 10 servers. Two servers are the storage nodes and one is the management node (The load of the management node is very low so you can easily add this to a existing server).

First I downloaded the MySQL 6.2.15 cluster edition from
http://dev.mysql.com/downloads/cluster/index.html#solaris_tar in my case the solaris software.

I don't use the default mysql folders. This are my mysql folder locations.
mysql folder /mysql/mysql
mysql data folder /mysql/mysql/data
mysql cluster folder /mysql/cluster

And I changed the default mysql parameters because we need to support big xml files.

Just change it or remove these parameters.

First step, installation of the mysql software.

Storage node ac-mhs20 (193.176.63.50)
mysql-ndb-1# mkdir /mysql
mysql-ndb-1# cd /mysql
mysql-ndb-1# put the mysql software in the /mysql folder
mysql-ndb-1# gunzip *.gz
mysql-ndb-1# tar xvf *.tar
mysql-ndb-1# rm *.tar
mysql-ndb-1# ln –s mysql-cluster-gpl-6.2.15-solaris10-i386 mysql
mysql-ndb-1# vi /etc/profile add PATH=$PATH:/mysql/mysql/bin

Let's make the mysql configuration file
mysql-ndb-1# vi /etc/my.cnf

[mysqld]
basedir=/mysql/mysql
datadir=/mysql/mysql/data
max_allowed_packet=64M
key_buffer_size=192M
table_cache=512
sort_buffer_size=12M
read_buffer_size=8M
wait_timeout=172800
interactive=172800

mysql-ndb-1# groupadd mysql
mysql-ndb-1# useradd -g mysql mysql
mysql-ndb-1# cd mysql ( /mysql/mysql )
mysql-ndb-1# scripts/mysql_install_db --user=mysql
mysql-ndb-1# chown -R root .
mysql-ndb-1# chown -R mysql data
mysql-ndb-1# chgrp -R mysql .
mysql-ndb-1# cp support-files/mysql.server /etc/init.d/mysql.server

change the mysqld_safe file located in /mysql/mysql/bin

first change
if test -f ./share/mysql/english/errmsg.sys -a -x ./bin/mysqld
to
if test -f ./share/english/errmsg.sys -a -x ./bin/mysqld

second change
elif test -f ./share/mysql/english/errmsg.sys -a -x ./libexec/mysqld
to
elif test -f ./share/english/errmsg.sys -a -x ./libexec/mysqld

Do the same steps for storage node two
Storage node ac-mhs21 (193.176.63.51)

Now we can put the mysql management software to the management server
Management server ac-mhs22 (193.176.63.52)

mysql-ndb-mgt # mkdir /mysql
mysql-ndb-mgt # cd /mysql
mysql-ndb-mgt # mkdir cluster
mysql-ndb-mgt # cd cluster
mysql-ndb-mgt # ftp or rcp /mysql/mysql/bin/ndb_mgm and ndb_mgmd from storage node A or B to /mysql/cluster folder of the management server
mysql-ndb-mgt # chmod u+x ndb_


Step 2 Add the cluster configuration

Management server ac-mhs22 (193.176.63.52)
Create a new file called config.ini and put this in the /mysql/cluster folder

[NDBD DEFAULT]
NoOfReplicas=2
DataDir=/mysql/cluster
DataMemory=80M
IndexMemory=18M

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]

# Management Server
[NDB_MGMD]
id=1
HostName=193.176.63.52 # IP address of this server

# Storage Nodes
[NDBD]
id=2
HostName=193.176.63.50 # IP address of storage-node-1
DataDir= /mysql/cluster

[NDBD]
id=3
HostName=193.176.63.51 # IP address of storage-node-2
DataDir=/mysql/cluster

[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]

Storage node ac-mhs20 (193.176.63.50)
mysql-ndb-1# vi /etc/my.cnf and add this to it

ndbclusterndb-connectstring='host=193.176.63.52' # IP address of the management server
default-table-type=NDBCLUSTER
[mysql_cluster]ndb-connectstring='host=193.176.63.52' # IP address of the management

Storage node ac-mhs21 (193.176.63.51)
mysql-ndb-2# vi /etc/my.cnf and add this to it

ndbclusterndb-connectstring='host=193.176.63.52' # IP address of the management server
default-table-type=NDBCLUSTER
[mysql_cluster]ndb-connectstring='host=193.176.63.52' # IP address of the management


Step 3 Let's start the cluster

Management server ac-mhs22 (193.176.63.52)
mysql-ndb-mgt # cd /mysql/cluster
mysql-ndb-mgt # ./ndb_mgmd

Storage node ac-mhs20 (193.176.63.50)
mysql-ndb-1# cd /mysql/cluster
mysql-ndb-1# /mysql/mysql/bin/ndbd --initial
mysql-ndb-1# /etc/init.d/mysql.server start

Storage node ac-mhs21 (193.176.63.51)
mysql-ndb-2# cd /mysql/cluster
mysql-ndb-2# /mysql/mysql/bin/ndbd --initial
mysql-ndb-2# /etc/init.d/mysql.server start

Step 4 Check the cluster status

Management server ac-mhs22 (193.176.63.52)
Start the management console

mysql-ndb-mgt # cd /mysql/cluster
mysql-ndb-mgt # ndb_mgm
ndb_mgm> show

Step 5 Create a new database

Storage node ac-mhs20 (193.176.63.50)

mysql-ndb-1# mysql -u root
mysql-ndb-1# create database foo;
mysql-ndb-1# use foo;
mysql-ndb-1# create table test1 ( i int );
mysql-ndb-1# insert into test1 () values (1);

Storage node ac-mhs21 (193.176.63.51)

mysql-ndb-2# mysql -u root
mysql-ndb-2# use foo;
mysql-ndb-2# select * from test1;


That's all the cluster is working

For more info check this great white paper. http://www.lod.com/whitepapers/mysql-cluster-howto.html