How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1

Version 1.0
Author: Peter Okupski <okupski [at] widzew [dot] net>
Last edited 06/02/2008

This tutorial is based on Falko Timme's tutorial for MySQL Cluster 5.0. It shows how to configure a MySQL 5.1 cluster with five nodes: 1 x management, 2 x storage nodes and 2 x balancer nodes. This cluster is load-balanced by an Ultra Monkey package which provides heartbeat (for checking if the other node is still alive) and ldirectord (to split up the requests to the nodes of the MySQL cluster).

In this document I use Debian Etch 4.0 for all nodes. Therefore the setup might differ a bit for other distributions. The two data nodes were x64 to use all of the 8GB RAM. Servers were compiled from source so you should be able to make it running on any platform. The MySQL version I use in this setup is 5.1.24-rc. It's a release candidate, but I wanted to use 5.1 to take advantage of Memory-Disk Based tables.

Beginning with MySQL 5.1.6, it is possible to store the non-indexed columns of NDB tables on disk, rather than in RAM as with previous versions of MySQL Cluster.[More here]

This howto is just a scratch to make it running, for many of you I am suggesting to read some off docs from MySQL page to be prepared to how manage the whole cluster and always know whats going on.

This document comes without warranty of any kind! Bare in mind you need to make tests and prepare your databases before using it in production mode.

1 My Servers

I will use the following Debian servers that are all in the same network (10.0.1.x in this example):

• mysql-mngt.example.com:10.0.1.30 [ MySQL cluster management server ]
• lb1.example.com: 10.0.1.31 [ Load Balancer 1 ]
• lb2.example.com: 10.0.1.32 [ Load Balancer 2 ]
• mysql-data1.example.com: 10.0.1.33 [ MySQL cluster node 1 ]
• mysql-data2.example.com: 10.0.1.34 [ MySQL cluster node 2 ]

In addition to that we need a virtual IP address : 10.0.1.10. It will be assigned to the MySQL cluster by the load balancer so that applications have a single IP address to access the cluster.

Although we want to have two MySQL cluster nodes in our MySQL cluster, we still need a third node, the MySQL cluster management server, for mainly one reason: if one of the two MySQL cluster nodes fails, and the management server is not running, then the data on the two cluster nodes will become inconsistent ("split brain"). We also need it for configuring the MySQL cluster.

2 MySQL data nodes + 1 cluster management server + 2 Load Balancers = 5

Here is my hardware configuration:

MySQL Data : DELL R300 Intel(R) Quad Core Xeon(R) CPU X3353 @ 2.66GHz, 2x SAS 146GB Drives (Raid 1), 8GB RAM

MySQL LoadBalancer : DELL R200 Intel(R) Xeon(R) CPU 3065 @ 2.33GHz, 2x SATA 250 GB Drives (Raid 1), 1GB RAM

MySQL Management : DELL R200 Intel(R) Celeron(R) CPU 430 @ 1.80GHz, 1x SATA 160 GB Drives (Raid 1), 1GB RAM

As the MySQL cluster management server does not use many resources, you can put additional load balancer on this machine or you can use it for monitoring the whole cluster by Nagios or Cacti.

2 Set Up The MySQL Cluster Management Server

