Setting Up a Basic 4 Machine Cluster

To start we will have the following machines:

One – Management server (mgm1)
One – MySQL Application Server (app1)
Two – NDB servers (ndb1, ndb2)

Append to /etc/hosts on each machine

@everyhost:~#vi /etc/hosts

192.168 100.104        mgm1  # Management server   
192.168.100.102        app1  # MySQL node Server  1 
192.168.100.106        ndb1  # first ndb       
192.168.100.107        ndb2  # second ndb

Copy mysql.tgz to each machine

@mgm1:~#sftp user@ubuntu ( Compiling Server )
@mgm1:~#get mysql.tgz

Login to mgm1 server and extract files

@mgm1:~#tar xzvf mysql.tgz -C /usr/local/mysql

Create config.ini file

@mgm1: /usr/local/mysql/# vi config.ini

[ndbd default]
NoOfReplicas = 2
DataDir = /usr/local/mysql/mysql-cluster

[ndb_mgmd]
NodeId = 1
PortNumber = 1186
HostName = mgm1
DataDir = /usr/local/mysql/mysql-cluster

[ndbd]
NodeId = 11
HostName = ndb1
[ndbd]
NodeId = 12
HostName = ndb2

[api]
NodeId = 31
HostName = app1

[tcp default]
SendBufferMemory = 8M
ReceiveBufferMemory = 8M

 Init-scripts (automatic startup at boot) for Management

@mgm1: /usr/local/mysql/# vi ndbmgm_daemon

#!/bin/bash
# ndb_mgmd   Startup script for the ndb_mgmd
# chkconfig: 2 50 22

ndb_mgmd=/usr/local/mysql/bin/ndb_mgmd
config_file=/usr/local/mysql/config.ini

if ! test -x $ndb_mgmd; then
    echo "Can't execute $ndb_mgmd"
    exit 1
fi

initial() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndb_mgmd" | wc -l`
    if [ $pids -eq 0 ]; then
        $ndb_mgmd -f $config_file --reload
        echo "NDB_MGM daemon started"
    else
        echo "NDB_MGM daemon is already running."
    fi
}

start() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndb_mgmd" | wc -l`
    if [ $pids -eq 0 ]; then
        $ndb_mgmd -f $config_file

        echo "NDB_MGM daemon started"
    else
        echo "NDB_MGM daemon is already running."
    fi
}

stop() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndb_mgmd" | wc -l`
    if [ $pids -ne 0 ]; then

        pids=`ps aux | grep -iv "grep" | grep -i "$ndb_mgmd" | awk '{ print $2 }'`
        for pid in $(echo $pids); do
            kill $pid 2> /dev/null
        done

        sleep 3

        pids=`ps aux | grep -iv "grep" | grep -i "$ndb_mgmd" | wc -l`
        if [ $pids -eq 0 ]; then
            echo "NDB_MGM daemon stopped."
        else
               echo "Could not stop NDB_MGM daemon."
        fi
    else
        echo "NDB_MGM daemon is not running."
    fi
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    initial)
        initial
        ;;
    restart)
        stop
        sleep 3
        start
        ;;
    *)
        echo $"Usage: $0 {start|stop|restart|initial}" >&2
        ;;
esac
exit 0

Chmod Script

@mgm1: /usr/local/mysql/# chown root:mysql config.ini
@mgm1: /usr/local/mysql/# chmod 755 ndbmgm_deamon

Launching Script

@mgm1: /usr/local/mysql/# ./ndbmgm_daemon initial

Verifying Management1 Console

@mgm1: /usr/local/mysql/# bin/ndb_mgm -e show

Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11 (not connected, accepting connect from ndb1)
id=12 (not connected, accepting connect from ndb2)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.100.101  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   1 node(s)
id=31 (not connected, accepting connect from app1)

Configuring ndb1 & ndb2
// Login to ndb1 & ndb2 servers and extract files
// Create user as above @ mgm1
// Copy and extracted MySQL

@[ndb1 & ndb2]: # tar xzf mysql.tgz -C /usr/local/mysql

Init-Scripts
//(automatic startup at boot) for ndb_daemon

@[ndb1 & ndb2]: /usr/local/mysql/# vi ndb_daemon

#!/bin/bash
# ndbd   Startup script for the NDBD
# chkconfig: 2 50 22
#

ndbd_bin=/usr/local/mysql/bin/ndbd
mgmserver=mgm1

if ! test -x $ndbd_bin; then
    echo "Can't execute $ndbd_bin"
    exit 1
fi

