October 9, 2006

How to create a MySQL Cluster with 6 machines

Posted in MySQL CLustering at 12:48 am by captsulu32

Installing Mysql Cluster can me a little tricky if you don’t have the right resources.

How many machines are you going to be running.
The optimal configuration for MySQL-Cluster is

4 storage node machines.
1 Manager machine.
1 Api Machine (Of course web-server can double as an API)

Storage nodes should by nature have at least 1 Gig of memory in them you have to remember that MySQL-cluster ndbcluster table structure keeps all the data in memory. So hard-drive space really is not much of a concern. (However, Mysql is working on Version 5.1 which is supposed to be able to keep text and memo fields on hard-drive and keep all the indexes in memory to keep speed to the utmost.) If you are going to be using this cluster in a production environment you should make sure you have the right hardware that will support your application.
I’ll walk through what I did as a test cluster before I could convince upper management to rely on a open source database cluster in a production environment. I took 5 workstation computers nothing too expensive they will all MSI motherboards which had Microsoft windows on them and 256 megs of Ram.  They were sitting around the office not being used as workstation since my business is travel we have our slow times and busy times of the year. This was one of those slow times.

Anyway, 6 machines. Yes I thought I could get away with 4 in the begining. But after reading the MySQL clustering whitepaper and a couple of magazine articles on the topic I ran around the office and got 2 more machines.

Installed FC5, on them the i386 version you can download the ISO images from http://download.fedora.redhat.com  and installed them on the machines with a level 3 type OS install (this means without Xwindows or any graphical programs). I would recommend installing the Developer tools you need to have the gcc compiler on the machine. Then once that is installed. Do a yum update on the machine. Be patient this will take a while there are 1058 updates the last time I looked. Redhat is working very hard on the selinux security therefore its had a lot of attention and lots of updates.

Then in a browser go to the mysql website copy the links to wget the needed files. With FC5 the i386 RHRel 5 edition will work fine. I used the rpm’s that are already compiled. On each of the storage nodes and the manager install

rpm -Uvh MySQL-ndb-tools-version.rpm

rpm -Uvh MySQL-ndb-extras-version.rpm

rpm -Uvh MySQL-ndb-storage-version.rpm

rpm -Uvh MySQL-ndb-management-version.rpm

What I did once I downloaded them is to scp copy from one machine to the rest don’t mix match the versions they won’t work. I also installed the management on all the storage nodes so that a storage node could double as a manager in case of an emergency.

MySQL-cluster uses what is called a heart beat. You go too long without a heart beat you can loose all your data because it’s all stored in memory.

Then on the manager node I created a config.ini file and stored in the /var/lib/mysql-cluster/ directory as config.ini

