How to install MYSQL NDB cluster

In the tutorial we will install MYSQL Network Database cluster (NDB) using 4 nodes.

Prerequisites

To complete this tutorial, you'll need the following:

• 1 management node
• 2 data nodes
• 1 SQL node
• mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64

Naming

db2004.dfw.ppnet = SQL node

db2005.dfw.ppnet = 1st data node

db2006.dfw.ppnet= 2nd data node

db2008.dfw.ppnet= management node

Hardware/software requirements

I am running this in a VM environment. All nodes have 1Gb of RAM and 20GB of disk. To run this in a productions environment please refer to the MSQL NDB cluster documentation.

free -h
              total        used        free      shared  buff/cache   available
Mem:           996M        254M        167M         10M        574M        578M
Swap:          1.0G        5.4M        1.0G

df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            488M     0  488M   0% /dev
tmpfs           100M   11M   90M  11% /run
/dev/sda1        19G  9.0G  8.8G  51% /
tmpfs           499M     0  499M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           499M     0  499M   0% /sys/fs/cgroup
tmpfs           100M     0  100M   0% /run/user/2001

All 4 nodes are running Debian stretch

lsb_release -a
No LSB modules are available.
Distributor ID:	Debian
Description:	Debian GNU/Linux 9.5 (stretch)
Release:	9.5
Codename:	stretch

Networking

IF you want to use the node FQDN you need to have a DNS server or update the /etc/hosts file; othewise, you can you the node IP address. For this tutorial We are gong to use the node FQDN.

• SQL node

127.0.0.1       localhost
10.192.16.62    db2004.dfw.ppnet        db2004

• 1st data node

127.0.0.1       localhost
10.192.16.63    db2005.dfw.ppnet        db2005

• 2nd data node

127.0.0.1       localhost
10.192.16.64    db2006.dfw.ppnet        db2006

• management node

127.0.0.1       localhost
10.192.16.61    db2008.dfw.ppnet        db2008

Installation

login to each node and copy the scrip for each node to the appropriate node

Management node

sudo vi mgmt_node.sh

#!/bin/bash
file="config.ini"
#create a folder called files under /tmp/
echo " Creating folder"
sleep 3
cd /tmp/
sudo mkdir files 
cd files
#download the ndb_cluster software 
echo "Download ndb_cluster software"
sleep 3
sudo wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.6/mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
echo "unzip the tar file "
sleep 3
sudo tar -xvf mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
#copy files"
echo "copy ndb and ndb_mgmd to /usr/local/bin"
sleep 3
cd mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64
cd bin
sudo cp ndb_mgm /usr/bin/
sudo cp ndb_mgmd /usr/bin/
cd /usr/bin/
sudo chmod 755 ndb_mg*
#create the configuration file
echo " Create /var/lib/mysql-cluster/config.ini"
sleep 3
sudo mkdir /var/lib/mysql-cluster/
cd /var/lib/mysql-cluster
if [ ! -f "$file" ] ; then
        # if not create the file
cat > $file << EOF
[ndb_mgmd]
# Management process options:
hostname=db2008.dfw.ppnet  # Hostname of the manager
datadir=/var/lib/mysql-cluster  # Directory for the log files
[ndbd]
hostname=db2005.dfw.ppnet    # Hostname of the first data node
datadir=/usr/local/mysql/data   # Remote directory for the data files
[ndbd]
hostname=db2006.dfw.ppnet    # Hostname of the second data node
datadir=/usr/local/mysql/data   # Remote directory for the data files
[mysqld]
# SQL node options:
hostname=db2004.dfw.ppnet  # Hostname of the MySQL server/client node
EOF
    else echo "$file existe"
    fi
#Start the manegement node
echo"starting the manegement node"
sleep 3
cd
sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/
#make the process to start a boot
echo"start ndb_mgmd at boot"
sleep 3
sudo echo "ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/" > /etc/init.d/ndb_mgmd
sudo chmod 755 /etc/init.d/ndb_mgmd

Make the script executable

sudo chmod +x mgmt_node.sh

Run the script

sudo ./mgmt_node.sh

Set back and relax

AFter the scrpt complete type command

ndb_mgm 

The output should look the same as below

-- NDB Cluster -- Management Client --
ndb_mgm> 

