MySQL Clustering – configuration and testing

By Arun Bagul | April 22, 2008 | Comments 1 comment

Introduction – MySQL Clustering is simple, easy to setup and reliable solution. That enables clustering of in-memory databases in a shared-nothing system. MySQL supports only NDB storage or table type system in clustering environment. MySQL Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB. A MySQL Cluster consists of a set of computers, each running a one or more processes which may include a MySQL Server (API), a Data (Storage) node, a Management server.

In MySQL Cluster the tables are stored in the Data (Storage) nodes. Due to this tables are directly accessible from all other MySQL servers in the cluster, provided that the Database which contains the table is available (if not need to create database) on all MySQL nodes. The Data stored in the Data nodes for MySQL Cluster can be mirrored. The NDB storage engine can be configured with a range of failover and load balancing options. NDB is an in-memory storage engine offering high-availability and data-persistence features.

mysql-clustering

There are three types of cluster nodes, in MySQL Cluster configuration. There will be at least three nodes, one of each of types is require to configure MySQL clustering successfully.

1) Management (MGM) Node – Manages the other nodes within the MySQL Cluster, performing such functions as providing configuration data, starting and stopping nodes, running backup, and so forth. Because this node type manages the configuration of the other nodes, a node of this type should be started first, before any other node. An MGM node is started with the command ndb_mgmd.

2) Data node – This node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you will need four data nodes. It is not necessary to have more than one replica. A data node is started with the command ndbd.

3) SQL node – use to accesses the cluster data. SQL node is a traditional MySQL server that uses the NDB Cluster storage engine. An SQL node is typically started with the command mysqld –ndbcluster or by using mysqld with the ndbcluster option added to my.cnf.

An SQL node is actually just a specialised type of API node, which designates any application which accesses Cluster data. One example of an API node is the ndb_restore utility that is used to restore a cluster backup. It is possible to write such applications using the NDB API.

** Let’s implement MySQL cluster practically –

In my case there are three(3) Storage Node ie (192.168.0.2 ,192.168.0.3 and 192.168.0.4), four(4) MySQL API server (192.168.0.1, 192.168.0.2, 192.168.0.3 and 192.168.0.4 ) and one Management Node (192.168.0.5)…

* Please follow these steps carefully you can easily set MySQL clustering.

1] Login to Management node here IP of MGM node is 192.168.0.5

* Create directory where data for Mgmd (Management node) is stored –

root@fileserver:~# mkdir /var/lib/mysql-cluster_MGM
root@fileserver:~#

root@fileserver:~# ll -d /var/lib/mysql-cluster_MGM
drwxr-xr-x 2 root root 4096 2008-01-05 11:22 /var/lib/mysql-cluster_MGM
root@fileserver:~#

* Make sure that the owner of data directory is MySQL user ie “mysql” –

root@fileserver:~# chown mysql:mysql -R /var/lib/mysql-cluster_MGM/
root@fileserver:~#

root@fileserver:~# ll -d /var/lib/mysql-cluster_MGM/
drwxr-xr-x 2 mysql mysql 4096 2008-01-05 11:22 /var/lib/mysql-cluster_MGM/
root@fileserver:~#

2] How to start Management Node

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgmd –help
Usage: ndb_mgmd [OPTIONS]
MySQL distrib 5.0.21, for pc-linux-gnu (i486)
-?, –usage Display this help and exit.
-?, –help Display this help and exit.
-V, –version Output version information and exit.
-c, –ndb-connectstring=name
Set connect string for connecting to ndb_mgmd. Syntax:
“[nodeid=<id>;][host=]<hostname>[:<port>]”. Overides
specifying entries in NDB_CONNECTSTRING and Ndb.cfg
–ndb-mgmd-host=name
Set host and port for connecting to ndb_mgmd. Syntax:
<hostname>[:<port>].
–ndb-nodeid=# Set node id for this node.
–ndb-shm Allow optimizing using shared memory connections when
available
–ndb-optimized-node-selection
Select nodes for transactions in a more optimal way
-c, –connect-string=name
same as –ndb-connectstring
–core-file Write core on errors.
–character-sets-dir=name
Directory where character sets are.
-f, –config-file=name
Specify cluster configuration file
-P, –print-full-config
Print full config and exit
-d, –daemon Run ndb_mgmd in daemon mode (default)
–interactive Run interactive. Not supported but provided for testing
purposes
–no-nodeid-checks Do not provide any node id checks
–nodaemon Don’t run as daemon, but don’t read from stdin
–mycnf Read cluster config from my.cnf

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————–
ndb-connectstring (No default value)
ndb-mgmd-host (No default value)
ndb-nodeid 0
ndb-shm FALSE
ndb-optimized-node-selection TRUE
connect-string (No default value)
core-file FALSE
character-sets-dir (No default value)
config-file (No default value)
print-full-config FALSE
daemon TRUE
interactive FALSE
no-nodeid-checks FALSE
nodaemon FALSE
mycnf FALSE
root@fileserver:/var/lib/mysql-cluster_MGM#

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgmd -f /var/lib/mysql-cluster_MGM/config.conf
root@fileserver:/var/lib/mysql-cluster_MGM#

