MySQL Cluster (OLD)
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.
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
# 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:
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 —
Connected to Management Server at: localhost:1186
[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
CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
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:
CREATE TABLE City (i INT) TYPE=MyISAM;
and need to be changed in this way:
CREATE TABLE City (i INT) ENGINE=NDBCLUSTER;
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
2 replaceing TYPE=MyISAM with ENGINE=NDBCLUSTER
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):
on both mysql hosts.