MySQL Cluster Server is a fault-tolerant, redundant, scalable
database architecture built on the open-source MySQL application, and
capable of delivering 99.999% reliability. In this paper we describe
the process we used to setup, configure, and test a three-node mySQL
cluster server in a test environment.
Schematic
Hardware
We used four Sun Ultra Enterprise servers in our test environment, but
the process for setting up a mySQL cluster server on other UNIX- or
Linux-based platforms is very similar, and this setup guide should be
applicable with little or no modification.
Our four machines each fall into one of three roles:
1. storage nodes (mysql-ndb-1
and mysql-ndb-2)
2. API node (mysql-api-1)
3. management server and management console (mgmt)
Note that the storage nodes are also API nodes, but the API node is not
a storage node. The API node is a full member of the cluster, but it
does not store any cluster data, and its
state (whether it is up or down) does not affect the integrity or
availablility of the data on the storage nodes. It can be thought of as
a "client" of the cluster. Applications such as web servers live on the
API nodes and communicate with the mySQL server process running
locally, on the API node itself, which takes care of fetching data from
the storage nodes. The storage nodes are API nodes as well, and
technically additional applications could be installed there and
communicate with the cluster via the mySQL server processes running on
them, but for management and performance reasons this probably should
be
considered a sub-optimal configuration in a production environment.
Software
Sun Solaris 8 operating system
mysql-max-4.1.9
We used the precompiled binary distribution of mySQL server for Sun
SPARC Solaris 8. Obviously, for implementation on other platforms, the
appropriate binary distribution should be used. In all cases, the "max"
mySQL distribution is required. The mySQL 4.1 download page can be
found here.
Procedure
Step 1. On both storage
nodes, mysql-ndb-1 (192.168.0.33) and mysql-ndb-2 (192.168.0.34), obtain
and install mySQL server:
mysql-ndb-1#
groupadd mysql mysql-ndb-1#
useradd
-g mysql mysql mysql-ndb-1#cd
/usr/local mysql-ndb-1# wget
http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz/from/http://mysql.he.net/ mysql-ndb-1#gzip
-dc mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz
| tar xvf - mysql-ndb-1#ln
-s mysql-max-4.1.9-sun-solaris2.8-sparc mysql mysql-ndb-1#cd
mysql mysql-ndb-1#
scripts/mysql_install_db --user=mysql
mysql-ndb-1# chown -R
root .
mysql-ndb-1# chown -R mysql data
mysql-ndb-1# chgrp -R mysql . mysql-ndb-1# cp support-files/mysql.server /etc/init.d/mysql.server
mysql-ndb-2#
groupadd mysql mysql-ndb-2#
useradd
-g mysql mysql mysql-ndb-2#cd
/usr/local mysql-ndb-2# wget
http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz/from/http://mysql.he.net/ mysql-ndb-2#gzip
-dc mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz
| tar xvf - mysql-ndb-2#ln
-s mysql-max-4.1.9-sun-solaris2.8-sparc mysql mysql-ndb-2#cd
mysql mysql-ndb-2#
scripts/mysql_install_db --user=mysql
mysql-ndb-2# chown -R
root .
mysql-ndb-2# chown -R mysql data
mysql-ndb-2# chgrp -R mysql . mysql-ndb-2# cp support-files/mysql.server /etc/init.d/mysql.server
Do not start the mysql servers yet.
Step 2. Setup the
management server and management console on host mgmt (192.168.0.32). This requires
only two
executables be extracted form the mysql distribution. The rest can be
deleted.
mgmt# gzip -dc
mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz |
tar xvf -
mgmt# cp
mysql-max-4.1.9-sun-solaris2.8-sparc/bin/ndb_mgm /usr/bin
mgmt# cp
mysql-max-4.1.9-sun-solaris2.8-sparc/bin/ndb_mgmd /usr/bin
mgmt# rm -r
mysql-max-4.1.9-sun-solaris2.8-sparc
mgmt# mkdir /var/lib/mysql-cluster
mgmt# cd /var/lib/mysql-cluster
mgmt# vi config.ini
The file config.ini
contains configuration information for the cluster:
[NDBD
DEFAULT]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Management Server
[NDB_MGMD]
HostName=192.168.0.32
# IP address of
this server
# Storage Nodes
[NDBD]
HostName=192.168.0.33
# IP address of
storage-node-1
DataDir= /var/lib/mysql-cluster
[NDBD]
HostName=192.168.0.34
# IP address of
storage-node-2
DataDir=/var/lib/mysql-cluster
# Setup node IDs for mySQL API-servers (clients of the cluster)
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]
Start the management server and verify that it is
running:
mgmt#ndb_mgmd
mgmt# ps -ef | grep [n]db
Step 3. On both storage nodes, mysql-ndb-1 (192.168.0.33) and mysql-ndb-2 (192.168.0.34),
configure the mySQL servers:
mysql-ndb-1# vi /etc/my.cnf mysql-ndb-2# vi /etc/my.cnf
This is the configuration file
(/etc/my.cnf) for the mysql server on both storage nodes:
[mysqld]
ndbcluster
ndb-connectstring='host=192.168.0.32' # IP address of
the management server
[mysql_cluster]
ndb-connectstring='host=192.168.0.32' # IP address of
the management server
On both
storage nodes, start the NDB storage engine and mysql server and verify
that they are running:
[mysqld(API)] 4 node(s)
id=4 (not connected, accepting connect from any host)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)
Step 5. Create
a test database, populate a table using the NDBCLUSTER engine, and
verify correct operation:
On both storage nodes
mysql-ndb-1 and mysql-ndb-2
create the test database:
mysql-ndb-1# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from test1;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
Now go to storage node mysql-ndb-2 and verify that the data
is accessible:
mysql-ndb-2# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use foo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
This is a good sign, but note that it
does not actually prove that
the data is being replicated. The storage node (mysql-ndb-2) is also a cluster API
node, and this test merely shows that it is able to retrieve data from
the cluster. It demonstrates nothing with respect to the
underlying storage mechanism in the cluster. This can be more clearly
demonstrated with the following test.
Kill off the NDB engine process (ndbd) on one of the
storage nodes (mysql-ndb-2) in
order to simulate failure of the storage engine:
mysql> select * from test2;
+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
Back on the second storage node (mysql-ndb-2) perform the same select
command:
mysql-ndb-2# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use foo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test2;
+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
The storage engine and the API server
are two separate, distinct processes that are not inherently dependent
on one another. Once the ndbd storage engine process is restarted on
the second storage node, the data is replicated, as the following test
demonstrates.
First, restart the storage engine process on mysql-ndb-2:
mysql-ndb-2# /usr/local/mysql/bin/ndbd
Next, shutdown the storage engine on mysql-ndb-1 either using the
management console or command line kill:
[mysqld(API)] 4 node(s)
id=4 @192.168.0.33 (Version: 4.1.9)
id=5 @192.168.0.34 (Version: 4.1.9)
id=6 (not connected, accepting connect from any host)
id=7 (not connected, accepting connect from any host)
ndb_mgm> 2 stop
Node 2 has shutdown.
Now, to determine if the SQL data was
replicated when the storage engine on mysql-ndb-2
was restarted, try the query on either (or both) hosts:
mysql-ndb-1# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use foo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test2;
+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
mysql-ndb-2# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use foo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test2;
+------+
| i |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
This shows that the data is being
replicated on both storage nodes. Restart the storage engine on mysql-ndb-1:
mysql-ndb-1# /usr/local/mysql/bin/ndbd
Step 6. Next, we add a
cluster API node. This node is a full member of the cluster, but does
not run the NDB storage engine. Data is not replicated on this node,
and it functions essentially as a "client" of the cluster server.
Typically, we would install applications that require access to the
mySQL data (web servers, etc) on this machine. The applications talk to
the mySQL server on localhost, which then handles the underlying
communication with the cluster in order to fetch the requested data.
First, install the mysql server on the API node mysql-api-1 (192.168.0.35):
mysql-api-1#
groupadd mysql mysql-api-1#
useradd
-g mysql mysql mysql-api-1#cd
/usr/local mysql-api-1# wget
http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz/from/http://mysql.he.net/ mysql-api-1#gzip
-dc mysql-max-4.1.9-sun-solaris2.8-sparc.tar.gz
| tar xvf - mysql-api-1#ln
-s mysql-max-4.1.9-sun-solaris2.8-sparc mysql mysql-api-1#cd
mysql mysql-api-1#
scripts/mysql_install_db --user=mysql
mysql-api-1# chown -R
root .
mysql-api-1# chown -R mysql data
mysql-api-1# chgrp -R mysql . mysql-api-1# cp support-files/mysql.server /etc/init.d/mysql.server
Install a simple /etc/my.cnf file:
[mysqld]
ndbcluster
ndb-connectstring='host=192.168.0.32' # IP address of
the management server
[mysql_cluster]
ndb-connectstring='host=192.168.0.32' # IP address of the management server
Now start the mySQL server:
mysql-api-1# /etc/init.d/mysql.server
start
Perform some test queries on the
database tables we created earlier:
mysql-api-1# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.9-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
Looks good. Now, disconnect the network
cable from the first storage node so that it falls out of the cluster.
Within a few seconds, the management console will recognize that it has
disappeared:
Now, plug the disconnected storage node
back into the network. It will attempt to rejoin the cluster, but
probably will be shutdown by the management server, and something
similar to the following will appear in the error log (/var/lib/mysql-cluster/mdb_2_error.log):
Date/Time: Saturday 12 February 2005 - 12:46:21
Type of error: error
Message: Arbitrator shutdown
Fault ID: 2305
Problem data: Arbitrator decided to shutdown this node
Object of reference: QMGR (Line: 3796) 0x0000000a
ProgramName: /usr/local/mysql/bin/ndbd
ProcessID: 1185
TraceFile: /var/lib/mysql-cluster/ndb_2_trace.log.3
***EOM***
Restart the ndb storage engine process
on that node and verify that it rejoins the cluster properly:
Remember that in order for SQL data to be stored (replicated) on
the cluster, database tables must be created specifying engine=NDBCLUSTER (as shown the in
the examples above). It is possible to use this mechanism to specify
different storage engines for different tables within the same
database, depending on individual performance and reliability
requirements. Non-critical database tables need not be stored on the
cluster.
It is possible to make NDBCLUSTER the default storage engine by
adding a line to the /etc/my.cnf
configuration file:
[mysqld]
default-table-type=NDBCLUSTER
Occasionally, after abnormal cluster node termination (for
example, a system crash) we see "hung" connections, and upon restart
the failed node is unable to join the cluster. In this case, the
session should be manually cleared on the management console using the
command, "purge stale sessions":
ndb_mgm>
purge stale sessions
Purged sessions with node id's: 3
ndb_mgm>
Please direct questions, comments, and suggestions regarding this
document to consult@lod.com
Unix/Linux/Mac OS X support by phone, email or on-site:
Support Rates
This is a Unix/Linux resource website. It contains technical articles about Unix, Linux and general computing related subjects, opinion, news, help files, how-to's, tutorials and more. We appreciate comments and article submissions.
Many of the products and books I review are things I purchased for my own use. Some were given to me specifically for the purpose of reviewing them. I resell or can earn commissions from the sale of some of these items. Links within these pages may be affiliate links that pay me for referring you to them. That's mostly insignificant amounts of money; whenever it is not I have made my relationship plain. I also may own stock in companies mentioned here. If you have any question, please do feel free to contact me.
Specific links that take you to pages that allow you to purchase the item I reviewed are very likely to pay me a commission. Many of the books I review were given to me by the publishers specifically for the purpose of writing a review. These gifts and referral fees do not affect my opinions; I often give bad reviews anyway.
We use Google third-party advertising companies to serve ads when you visit our website. These companies may use information (not including your name, address, email address, or telephone number) about your visits to this and other websites in order to provide advertisements about goods and services of interest to you. If you would like more information about this practice and to know your choices about not having this information used by these companies, click here.
Click here to add your comments
Don't miss responses! Subscribe to Comments by RSS or by Email
Click here to add your comments
If you want a picture to show with your comment, go get a Gravatar
My Hard Truths about Easy Money Book
My Self Employment Book