How To Set Up A Load-Balanced MySQL Cluster With MySQL 5.1 - Page 2

4 Test The MySQL Cluster

Our MySQL cluster configuration is already finished, now it's time to test it. On the cluster management server (loadb1.example.com), run the cluster management client ndb_mgm to check if the cluster nodes are connected:

mysql-mngt.example.com:

ndb_mgm

You should see this:

-- NDB Cluster -- Management Client --
ndb_mgm>

Now type show; at the command prompt:

show;

The output should be like this:

ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @10.0.1.33 (Version: 5.1.24, Nodegroup: 0, Master)
id=3 @10.0.1.34 (Version: 5.1.24, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.1.30 (Version: 5.1.24)

[mysqld(API)] 2 node(s)
id=4 @10.0.1.34 (Version: 5.1.24)
id=5 @10.0.1.33 (Version: 5.1.24)

ndb_mgm>

If you see that your nodes are connected, then everything's ok!

Type

quit;

to leave the ndb_mgm client console.

Now we have to create a test database with a test table and some data on mysql-data1.example.com:

mysql-data1.example.com:

mysql -u root -p
CREATE DATABASE mysqlclustertest;
USE mysqlclustertest;
CREATE TABLE testtable (I INT) ENGINE=NDBCLUSTER;
INSERT INTO testtable () VALUES (1);
SELECT * FROM testtable;
quit;

(Have a look at the CREATE statement: We must use ENGINE=NDBCLUSTER for all database tables that we want to get clustered! If you use another engine, then clustering will not work!)

The result of the SELECT statement should be:

mysql> SELECT * FROM testtable;
+------+
| I    |
+------+
| 1    |
+------+
1 row in set (0.03 sec)

Now we have to create the same database on sql2.example.com (yes, we still have to create it, but afterwards testtable and its data should be replicated to mysql-data2.example.com because testtable uses ENGINE=NDBCLUSTER):

mysql-data2.example.com:

mysql -u root -p
CREATE DATABASE mysqlclustertest;
USE mysqlclustertest;
SELECT * FROM testtable;

The SELECT statement should deliver you the same result as before on mysql-data1.example.com: [The CREATE statement should fail due to NDBCLUSTER Engine]

mysql> SELECT * FROM testtable;
+------+
| I    |
+------+
| 1    |
+------+
1 row in set (0.04 sec)

So the data was replicated from mysql-data1.example.com to mysql-data2.example.com. Now we insert another row into testtable:

mysql-data2.example.com:

INSERT INTO testtable () VALUES (2);
quit;

Now let's go back to mysql-data1.example.com and check if we see the new row there:

mysql-data1.example.com:

mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;

You should see something like this:

mysql> SELECT * FROM testtable;
+------+
| I    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.05 sec)

So both MySQL cluster nodes always have the same data!

Now let's see what happens if we stop node 1 (mysql-data1.example.com): Run

mysql-data1.example.com:

killall ndbd

and check with

ps aux | grep ndbd | grep -iv grep

that all ndbd processes have terminated. If you still see ndbd processes, run another

killall ndbd

until all ndbd processes are gone.

Now let's check the cluster status on our management server (mysql-mngt.example.com):

mysql-mngt.example.com:

ndb_mgm

On the ndb_mgm console, issue

show;

and you should see this:

ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 10.0.1.33)
id=3 @10.0.1.34 (Version: 5.1.24, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.0.1.30 (Version: 5.1.24)

[mysqld(API)] 2 node(s)
id=4 @10.0.1.34 (Version: 5.1.24)
id=5 @10.0.1.33 (Version: 5.1.24)

ndb_mgm>

You see, mysql-data1.example.com is not connected anymore.

Type

quit;

to leave the ndb_mgm console.

Let's check mysql-data2.example.com:

mysql-data2.example.com:

mysql -u root -p
USE mysqlclustertest;
SELECT * FROM testtable;
quit;

The result of the SELECT query should still be

mysql> SELECT * FROM testtable;
+------+
| I    |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.17 sec)

Ok, all tests went fine, so let's start our mysql-data1.example.com node again:

mysql-data1.example.com:

ndbd

Share this page:

6 Comment(s)

Add comment

Comments

From: Rico at: 2009-07-21 11:43:43

je n'ai jamais réussi a faire fonctionner le cluster avec ce tutorial :

 debian1:/usr/src/mysql-5.1.35# /usr/src/mysql-5.1.35/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/
Installing MySQL system tables...
090721 13:30:36 [ERROR] /usr/local/mysql/libexec/mysqld: unknown option '--skip-bdb'
090721 13:30:36 [ERROR] Aborting

090721 13:30:36 [Warning] Forcing shutdown of 1 plugins
090721 13:30:36 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

 

les options de mysql ont du changer. Avec la 5.1.35 ca ne marche plus !

Tutoriel intéressant tout de même. Merci

 

Cordialement.

From: at: 2013-12-18 23:12:36

The problem with ndbd not found is due to not setting the PATH enviroment (which is a step in this tutorial). Since PATH may be a problem I have updated the howto with the full path to:

/usr/local/mysql/libexec/ndbd --initial

The error with --skip-bdb is easy to fix just edit /etc/mysql/my.cnf or your my.cnf file in other localisation and put a # before skip-bdb like this:

#skip-bdb. Restart MySQL and it should start without any problems.

From: Michael at: 2009-10-14 11:03:13

Hey,

 So I have folled this through and am stuck with the following block.

mkdir /usr/local/mysql/var/mysql-cluster
mkdir /usr/local/mysql/var/mysql-cluster/backup
cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start 

 

The folder /var/lib/mysql-cluster does not exists and the command ndbd is not present in the build.  I am using version 5.1.39.

Is there something different between the two versions?  

I would apprechiate it if someone would come backt o me with an answer on how to complete the build.

 

Regards,

Michael

From: Ron at: 2010-09-20 20:38:24

Michael,

 I am having the exact same problem, I am using version 5.1.50.  Did you every find the solution?  Can anyone please help?

I have no directory called:  /var/lib/mysql-cluster

 Thanks.

From: at: 2008-06-20 08:54:26

the command CREATE DATABASE in a cluster mysql is a command that is already propagete in all the node. You dont need to create database in every node.

the rest is great!

 

From: Chris D. at: 2008-10-09 23:54:40

Your article is supposed to show how to set this up on CentOS 5.1 however the load balancing section seems to be using a debian based setup.  Is this correct?  Can you update this to reflect the use of CentOS 5.1 as referenced in the title?  I have everything working up to the point of the load balancers but now I am stuck because I do not have a server based on debianor ubuntu.