root@fileserver:/var/lib/mysql-cluster_MGM# netstat -nlp | grep ndb
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 24291/ndb_mgmd

root@fileserver:/var/lib/mysql-cluster_MGM#
root@fileserver:/var/lib/mysql-cluster_MGM# /etc/init.d/mysql-ndb
mysql-ndb mysql-ndb-mgm
root@fileserver:/var/lib/mysql-cluster_MGM# /etc/init.d/mysql-ndb-mgm

3] Managment Node command line utility –

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
ndb_mgm ndb_mgmd
root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 (not connected, accepting connect from 192.168.0.2)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 (not connected, accepting connect from 192.168.0.4)

ndb_mgm>

4] Login to Storage Node 192.168.0.2 and edit my.cnf file –

* Make changes to my.cnf file as shown below –

root@asterisk:/etc/mysql# tail /etc/mysql/my.cnf
# The following configuration is read by the ndbd storage daemons,
# not from the ndb_mgmd management daemon.
#
#################################
#Use this MySQL server as NDB storage/Data node
#MGM node IP is – 192.168.0.5

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.5

root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
root@asterisk:/etc/mysql# netstat -nlp | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 19518/mysqld
root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# ndbd –help
Usage: ndbd [OPTIONS]
MySQL distrib 5.0.22, for pc-linux-gnu (i486)
-?, –usage Display this help and exit.
-?, –help Display this help and exit.
-V, –version Output version information and exit.
-c, –ndb-connectstring=name
Set connect string for connecting to ndb_mgmd. Syntax:
“[nodeid=<id>;][host=]<hostname>[:<port>]”. Overides
specifying entries in NDB_CONNECTSTRING and Ndb.cfg
–ndb-mgmd-host=name
Set host and port for connecting to ndb_mgmd. Syntax:
<hostname>[:<port>].
–ndb-nodeid=# Set node id for this node.
–ndb-shm Allow optimizing using shared memory connections when
available
–ndb-optimized-node-selection
Select nodes for transactions in a more optimal way
-c, –connect-string=name
same as –ndb-connectstring
–core-file Write core on errors.
–character-sets-dir=name
Directory where character sets are.
–initial Perform initial start of ndbd, including cleaning the
file system. Consult documentation before using this
-n, –nostart Don’t start ndbd immediately. Ndbd will await command
from ndb_mgmd
-d, –daemon Start ndbd as daemon (default)
–nodaemon Do not start ndbd as daemon, provided for testing
purposes
–foreground Run real ndbd in foreground, provided for debugging
purposes (implies –nodaemon)

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————–
ndb-connectstring 192.168.0.5
ndb-mgmd-host (No default value)
ndb-nodeid 0
ndb-shm FALSE
ndb-optimized-node-selection TRUE
connect-string 192.168.0.5
core-file FALSE
character-sets-dir (No default value)
initial FALSE
nostart FALSE
daemon TRUE
nodaemon FALSE
foreground FALSE
root@asterisk:/etc/mysql#

* Data on Storage node is stored in ” /var/lib/mysql-cluster” directory. The MySQL user ie “mysql” need to be owner of this directory.

root@asterisk:/etc/mysql# ll -d /var/lib/mysql-cluster/
drwxr-xr-x 2 root root 4096 2006-06-16 17:38 /var/lib/mysql-cluster/
root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# chown mysql:mysql -R /var/lib/mysql-cluster/
root@asterisk:/etc/mysql# ll -d /var/lib/mysql-cluster/
drwxr-xr-x 2 mysql mysql 4096 2006-06-16 17:38 /var/lib/mysql-cluster/
root@asterisk:/etc/mysql#

** How to start Storage or Data node first time –

root@asterisk:/etc/mysql# ndbd –initial
…
….
root@asterisk:/etc/mysql#

Note – please use “–initial” while starting storage or data node first time
root@asterisk:/etc/mysql# netstat -nlp | grep ndb
tcp 0 0 192.168.0.2:38321 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:43001 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:41115 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:54651 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:55003 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:59453 0.0.0.0:* LISTEN 19686/ndbd

