MySQL Cluster Installation

MySQL Cluster is a technology that enables clustering of in-memory databases in a shared-nothing system. The shared-nothing architecture allows the system to work with very inexpensive hardware, and without any specific requirements on hardware or software. It also does not have any single point of failure because each component has its own memory and disk.

A MySQL Cluster consists of a set of computers, each running a one or more processes which may include a MySQL server, a data node, a management server, and (possibly) a specialized data access programs. All these programs work together to form a MySQL Cluster.

There are three types of cluster nodes, and in a minimal MySQL Cluster configuration, there will be at least three nodes, one of each of these types:

Management node (MGM nod.): The role of this type of node is to manage the other nodes within the MySQL Cluster.

Data node: This type of node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments.

SQL node: This is a node that accesses the cluster data. An SQL node is typically started with the command mysqld --ndbcluster or by using mysqld with the ndbcluster option added to my.cnf.

NDB stands for “Network Database.”

The setup

1. The cluster setup has four nodes, each on a separate host, and each with a fixed network address on a typical Ethernet as shown here:

Quote

Node IP Address Function
Management (MGM) node 192.168.0.1 ndb_mgmd, ndb_mgm
MySQL server (SQL) node 192.168.0.2 mysqld –ndb-cluster
Data (NDBD) node "A" 192.168.0.3 ndbd
Data (NDBD) node "B" 192.168.0.4 ndbd

Packages Specification:

Package requirement:

192.168.0.2 - MySQL server (SQL) node
MySQL-server-5.1.11-0.glibc23
MySQL-shared-5.1.11-0.glibc23
MySQL-client-5.1.11-0.glibc23

192.168.0.1 - Management (MGM) node
MySQL-ndb-tools-5.1.11-0.glibc23
MySQL-ndb-management-5.1.11-0.glibc23

192.168.0.3 - Data (NDBD) node "A"
MySQL-ndb-tools-5.1.11-0.glibc23
MySQL-client-5.1.11-0.glibc23
MySQL-ndb-storage-5.1.11-0.glibc23
MySQL-server-5.1.11-0.glibc23 ( Optional )

192.168.0.4 - Data (NDBD) node "B"
MySQL-ndb-tools-5.1.11-0.glibc23
MySQL-client-5.1.11-0.glibc23
MySQL-ndb-storage-5.1.11-0.glibc23
MySQL-server-5.1.11-0.glibc23 ( Optional )

Step by Step configuration:

Each data node or SQL node requires a my.cnf file that provides two pieces of information:
- A connect string to find the MGM node
- A line which says the MySQL server on this host to run in NDB mode.

The my.cnf file for data node (ndb)

For each data node and SQL node in our example setup(i.e. Data (NDBD) node "A" 192.168.0.3 & Data (NDBD) node"B" 192.168.0.4) my.cnf should look like this:

# Options for mysqld process:
[MYSQLD]
ndbcluster # run NDB engine
ndb-connectstring=192.168.0.1 # location of MGM node

# Options for ndbd process:
[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.1 # location of MGM node

Now, you need to start the actual cluster:

The Management Node configuration:

You need to create a directory in MGM node to in which the configuration file can be found and then to create file it self.

# mkdir /var/lib/mysql-cluster
# cd /var/lib/mysql-cluster
# vi config.ini

# Options affecting ndbd processes on all data nodes:
[NDBD DEFAULT]
NoOfReplicas=2 # Number of replicas


# TCP/IP options:
[TCP DEFAULT]

# Management process options:
[NDB_MGMD]
hostname=192.168.0.1 # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node logfiles

# Options for data node "A":
[NDBD]
# (one [NDBD] section per data node)
hostname=192.168.0.3 # Hostname or IP address
DataDir=/var/lib/mysql-cluster # Directory for this data node's datafiles
BackupDataDir=/var/lib/mysql-cluster/backup
#DataMemory=256M # Set this as per your server h/w


# Options for data node "B":
[NDBD]
hostname=192.168.0.4 # Hostname or IP address
DataDir= /var/lib/mysql-cluster # Directory for this data node's datafiles
BackupDataDir=/var/lib/mysql-cluster/backup
#DataMemory = 256M # Set this as per your server h/w

# SQL node options:
[MYSQLD]
hostname=192.168.0.2 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[MYSQLD] # for API
[MYSQLD]
[MYSQLD]

Then, you need to start a cluster which as been configured; each cluster node process must be started separately.

It is recommended that the management node be started first, followed by the storage nodes, and then finally by any SQL nodes:

In MGM node (192.168.0.1) pass the following command:

# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

-OR-

# cd /var/lib/mysql-cluster
# ndb_mgmd

You must need to tell mgmd to find the configuration file.

Now, start the ndbd process in each data node A and B (192.168.0.3 & 192.168.0.4)

# ndbd --initial

Then, start mysql server process on the SQL node (192.168.0.2)

# service mysql start

Verify that SQL node is started with support of ndb cluster:

# mysql –u root –p

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.1.11-beta

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

mysql> SHOW ENGINES\G
*************************** 10. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO
************************************************

If all has been setup correctly, the cluster should be operational.

Type the below mentioned command from Management Node:

# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.0.4 (Version: 5.1.11, Node group: 0)
id=3 @192.168.0.3 (Version: 5.1.11, Node group: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.1 (Version: 5.1.11)

[mysqld(API)] 3 node(s)
id=4 @192.168.0.2 (Version: 5.1.11)

ndb_mgm>

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db:
Command: Daemon
Time: 1
State: Waiting for event from ndbcluster
Info: NULL
*************************************************

How to shutdown and restart the cluster:

In MGM node use the below mentioned command:

# ndb_mgm -e shutdown

The above command causes the ndb_mgm, ndb_mgmd, and any ndbd processes to terminate gracefully.

To restart the cluster:

From MGM node (192.168.0.1)

# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

From Data Node (192.168.0.3 & 192.168.0.4)

# ndbd (do not use –initial as it is used only first time)

From SQL node:

# service mysql start