MySQL Cluster (NDB) Example

Here are my notes on how to set-up a simple MySQL Cluster configuration. The following limits the configuration to 2 Linux Servers. By no means a production set-up, the following is meant to demonstrate the configuration/operational basics:


This example assumes that there are 2 pre-existing hosts, both with binary versions of MySQL 5.0 installed.

Download the applicable Cluster binaries from MySQL

On both hosts

There are certain dependencies that need to dealt with before installing the cluster binaries; you may want to consult the release notes for your applicable version. In my case, I need to update the perl module HTML::Template:

# cpan 'install HTML::Template'

Install the applicable cluster binaries to the existing distribution:

rpm -ivh --nodeps MySQL-clusterstorage-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clustermanagement-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clustertools-community-5.0.51a-0.rhel4.i386.rpm \
MySQL-clusterextra-community-5.0.51a-0.rhel4.i386.rpm

On the host where the Management Server will reside, create the /var/lib/mysql-cluster/config.ini configuration file and edit as appropriate:

config.ini

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[tcp default]
portnumber=2202

[ndb_mgmd]
hostname=192.168.1.101
datadir=/var/lib/mysql

# Options for data node "A":
[ndbd]
hostname=192.168.1.101
datadir=/var/lib/mysql

# Options for data node "B":
[ndbd]
hostname=192.168.1.102
datadir=/var/lib/mysql

# SQL node options:
[mysqld]
hostname=192.168.1.101

[mysqld]
hostname=192.168.1.102

As you can see, I have configured this file to reflect the aforementioned node locations.

On both hosts

Configure the /etc/my.cnf to reflect the location of the Management Server; this is used by the Data Nodes:

[mysql_cluster]
ndb-connectstring=192.168.1.101

Also, update the option file used to start each sqld process:

[mysqld]
ndbcluster
ndb-connectstring   = 192.168.1.101

At this point, you are ready to start the processes:

On the Management Server:

# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Warning line 29: Cluster configuration warning:
  arbitrator with id 1 and db node with id 2 on same host 192.168.1.101
  Running arbitrator on the same host as a database node may
  cause complete cluster shutdown in case of host failure.

You can ignore the aforementioned message, this warning occurs because it is customary to run the Management Server on a seperate host from that of the Data/sqld nodes.

On both hosts

Start the Data Nodes and re-start the sqld processes:

# ndbd
# service mysql-5.0.51 restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..........                                   [  OK  ]

Once all processes are started on all Nodes, issue the following on the Management Server's Node:

# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.101:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.1.101  (Version: 5.0.51, Nodegroup: 0, Master)
id=3    @192.168.1.102  (Version: 5.0.51, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.101  (Version: 5.0.51)

[mysqld(API)]   2 node(s)
id=4    @192.168.1.101  (Version: 5.0.51)
id=5    @192.168.1.102  (Version: 5.0.51)

You should see something similar to the above. Additionally, each sqld process
should show the ndbcluster engine as enabled:

mysql> show storage engines;
+------------+---------+----------------------------------------------------------------+
 Engine      Support  Comment                                                       
+------------+---------+----------------------------------------------------------------+
 MyISAM      DEFAULT  Default engine as of MySQL 3.23 with great performance        
 MEMORY      YES      Hash based, stored in memory, useful for temporary tables     
 InnoDB      YES      Supports transactions, row-level locking, and foreign keys    
 BerkeleyDB  NO       Supports transactions and page-level locking                  
 BLACKHOLE   YES      /dev/null storage engine (anything you write to it disappears)
 EXAMPLE     YES      Example storage engine                                        
 ARCHIVE     YES      Archive storage engine                                        
 CSV         YES      CSV storage engine                                            
 ndbcluster  YES      Clustered, fault-tolerant, memory-based tables                
 FEDERATED   YES      Federated MySQL storage engine                                
 MRG_MYISAM  YES      Collection of identical MyISAM tables                         
 ISAM        NO       Obsolete storage engine                                       
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

Test the configuration by creating a table on one of the nodes - assigned to the ndbcluster storage engine - in a database that is present in every nodes' datadir; for example:

mysql> use cluster;
Database changed
mysql> CREATE TABLE `cluster_test` (
    ->   `id` tinyint(4) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.56 sec)

Insert a sample data row:

mysql> insert into cluster_test values (100);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Once you begin to use the cluster storage engine, you will be able to monitor its statisics via:

mysql> show engine ndb status;
+-----------------------+---------+------+--------+
 free_list              created  free  sizeof
+-----------------------+---------+------+--------+
 NdbTransaction               2     0     208
 NdbOperation                 4     4     652
 NdbIndexScanOperation        1     1     736
 NdbIndexOperation            0     0     656
 NdbRecAttr                 516   516      72
 NdbApiSignal                16    16     136
 NdbLabel                     0     0     196
 NdbBranch                    0     0      24
 NdbSubroutine                0     0      68
 NdbCall                      0     0      16
 NdbBlob                      0     0     204
 NdbReceiver                  2     0      68
+-----------------------+---------+------+--------+
12 rows in set (0.00 sec)

To complete the test, login to the other Node and query its database to see whether the aforementioned table and row now exist there:

mysql> use cluster
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> show tables;
+-------------------+
 Tables_in_cluster
+-------------------+
 cluster_test     
+-------------------+
1 row in set (0.01 sec)

mysql> select * from cluster_test;
+-----+
 id 
+-----+
 100
+-----+
1 row in set (0.00 sec)

mysql> show engine ndb status;
+-----------------------+---------+------+--------+
 free_list              created  free  sizeof
+-----------------------+---------+------+--------+
 NdbTransaction               3     0     208
 NdbOperation                 4     4     652
 NdbIndexScanOperation        1     1     736
 NdbIndexOperation            0     0     656
 NdbRecAttr                 516   516      72
 NdbApiSignal                16    16     136
 NdbLabel                     0     0     196
 NdbBranch                    0     0      24
 NdbSubroutine                0     0      68
 NdbCall                      0     0      16
 NdbBlob                      0     0     204
 NdbReceiver                  2     0      68
+-----------------------+---------+------+--------+
12 rows in set (0.00 sec)

As you can see, the changes are now present on the other Node!

The sample configuration is now up and running and ready for further testing, etc.

Labels: MySQL