root@asterisk:/etc/mysql#

NOTE :: Please follow the same steps as mention here for other storage node ie 192.168.0.3 and 192.168.0.4.

5] Goto Managment node and check whether 192.168.0.2 Storage node is activated or not

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

ndb_mgm>

6] Now login to server 192.168.0.4 and configure mysql server as MySQL Node (API) –

root@linserver:~# vi /etc/mysql/my.cnf

[mysqld]

#
#setting for SQL-node of MySQL cluser
#
###########################################
#Use this MySQL server as NDB storage/Data node
#MGM node IP is – 192.168.0.5
#set Table/engine type to NDB
ndbcluster
#set MGM IP address
ndb-connectstring=192.168.0.5

root@linserver:~#

7] Goto Managment node and check whether 192.168.0.4 as MySQL node is activated or not –

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, starting, Nodegroup: 0)
id=3 @192.168.0.3 (Version: 5.0.21, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 (Version: 5.0.21)

ndb_mgm>

** Now check that MySQL API node 192.168.0.4 is activated

ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, Nodegroup: 0, Master)
id=3 @192.168.0.3 (Version: 5.0.21, Nodegroup: 0)
id=4 @192.168.0.4 (Version: 5.0.22, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

ndb_mgm>

NOTE:: Please follow the same steps as mention here for other MySQL API node ie 192.168.0.1, 192.168.0.2 and 192.168.0.3

** How to check/use MySQL Clustering –

8] Login to MySQL server 192.168.0.4 and follow the process

root@linserver:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 708 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database arunbagul;
Query OK, 1 row affected (0.00 sec)

mysql>

mysql> use arunbagul;
Database changed
mysql> CREATE TABLE cluster_tab (rollno INT NOT NULL , name varchar(255)) ENGINE=’NDBCLUSTER’;
Query OK, 0 rows affected (0.38 sec)

mysql>

mysql> SHOW TABLES;
+———————+
| Tables_in_arunbagul |
+———————+
| cluster_tab |
+———————+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO cluster_tab VALUES ( 1 , ‘Arun Bagul’ ) ;
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO cluster_tab VALUES ( 2 , ‘Yogesh Nikam’ );
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO cluster_tab VALUES ( 3 , ‘Ketan Mayangar’ );
Query OK, 1 row affected (0.01 sec)

mysql>

9] Now login to MySQL server of 192.168.0.3 which is acting as MySQL node (API).

root@proxy:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

NOTE :: See out put of above query. It’s not showning the “arunbagul” DB which is clustered environment. Please Remember that you need to create DB on each MySQL API node, for DB which is in MySQL cluster! Then after the Clustered DB can be accessed from all MySQL API. All the tables etc are also available….

mysql> create database arunbagul;
Query OK, 1 row affected (0.07 sec)

mysql> use arunbagul ;
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>
mysql> show tables;
+———————+
| Tables_in_arunbagul |
+———————+
| cluster_tab |
+———————+
1 row in set (0.01 sec)

mysql> INSERT INTO cluster_tab VALUES ( 4 , ‘Nishit Shah’ );
Query OK, 1 row affected (0.10 sec)

mysql> quit
Bye
root@proxy:~#

10] Now login to MySQL server 192.168.0.4 which is MySQL API node.

root@linserver:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 800 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

11] Now login to MySQL Managment node.. and check the MySQL Cluster;

root@fileserver:/home# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, Nodegroup: 0, Master)
id=3 @192.168.0.3 (Version: 5.0.21, Nodegroup: 0)
id=4 @192.168.0.4 (Version: 5.0.22, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 @192.168.0.3 (Version: 5.0.21)
id=8 @192.168.0.4 (Version: 5.0.22)

ndb_mgm>

12] How to start/stop, restart MySQL Cluster –

** Login to MGM node and then start MGMD node as…

root@fileserver:~# ndb_mgmd -f /var/lib/mysql-cluster_MGM/config.conf
root@fileserver:~# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 (not connected, accepting connect from 192.168.0.2)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

ndb_mgm>

*** How to start NDBD Node from MGM interface..

ndb_mgm> 2 STATUS
Node 2: not connected

ndb_mgm> 2 START
Start failed.
* 22: Error
* No contact with the process (dead ?).

ndb_mgm>

That’s it!! MySQL clustering is working…

Thank you,
Arun Bagul

MySQL Clustering – configuration and testing

By Arun Bagul | April 22, 2008 | Comments 1 comment

Similar Posts:

One Reply to “MySQL Clustering – configuration and testing”

Leave a Reply