MYSQL Cluster 5.1 on single node

MYSQL CLUster5.1 on single node

MySQL Clustering How-to - Easy step by step guide
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.

Assumptions:
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.2.159 ndb_mgmd, ndb_mgm MySQL server (SQL) node 192.168.2.160 mysqld –ndb-cluster Data (NDBD) node "A" 192.168.2.161 ndbd Data (NDBD) node "B" 192.168.2.162 ndbd

Consider that each host is with enough ram and good hardware configuration.
Packages Specification:
MySQL-server-VERSION.i386.rpm
The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.
MySQL-client-VERSION.i386.rpm
The standard MySQL client programs. You probably always want to install this package
MySQL-bench-VERSION.i386.rpm
Tests and benchmarks. Requires Perl and the DBI and DBD::mysql modules.
MySQL-devel-VERSION.i386.rpm

The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.
MySQL-shared-VERSION.i386.rpm
This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. It contains single-threaded and thread-safe libraries. If you install this package, do not install the MySQL-shared-compat package.
MySQL-ndb-storage-VERSION.i386.rpm
NDB Cluster - Storage engine (contains the NDB Data Node program)
MySQL-ndb-management-VERSION.i386.rpm
NDB Cluster - Storage engine management (contains the NDB Management Server)
MySQL-ndb-tools-VERSION.i386.rpm
NDB Cluster - Storage engine basic tools
MySQL-ndb-extra-VERSION.i386.rpm
NDB Cluster - Storage engine extra tools
Cont...


Package requirement:

192.168.2.159 - 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.2.160 - Management (MGM) node
MySQL-ndb-tools-5.1.11-0.glibc23
MySQL-ndb-management-5.1.11-0.glibc23

192.168.2.161 - 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.2.162 - 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.2.161 &
Data (NDBD) node"B" 192.168.2.162) my.cnf should look like this:

Quote:

# Options for mysqld process: [MYSQLD] ndbcluster # run NDB engine ndb-connectstring=192.168.2.160 # location of MGM node # Options for ndbd process: [MYSQL_CLUSTER] ndb-connectstring=192.168.2.160 # 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.

Quote:

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


# 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.2.160 # 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.2.161 # 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.2.162 # 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.2.159 # 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.2.160) pass the following command:
# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
# 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.2.161 & 192.168.2.162)

# ndbd –initial Then, start mysql server process on the SQL node (192.168.2.159) # /etc/rc.d/init.d/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 Managment 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.2.161 (Version: 5.1.11, Nodegroup: 0) id=3 @192.168.2.162 (Version: 5.1.11, Nodegroup: 0, Master) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.2.160 (Version: 5.1.11) [mysqld(API)] 3 node(s) id=4 @192.168.2.161 (Version: 5.1.11) id=5 @192.168.2.162 (Version: 5.1.11) id=6 @192.168.2.159 (Version: 5.1.11) ndb_mgm> Here, you will find three API node as we have also configurd it as a API in config.ini. You can obtain some information from a MySQL server running as a Cluster SQL node using SHOW STATUS in the mysql client, as shown here: mysql> SHOW STATUS LIKE 'ndb%'; +-----------------------------+---------------+ | Variable_name | Value | +-----------------------------+---------------+ | Ndb_cluster_node_id | 6 | | Ndb_connected_host | 192.168.2.160 | | Ndb_connected_port | 1186 | | Ndb_number_of_storage_nodes | 2 | +-----------------------------+---------------+ 4 rows in set (0.00 sec) 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: Quote: # 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.2.160) # ndb_mgmd -f /var/lib/mysql-cluster/config.ini From Data Node (192.168.2.161 & 192.168.2.162) Quote: # ndbd (do not use –initial as it is used only first time) From SQL node: # mysqld