Steps to Set Up MySQL Cluster For Multiple Dedicated Servers

Main aim to setup clustering MySQL is to haveIP of the MANAGMENT (THIRD) SERVER
redundancy - you server and applications will run[mysql_cluster]ndb-connectstring=192.168.0.3 # the IP
smoothly even if one server goes down.of the MANAGMENT (THIRD) SERVER
Note: For better performance you should have a 3rdNow, we make the data directory and start the
server as a management node but this can be shutstorage engine:mkdir /var/lib/mysql-clustercd /var/lib
down after the cluster starts. Also note that shuttingmysql-cluster
down the management server is not recommended/usr/local/mysql/bin/ndbd --initial
(see the extra notes at the bottom of this/etc/rc.d/init.d/mysql.server start
document for more information). You can not run aIf you have done one server now go back to the
MySQL Cluster with just two Dedicated servers Andstart of stage 3 and repeat exactly the same
have true redundancy.procedure on the second server.
It is possible to set up the cluster on two DedicatedNote: you should ONLY use --initial if you are either
Servers you will not get the ability to "kill" one serverstarting from scratch or have changed the config.ini
and for the cluster to continue as normal. For thisfile on the management.
you need a third server running the managementSTAGE 4: Check its working
node.You can now return to the management server
Now below I had given the example for three - - -(mysql3) and enter the managment console:
192.168.0.3/usr/local/mysql/bin/ndb_mgm
Servers 1 and 2 will be the two that end upEnter the command SHOW to see what is going on.
"clustered". This would be perfect for two serversA sample output looks like this:
behind a load balancer or using round robin DNS and is[root@mysql3 mysql-cluster]# /usr/local/mysql/bin
a good replacement for replication. Server 3 needs tondb_mgm
have only minor changes made to it and does NOT-- NDB Cluster -- Management Client --ndb_mgm>
require a MySQL install. It can be a low-end machineshow
and can be carrying out other tasks.Connected to Management Server at: localhost:1186
STAGE 1: Install MySQL on the first two servers:Cluster Configuration
Complete the following steps on both mysql1 and[ndbd(NDB)] 2 node(s)id=2 @192.168.0.1 (Version:
mysql2:cd /usr/local/dev.mysql.com/get/Downloads4.1.9, Nodegroup: 0, Master)id=3 @192.168.0.2
MySQL-4.1/mysql-max-4.1.9-pc-linux-gnu-i686.tar.gz(Version: 4.1.9, Nodegroup: 0)
from/signal42.com/mirrors/mysql/groupadd[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.3
mysqluseradd -g mysql mysqltar -zxvf(Version: 4.1.9)
mysql-max-4.1.9-pc-linux-gnu-i686.tar.gzrm[mysqld(API)] 2 node(s)id=4 (Version: 4.1.9)id=5
mysql-max-4.1.9-pc-linux-gnu-i686.tar.gzln -s(Version: 4.1.9)ndb_mgm>
mysql-max-4.1.9-pc-linux-gnu-i686 mysqlcdIf you seenot connected, accepting connect from
mysqlscripts/mysql_install_db --user=mysqlchown -R192.168.0.[1/2/3]in the first or last two lines they you
root .chown -R mysql datachgrp -R mysql .cphave a problem. Please email me with as much detail
support-files/mysql.server /etc/rc.d/init.d/chmod +xas you can give and I can try to find out where you
etc/rc.d/init.d/mysql.serverchkconfig --addhave gone wrong and change this HOWTO to fix it.
mysql.serverIf you are OK to here it is time to test MySQL. On
Do not start MySQL yet.either server mysql1 or mysql2 enter the following
STAGE 2: Install and configure the managementcommands: Note that we have no root password
serveryet.mysqluse test;
You need the following files from the bin/ of theCREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
mysql directory: ndb_mgm and ndb_mgmd. DownloadINSERT INTO ctest () VALUES (1);
the whole mysql-max tarball and extract them fromSELECT * FROM ctest;
the bin/ directory.mkdir /usr/src/mysql-mgmcd /usrYou should see 1 row returned (with the value 1).
src/mysql-mgmdev.mysql.com/get/DownloadsIf this works,which will probably happen, go to the
MySQL-4.1/mysql-max-4.1.9-pc-linux-gnu-i686.tar.gzother server and run the same SELECT and see
from/ -zxvf mysql-max-4.1.9-pc-linux-gnu-i686.tar.gzrmwhat you get. Insert from that host and go back to
mysql-max-4.1.9-pc-linux-gnu-i686.tar.gzcdhost 1 and see if it works. If it works then
mysql-max-4.1.9-pc-linux-gnu-i686mv bin/ndb_mgmcongratulations.
.mv bin/ndb_mgmd .chmod +x ndb_mg*mvThe final test is to kill one server to see what
ndb_mg* /usr/bin/cdrm -rf /usr/src/mysql-mgmhappens. If you have physical access to the machine
You now need to set up the config file for thissimply unplug its network cable and see if the other
management:mkdir /var/lib/mysql-clustercd /var/libserver keeps on going fine (try the SELECT query).
mysql-clustervi [or emacs or any other editor]If you dont have physical access do the following:ps
config.iniaux | grep ndbd
Now, insert the following (changing the bits asYou get an output like this:root 5578 0.0 0.3 6220
indicated):1964 ? S 03:14 0:00 ndbdroot 5579 0.0 20.4 492072
[NDBD DEFAULT]102828 ? R 03:14 0:04 ndbdroot 23532 0.0 0.1 3680
NoOfReplicas=2684 pts/1 S 07:59 0:00 grep ndbd
[MYSQLD DEFAULT]In this case ignore the command "grep ndbd" (the
[NDB_MGMD DEFAULT]last line) but kill the first two processes by issuing the
[TCP DEFAULT]command kill -9 pid pid:kill -9 5578 5579
# Managment ServerThen try the select on the other server. While you
[NDB_MGMD]are at it run a SHOW command on the managment
HostName=192.168.0.3 # the IP of THIS SERVERnode to see that the server has died. To restart it,
# Storage Enginesjust issuendbd
[NDBD]Note: no --initial!
HostName=192.168.0.1 # the IP of the FIRSTFurther notes about setup
SERVERI strongly recommend that you read all of this (and
DataDir= /var/lib/mysql-clusterbookmark this page). It will almost certainly save you
[NDBD]a lot of searching.
HostName=192.168.0.2 # the IP of the SECONDThe Management Server
SERVERI strongly recommend that you do not stop the
DataDir=/var/lib/mysql-clustermanagement server once it has started. This is for
# 2 MySQL Clientsseveral reasons:
# I personally leave this blank to allow rapid changes* The server might hardly require and take any
of the mysql clients;server resources
# you can enter the hostnames of the above two* If a cluster falls over, you want to be able to just
servers here. I suggest you dont.ssh in and type ndbd to stat it. You will not want to
[MYSQLD]start messing around with another server
[MYSQLD]* You need the management server up If you want
Now, start the management server:ndb_mgmdto take backups
This is the MySQL management server, not* The cluster log is sent to the management server
management console. You should therefore notso to check what is going on in the cluster or has
expect any output (we will start the console later).happened since last this is an important tool
STAGE 3: Configure the storage/SQL servers and* All commands from the ndb_mgm client is sent to
start MySQLthe management server and thus no management
On each of the two storage/SQL servers (192.168.0.1commands without management server.
and 192.168.0.2) enter the following (changing the bits* The management server is required in case of
as appropriate):vi /etc/my.cnfcluster reconfiguration (crashed server or network
Enter i to go to insert mode again and insert this onsplit). In the case that it is not running, "split-brain"
both servers (changing the IP address to the IP ofscenario will occur. The management server
the managment server that you set up in stage 2):arbitration role is required for this type of setup to
[mysqld]ndbclusterndb-connectstring=192.168.0.3 # theprovide better fault tolerance.