initial() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndbd_bin" | wc -l`
    if [ $pids -eq 0 ]; then
        $ndbd_bin -c $mgmserver --initial
        echo "NDB daemon initial."
    else
        echo "NDB daemon is already running."
    fi
}

start() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndbd_bin" | wc -l`
    if [ $pids -eq 0 ]; then
        $ndbd_bin -c $mgmserver
        echo "NDB daemon started."
    else
        echo "NDB daemon is already running."
    fi
}

stop() {
    pids=`ps aux | grep -iv "grep" | grep -i "$ndbd_bin" | wc -l`
    if [ $pids -ne 0 ]; then

        pids=`ps aux | grep -iv "grep" | grep -i "$ndbd_bin" | awk '{ print $2 }'`
        for pid in $(echo $pids); do
               kill $pid 2> /dev/null
        done

        sleep 3

        pids=`ps aux | grep -iv "grep" | grep -i "$ndbd_bin" | wc -l`
        if [ $pids -eq 0 ]; then
            echo "NDB daemon is stopped."
        else
            echo "Could not stop NDB daemon."
        fi
    else
        echo "NDB daemon is not running."
    fi
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    initial)
        initial
        ;;
    restart)
        stop
        sleep 3
        start
        ;;
    *)
        echo $"Usage: $0 {start|stop|restart|initial}" >&2
        ;;
esac
exit

Owning the script
//on ndb1 & ndb2 servers
// adding required user & group

@ndb_servers: /usr/local/mysql/# chown root ndb_daemon
@ndb_servers: /usr/local/mysql/# chmod 755 ndb_daemon

Start ndb_daemon scripts
//on ndb1 & ndb2 servers

@ndb1:/usr/local/mysql# ./ndb_daemon initial / start
2014-08-11 21:40:23 [ndbd] INFO     -- Angel connected to 'mgm1:1186'
2014-08-11 21:40:23 [ndbd] INFO     -- Angel allocated nodeid: 11
NDB daemon started on mgm1

@ndb2:/usr/local/mysql# ./ndb_daemon initial /start
2014-08-11 21:42:39 [ndbd] INFO     -- Angel connected to 'mgm1:1186'
2014-08-11 21:42:39 [ndbd] INFO     -- Angel allocated nodeid: 12
NDB daemon started on mgm1 initial

Verifying Management1 server

@mgm1:/usr/local/mysql# bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     4 node(s)
id=11   @192.168.100.106  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=12   @192.168.100.107  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.100.104  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=31  (not connected, accepting connect from app1)

// As we can see that ndbd id=11 & id=12 have started

Configuring App1
// Login to app1 server and extract files

@app1:~#tar xzf mysql.tgz -C /usr/local/mysql

Add MySQL user & group
// Adding mysql user and group;
// You will need to do this on all machines where MySQL Node will be running

@app1:~# /usr/local/mysql# groupadd mysql
@app1:~# /usr/local/mysql# useradd -g mysql mysql
@app1:~# /usr/local/mysql# chown -R mysql.mysql .

Configuring MySQL
// Creating my.cnf file

@app1:~# /usr/local/mysql/# vi my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
server-id = 31
ndbcluster

# location of management servers
ndb-connectstring = "mgm1"

[mysql_cluster]
# location of management servers
ndb-connectstring = "mgm1"

Starting the Application Server (MySQLD)

@app1:~# /usr/local/mysql/# ./mysqld start

Verifying mgm1 server

@mgm1:~# /usr/local/mysql/# bin/ndb_mgm --initial
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @192.168.100.106  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=12   @192.168.100.107  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.100.104  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=31  @192.168.100.102  (mysql-5.6.19 ndb-7.3.6)
// We can see that API id=31 server started
// Verifying login to mysql app1

Add each user and grant permission on current mysql server

@mgm1:~# /usr/local/mysql/# bin/mysql -u root -p -h app1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.6.19-ndb-7.3.6 Source distribution

Copyright (c) 1000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database clusterdb;
Query OK, 1 row affected (0.04 sec)

mysql> use clusterdb;
Database changed
mysql> create table testcluster(id int not null auto_increment, message text not null, primary key(id)) engine=ndbcluster;
Query OK, 0 rows affected (1.08 sec)

mysql> insert into testcluster(message) values('first test our cluster!');
Query OK, 1 row affected (0.01 sec)

mysql> select * from testcluster;
+----+-------------------------+
| id | message                 |
+----+-------------------------+
|  1 | first test our cluster! |
+----+-------------------------+
1 row in set (0.00 sec)

mysql>