[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=786M
IndexMemory=128M
MaxNoOfAttributes=4000
MaxNoOfOrderedIndexes=125
MaxNoOftables=150
TimeBetweenWatchDogCheck= 90000

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Managment Server
[NDB_MGMD]
# the IP of THIS SERVER
HostName=192.168.1.11
LogDestination=FILE:filename=cluster.log,maxsize=1000000,maxfiles=6
# Storage Engines
[NDBD]
# the IP of the FIRST SERVER (STORAGE NODE)
HostName=192.168.1.12
DataDir= /var/lib/mysql-cluster
[NDBD]
# the IP of the SECOND SERVER (STORAGE NODE)
HostName=192.168.1.13
DataDir=/var/lib/mysql-cluster
[NDBD]
# the IP of the Third SERVER (STORAGE NODE)
HostName=192.168.1.14
DataDir=/var/lib/mysql-cluster
[NDBD]
# the IP of the Forth SERVER (STORAGE NODE)
HostName=192.168.1.15
DataDir=/var/lib/mysql-cluster

# 2 MySQL Clients
# I personally leave this blank to allow rapid changes of the mysql clients;
# you can enter the hostnames of the above two servers here. I suggest you dont.
[MYSQLD] 

[MYSQLD]

[MYSQLD]

#end of file

Then run the manager daemon

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

then

ndb_mgm

Once you have run the manager and are at a ndb_mgm> prompt type in the command show. I use putty.exe on my windows desktop machine on my desk and ssh into my servers so I am have to bring up all of the nodes and manager in separate screens(windows) I know I should be using a Linux desktop but I just can’t bring myself to make that plunge just yet.  

Now on each of the storage nodes you need to create a my.cnf file and put it in the /etc directory. Create the my.cnf file making sure the ip address is that of the manager for each storage node.

#Options for mysqld process:
[MYSQLD]
ndbcluster
ndb-connectstring=192.168.1.11

#Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=192.168.1.11

#end of file

Then you need to make sure you create the directory if it’s not already there

/var/lib/mysql-cluster

chown mysql:mysql /var/lib/mysql-cluster

Now your ready to start each of the storage nodes. Login to each node and run the

ndbd –initial

since I use putty on the manager node in the ndb_mgm program type show you should now see that each node is in starting mode. As you bring them up.  If you are doing the same type of config The cluster will not start until you run the ndbd –initial on each of the storage nodes.  Once that is done it will take a few minutes (It’s creating a blank database with the size that is specified in the config.ini file. If you need to change this you will have to rerun the ndbd –initial on each of the storage nodes to create the new sizes. Don’t forget to restart the ndb_mgmd as well if you change the config.ini after initial startup. Otherwise running ndbd –initial will only re-create the same size database that if is memory)

Now you should have a screen that looks like this in your manager

Cluster Configuration
———————
[ndbd(NDB)]     4 node(s)
id=2    @192.168.1.12  (Version: 5.0.24, Nodegroup: 0, Master)
id=3    @192.168.1.13  (Version: 5.0.24, Nodegroup: 0)
id=4    @192.168.1.14  (Version: 5.0.24, Nodegroup: 1)
id=5    @192.168.1.15  (Version: 5.0.24, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.11 (Version: 5.0.24)

[mysqld(API)]   6 node(s)
id=6     (not connected, accepting connect from any host)
id=7     (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
Now your ready to start interfacing with your new cluster to interface you must do so through an API node. API nodes are the trickier ones to load. The my.cnf files should be the same as a storage node as I did above. 

From the Mysql download website make sure that you use the same version as the rpms you had for the ndb part of your cluster

rpm -Uvh MySQL-client-verison.rpm

rpm -Uvh MySQL-server-version.rpm

rpm -Uvh MySQL-Max-version.rpm  (absolutely a must have)

rpm -Uvh MySQL-shared-version.rpm

If while installing any of these rpm’s you find that it’s requesting additional files to be installed to support the rpm packages I would recommend that you yum install them. One example that I remember is I had to yum install perl-DBI on all the machines.

Make sure that you restart the mysql daemon once you get the my.cnf file in place. Oh, and something that FC5 won’t tell you is selinux comes turned on. I would not do this on an outside IP address do it behind a firewall and simply turn off the selinux you can do this at a prompt type:

setenforce 0

But make sure you have a firewall in place otherwise you will have to dig into selinux and tell it to allow port 1156 communication and iptables that this port is needed.

Once all that is done you should be able to see something like this in you manager section when you do a show command.

ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)]     4 node(s)
id=2    @192.168.1.12  (Version: 5.0.24, Nodegroup: 0, Master)
id=3    @192.168.1.13  (Version: 5.0.24, Nodegroup: 0)
id=4    @192.168.1.14  (Version: 5.0.24, Nodegroup: 1)
id=5    @192.168.1.15  (Version: 5.0.24, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.11  (Version: 5.0.24)

[mysqld(API)]   6 node(s)
id=6    @192.168.1.7  (Version: 5.0.24)
id=7    @192.168.1.110  (Version: 5.0.24)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)

ndb_mgm>
Oh, mine looks this way because I have 2 api’s you need to also make sure you are doing regular scheduled backups RAM can fail from power loss or static electricity can destroy memory over time.  I made a simple script and put it in the /etc/cron.daily sub-directory called clusterbackup. Inside the script I have the following command.

ndb_mgm -e “START BACKUP”

This will create a backup set of files on each of the storage nodes in your /var/lib/mysql-cluster/ directory each time one is done it creates a new sub-directory under BACKUP. Example would be…

/var/lib/mysql-cluster/BACKUP/BACKUP-15

if you have a catastrophic failure you can restore from backup. With the ndb_restore command. Remember that storage node 1 is numbered starting with 2 as in the management program. So your restore would look like this.

ndb_restore -c 192.168.1.11 -b 15 -n 2 -m -r /var/lib/mysql-cluster/BACKUP/BACKUP-15

-m is used only on the first node your recovering on

-b stands for the backup number your restoring

-n is the node that you are restoring the files from.

-c is the management node ip address

Notice in all of the configs that IP addresses are used instead of DNS names. This is because the amount of communication that is needed between the machines is heavy at times and if the heart beat can’t get through you could have a catastrophic failure on your hands.  But as a network engineer knows plan for the worst and hope for the best.

Failures do happen but for the most part I must admit the cluster is pretty stable. I have been running one for about 9 months now and have only seen 15 to 20 failures and most of the time it was electrical or NIC cards on cheap motherboards that caused issues. But I have for the lsat three months had 100% database uptime despite the failures. And to top it off I live in one of the roughest places to have a network when we get tornado’s, hurricanes and spring time weather that leads to hours without power but luckily our building has a generator for those issues. If your not so lucky you should at the very least have battery backup that will give you enough time to run a manual backup.  You can login at any time to the manager and do a snap shot backup of the database.

Permalink