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