At the prompt, type "show" and you should see

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2 (not connected, accepting connect from db2005.dfw.ppnet)
id=3 (not connected, accepting connect from db2006.dfw.ppnet)
[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.192.16.61  (mysql-5.7.23 ndb-7.6.7)
[mysqld(API)]	1 node(s)
id=4 (not connected, accepting connect from db2004.dfw.ppnet)

We can see that the management node is up and running and the data nodes and SQL node are stay down. In the next session we are going to setup the data nodes

Data node

Create a file on you data nodes and copy and paste the script below.

sudo vi data_node.sh

#!/bin/bash
#create a folder called files under /tmp/
echo " Creating folder"
sleep 3
cd /tmp/
sudo mkdir files 
cd files
#download the ndb_cluster software 
echo "Download ndb_cluster software"
sleep 3
sudo wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.6/mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
echo "unzip the tar file "
sleep 3
sudo tar -xvf mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
cd  mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64/bin
#copy files ndbd and ndbmtd to /usr/local/bin
sudo cp ndbd /usr/local/bin
sudo cp ndbmtd /usr/local/bin
cd /usr/local/bin
sudo chmod 755 ndb*
#create the my.cnf file /etc/my.cnf
cd /etc/
if [ ! -f "$file" ] ; then
        # if not create the file
cat > $file << EOF
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=db2008.dfw.ppnet
[mysql_cluster]
ndb-connectstring=db2008.dfw.ppnet
EOF
else echo "$file existe"
    fi
#Create the data directory 
sudo mkdir -p /usr/local/mysql/data
#start the service
cd
ndbd

save the file and close it. Make the file executable

sudo chmod +x data._node.sh

run the file

sudo ./data_node.sh

After the installation you should get the output below

2018-09-19 00:06:16 [ndbd] INFO     -- Angel connected to 'db2008.dfw.ppnet:1186'
2018-09-19 00:06:16 [ndbd] INFO     -- Angel allocated nodeid: 2

Repeat the above steps for each data node. Once done, login to your management node to check the 2 data nodes

• check the management server

ppaul@db2008:~$ 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	@10.192.16.63  (mysql-5.7.23 ndb-7.6.7, Nodegroup: 0)
id=3	@10.192.16.64  (mysql-5.7.23 ndb-7.6.7, Nodegroup: 0, *)
[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.192.16.61  (mysql-5.7.23 ndb-7.6.7)
[mysqld(API)]	1 node(s)
id=4 (not connected, accepting connect from db2004.dfw.ppnet)
ndb_mgm>

We see that the 2 data nodes are up and running. In the next step, we will setup the mysql_node

SQL node

Login to you sql node (my case db2004) as root and create a file called "sql_node.sh" and copy and paste the content below into the file.

sudo vi sql_node.sh

#!/bin/bash
###sql node
##run as root##
file="my.cnf"
#Create the mysql user and group
echo "Add user and group mysql"
sleep 3
groupadd mysql
useradd -g mysql mysql
#create a folder called files under /tmp/
echo "Creating folder"
sleep 3
cd /tmp/
mkdir files
cd files
#Download the ndb_cluster software
echo "Download ndb_cluster software"
sleep 3
sudo wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.6/mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
echo "unzip the tar file "
sleep 3
sudo tar -xvf mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64.tar.gz
mv mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64/ /usr/local/
#Create symbolic link
echo "reate symbolic link"
cd /usr/local
ln -s mysql-cluster-gpl-7.6.7-linux-glibc2.12-x86_64/ /usr/local/mysql
apt-get install libaio1
apt-get install libaio-dev
apt-get install libnuma1
#Change to mysql directory, initialize Mysql
echo "Initilize MYSQL"
sleep 3
cd /usr/local/mysql/bin/
./mysqld --initialize \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
#Relocate the bin repectory under /usr/local/mysql/bin
cd /usr/local/mysql/bin
mv * /usr/bin
cd ../
rm -fr /usr/local/mysql/bin
ln -s /usr/bin /usr/local/mysql/bin
#Set permission for root and mysql users
echo "Setting permission"
sleep 3
cd /usr/local/
chown -R root:root mysql
cd /usr/local/mysql
chown -R mysql:mysql data/
#set mysqld to start at boot
echo "mysqld sartup"
sleep 3
cd /usr/local/mysql/
cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server
#create the my.cnf file /etc/my.cnf
cd /etc/
if [ ! -f "$file" ] ; then
        # if not create the file
cat > $file << EOF
[mysqld]
ndbcluster
# IP address of the cluster management node
ndb-connectstring=db2008.dfw.ppnet
[mysql_cluster]
ndb-connectstring=db2008.dfw.ppnet
EOF
else echo "$file existe"
fi
#Start MYSQL"
echo "starting mysql"
sleep 3
/etc/init.d/mysql.server start

Save the file and make it executable

chmod +x sql_node.sh

Run the file

./sql_node.sh

After completion, you will see a line that says; "[Note] A temporary password is generated for root@localhost: taE2*BEj;,:N" Copy and paste the temporary password somewhere. we will change this in the next step.