First we have to download MySQL 5.1.24 (the sources version) and install the cluster management server (ndb_mgmd) and the cluster management client (ndb_mgm - it can be used to monitor what's going on in the cluster). The following steps are carried out on mysql-mngt.example.com (10.0.1.30):

mysql-mngt.example.com:

cd /usr/src
wget http://mysql.mirrors.pair.com/Downloads/MySQL-5.1/mysql-5.1.24-rc.tar.gz
tar xvzf mysql-5.1.24-rc.tar.gz
#Lets add proper user and group
groupadd mysql
useradd -g mysql mysql
./configure --prefix=/usr/local/mysql --enable-community-features \
--with-mysqld-user=mysql --with-mysqlmanager --with-plugins=ndbcluster
make
make install

This way we are wasting about 124MB space since we do not need all the actual MySQL files, but believe me it's way easier to make any cleanup/upgrade in just one dir /usr/local/mysql instead of searching for all files in /usr/bin and so on. After compiling we have two directories we are interested in /usr/local/mysql/bin and /usr/local/mysql/libexec [last one contains the ndb management exec].

Just to make life easier let's add this below to your PATH environment, to do so we have to edit file /root/.bash_profile:

mysql-mngt.example.com:

echo "PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/libexec" >>/root/.bash_profile
echo "export PATH" >>/root/.bash_profile

Next, we must create the cluster configuration file, /usr/local/mysql/var/mysql-cluster/config.ini: [Hints here]

mysql-mngt.example.com:

mkdir /usr/local/mysql/var/mysql-cluster
cd /usr/local/mysql/var/mysql-cluster
vi config.ini

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=10.0.1.30
# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=10.0.1.33
DataDir=/usr/local/mysql/var/mysql-cluster
BackupDataDir=/usr/local/mysql/var/mysql-cluster/backup
DataMemory=2048M
[NDBD]
# IP address of the second storage node
HostName=10.0.1.34
DataDir=/usr/local/mysql/var/mysql-cluster
BackupDataDir=/usr/local/mysql/var/mysql-cluster/backup
DataMemory=2048M

# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]

Please replace the IP addresses in the file appropriately to your setup.

Then we proceed to DataSQL nodes to make necessary dirs and files setup.

mysql-mngt.example.com:

ndb_mgmd -f /usr/local/mysql/var/mysql-cluster/config.ini

It makes sense to automatically start the management server at system boot time, so we create a very simple init script and the appropriate startup links:

mysql-mngt.example.com:

echo '/usr/local/mysql/libexec/ndb_mgmd -f /usr/local/mysql/var/mysql-cluster/config.ini' > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults

3 Set Up The MySQL Cluster Nodes (Storage Nodes)

Now we install mysql-5.1.14-rc on both mysql-data1.example.com and mysql-data2.example.com:

mysql-data1.example.com / mysql-data2.example.com:

cd /usr/src
wget http://mysql.mirrors.pair.com/Downloads/MySQL-5.1/mysql-5.1.24-rc.tar.gz
tar xvzf mysql-5.1.24-rc.tar.gz
groupadd mysql
useradd -g mysql mysql
cd /usr/src/mysql-5.1.24-rc
./configure --prefix=/usr/local/mysql --enable-community-features --with-mysqld-user=mysql --with-plugins=ndbcluster
make
make install
/usr/src/mysql-5.1.24-rc/scripts/mysql_install_db --user=mysql
cd /usr/local/mysql
chown -R root:mysql .
chown -R mysql.mysql /usr/local/mysql/var

cd /usr/src/mysql-5.1.24-rc
cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server
cd /etc/init.d
update-rc.d mysql.server defaults

Then we create the MySQL configuration file /etc/my.cnf on both nodes:

mysql-data1.example.com / mysql-data2.example.com:

vi /etc/my.cnf

[client]
port=3306
socket=/tmp/mysql.sock
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=10.0.1.30
default-storage-engine=NDBCLUSTER

#Those are for future tuning
#max_connections=341
#query_cache_size=16M
#thread_concurrency = 4
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=10.0.1.30

Make sure to fill in the correct IP address of the MySQL cluster management server.

Lets add PATH env. to data nodes also:

mysql-data1.example.com / mysql-data2.example.com:

echo "PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/libexec" >>/root/.bash_profile
echo "export PATH" >>/root/.bash_profile

Next we create the data and backup directories and start the MySQL server on both cluster nodes:

mysql-data1.example.com / mysql-data2.example.com:

mkdir /usr/local/mysql/var/mysql-cluster
mkdir /usr/local/mysql/var/mysql-cluster/backup
cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start

(Please note: we have to run ndbd --initial only when we start MySQL for the first time, and if /usr/local/mysql/mysql-cluster/config.ini on mysql-mngt.example.com changes.)

Now is a good time to set a password for the MySQL root user:

mysql-data1.example.com / mysql-data2.example.com:

mysqladmin -u root password yourrootsqlpassword

We want to start the cluster nodes at boot time, so we create an ndbd init script and the appropriate system startup links:

mysql-data1.example.com / mysql-data2.example.com:

echo '/usr/local/mysql/libexec/ndbd' > /etc/init.d/ndbd
chmod 755 /etc/init.d/ndbd
update-rc.d ndbd defaults