Home » Clustering, Debian, Linux, Mysql

MySQL Cluster (OLD)

12 November 2007 No Comment

I will try to exemplify as easy as possible a clustered mysql server under Debian

1. Mysql Servers

For example we have 2 servers what need to be configured in clustered mode. This servers run mysql 4.1.8a (The configuration is still possible to work on other versions). Each cluster server need to have this additional conf in /etc/mysql/my.cnf:


where app1.mydomain.com is the management server for cluster. To restart a server you need to restart start ndbd server either.

/etc/init.d/mysql restart
killall ndbd
/etc/init.d/ndbd start

2. Management server
The management sever is used to provide configuration for cluster servers. Is dont need to be all time up, but is strongly recommended

# Managment Server
# Storage Engines
DataDir= /var/lib/mysql
# 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.

After that we need to start the ndb_mgmd. The configuration is located in /etc/mysql/config.ini.
To start the ndb_mgmd just run the command:

/etc/init.d/ndb_mgmd start

After each modification you need to do a gracefully restart to ndb_mgmd and ndb and ndb need initialised again with ndb –-initial. See point 8 for gracefully restart.

3. Checking Cluster server

From app1.mydomain.com run:


A console will appear where will type show

— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=2 @db1.mydomain.com (Version: 4.1.8a, Nodegroup: 0, Master)
id=3 @db2.mydomain.com (Version: 4.1.8a, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @app1.mydomain.com (Version: 4.1.8a)

[mysqld(API)] 2 node(s)
id=4 (Version: 4.1.8a)
id=5 (Version: 4.1.8a)

This means the servers are up.

Now we try to see if the replication is ok in db1 and db2

mysql -u root -p -h db1.mydomain.com
use test;
INSERT INTO ctest () VALUES (1);
SELECT * FROM ctest;

If this works, now go to the other server (db2) and run the same SELECT and see what you get. Insert from that host and go back to host 1 and see if it works.
4. Adding a new server in cluster:

1.Stop mysql on the new server. See point 3 to configure the cluster.
2.Configure management server see point 4.
3.Restart the management server
4.Start new server and restart all servers from cluster
5.Checking cluster see point 5

5. Importing the data

Tables must be created with the ENGINE=NDB or ENGINE=NDBCLUSTER option in order to have them replicated in the cluster. From mysql dump we get something like this:


and need to be changed in this way:


6. Management Server Recommendations

I strongly recommend that you do not stop the management server once it has started. This is for several reasons:
1. The server takes hardly any server resources.
2. If a cluster falls over, you want to be able to just ssh in and type ndbd to stat it. You don’t want to have to start messing around with another server.
3. If you want to take backups then you need the management server up
4. The cluster log is sent to the management server so to check what is going on in the cluster or has happened since last this is an important tool
5. All commands from the ndb_mgm client is sent to the management server and thus no management commands without management server.
6. The managment server is required in case of cluster reconfiguration (crashed server or network split). In the case that it is not running, “split-brain” scenario will occurs. The management server arbitration role is required for this type of setup to provide better fault tolerance.
7. Converting Mysql Dump to a cluster ready dump file

For example we need to convert a file dumped with mysqldump version 4.0.x and we need this file to be mysql 4.1.x cluster ready. We need to do 2 things:
1 removing ` from tables and other variables

From command prompt run:

cat mysqldatabasefile.sql | sed -e ‘/s/`//g’ | sed -e ‘s/TYPE=MyISAM/ENGINE=NDBCLUSTER/’ > newmysqldatabasefile.sql
If we get the file from a mysql 4.1.x we need only do:
cat mysqldatabasefile.sql | sed -e ‘s/TYPE=MyISAM/ENGINE=NDBCLUSTER/’ > newmysqldatabasefile.sql

8. Safe Shutdown and Restart
To shut down the cluster simply enter the following in a shell on the machine hosting the MGM node:

ndb_mgm -e shutdown

This will cause the ndb_mgm, ndb_mgm, and any ndbd processes to terminate gracefully. Any SQL nodes can be terminated using mysqladmin shutdown and other means.
To restart the cluster, simply run these commands:
1. On the management host (app1.mydomain.com in our setup):


2. On each of the storage node hosts (db1.mydomain.com and db2.mydomain.com):



/etc/init.d/mysql start

on both mysql hosts.

Leave your response!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.