MySQL Cluster 7.4.12

Our purpose is install and configure MySQL cluster. For that we need four machines. I will describe the as following:

1 – Management server.
2 – Data server.
1 – SQL server.

The network topology will be as following:

As virtual environment I have used  VMWare workstation 10.1.
As server environment I have used Ubuntu 14.04 x64 server. Already installed, configured and updated to all virtual machines.

The IP addresses of the servers will be as following: nodeA) – Management server. nodeB) – Data node1 nodeC) – Data node2 nodeD) – SQL node.

First of all we must be registered in the site and download MySQL-Cluster package.

Select “Linux – Generic” and click to download button.

When download is finished, copy mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz file from Windows desktop to the nodeA via WinSCP. And after that copy this file via scp from nodeA to all other(nodeB, nodeC, nodeD) nodes.

Install libaio1 package to the all nodes:
root@nodeA:~# apt-get -y install libaio1
root@nodeB:~# apt-get -y install libaio1
root@nodeC:~# apt-get -y install libaio1
root@nodeD:~# apt-get -y install libaio1

Installation and configuration Management node

We will install ndb_mgmd and ndb_mgm to the management node nodeA.

root@nodeA:~# tar zxvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
root@nodeA:~# cd mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64/
root@nodeA:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cp bin/ndb_mgm* /usr/local/bin

Make this files executable:
root@nodeA:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cd /usr/local/bin/
root@nodeA:/usr/local/bin# chmod +x ndb_mgm*

Create configuration directory and configuration file:
root@nodeA:/usr/local/bin# mkdir /var/lib/mysql-cluster; cd /var/lib/mysql-cluster

config.ini file content will be as following:
aroot@nodeA:/var/lib/mysql-cluster# cat config.ini
[ndbd default]
NoOfReplicas=2    # Replication count number
DataMemory=80M    # Memory allocation size for data storage
IndexMemory=18M   # Memory allocation for index storage

[tcp default]
portnumber=2202   # Default port number which needed for communicate between nodes.

hostname=         # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

hostname=         # Hostname or IP address of data node
datadir=/usr/local/mysql/data   # Directory for this data node’s data files

hostname=         # Hostname or IP address of data node
datadir=/usr/local/mysql/data   # Directory for this data node’s data files

hostname=         # Hostname or IP address of SQL node

Add service to the startup. For that we will use /etc/rc.local file.
root@nodeA:~# cat /etc/rc.local | grep -v “^#” | grep -v ‘^$’
/usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster
exit 0

Installation and configuration data node

The following steps we will do for nodeB and nodeC(Just look at the hostnames):

root@nodeB:~# tar zxvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
root@nodeC:~# tar zxvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz

root@nodeB:~# cd mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64/
root@nodeC:~# cd mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64/

root@nodeB:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cp bin/ndbd /usr/local/bin
root@nodeC:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cp bin/ndbd /usr/local/bin

Make ndbd and ndbmtd files, executable:
root@nodeB:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cd /usr/local/bin
root@nodeB:/usr/local/bin# chmod +x ndb*

root@nodeC:~/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64# cd /usr/local/bin
root@nodeC:/usr/local/bin# chmod +x ndb*

Create data folder for each data nodes:
root@nodeB:/usr/local/bin# mkdir -p /usr/local/mysql/data
root@nodeC:/usr/local/bin# mkdir -p /usr/local/mysql/data

/etc/my.cnf file for nodeB and nodeC will be as following:
# cat /etc/my.cnf
ndbcluster                        # run NDB storage engine

ndb-connectstring=  # IP address or hostname of management server

Add data nodeB and nodeC to the startUP. For that we will use the /etc/rc.local file.
root@nodeB:~# cat /etc/rc.local | grep -v ‘^#’ | grep -v ‘^$’
exit 0

root@nodeC:~# cat /etc/rc.local | egrep -v ‘^$|^#’
exit 0

Installation and configuration SQL node

We need install MySQL server and for that we will use mysql user and group.

The following commands we will use in the nodeD server.

root@nodeD:~# groupadd mysql
root@nodeD:~# useradd -r -g mysql -s /bin/false mysql

Extract downloaded file and create symlink to the mysql:
root@nodeD:~# tar zxvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
root@nodeD:~# mv mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64 /usr/local/
root@nodeD:~# ln -s /usr/local/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64/ /usr/local/mysql

Go to the MySQL folder and install database with mysql user:
root@nodeD:~# cd /usr/local/mysql
root@nodeD:/usr/local/mysql# scripts/mysql_install_db –user=mysql

Set needed permissions to the server and data directories:
root@nodeD:/usr/local/mysql# chown -R root .
root@nodeD:/usr/local/mysql# chown -R mysql data
root@nodeD:/usr/local/mysql# chgrp -R mysql .

Copy mysql startup script to the startUp folder and add service to the startup:
root@nodeD:/usr/local/mysql# cp support-files/mysql.server /etc/init.d
root@nodeD:/usr/local/mysql# chmod +x /etc/init.d/mysql.server
root@nodeD:/usr/local/mysql# update-rc.d mysql.server defaults
Adding system startup for /etc/init.d/mysql.server …
/etc/rc0.d/K20mysql.server -> ../init.d/mysql.server
/etc/rc1.d/K20mysql.server -> ../init.d/mysql.server
/etc/rc6.d/K20mysql.server -> ../init.d/mysql.server
/etc/rc2.d/S20mysql.server -> ../init.d/mysql.server
/etc/rc3.d/S20mysql.server -> ../init.d/mysql.server
/etc/rc4.d/S20mysql.server -> ../init.d/mysql.server
/etc/rc5.d/S20mysql.server -> ../init.d/mysql.server

The content of the /etc/my.cnf file will be as following:
ndbcluster                        # run NDB storage engine

ndb-connectstring=  # IP address or hostname of management server

Add symlink to the binary files:
root@nodeD:~# ln -s /usr/local/mysql/bin/* /usr/sbin/

Note: You can set MySQL root password only after start MySQL service. And for that use the “mysql_secure_installation” command after “Start MySQL cluster” section.

Set MySQL root password:
root@nodeD:~# mysql_secure_installation
Set root password? [Y/n] Y
New password: newpass
Re-enter new password: newpass
Password updated successfully!
Reloading privilege tables..
… Success!

Remove anonymous users? [Y/n] Y
… Success!

Disallow root login remotely? [Y/n] Y
… Success!

Remove test database and access to it? [Y/n] Y
– Dropping test database…
… Success!

– Removing privileges on test database…
… Success!

Reload privilege tables now? [Y/n] Y
… Success!

Start MySQL cluster

First of all we should start management nodeA. And after that we should start nodeB and nodeC data nodes. At the end we should start nodeD SQL node.

Start management nodeA:
root@nodeA:~# /usr/local/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster

Start data nodes nodeB and nodeC:
root@nodeB:~# /usr/local/bin/ndbd
root@nodeC:~# /usr/local/bin/ndbd

At the end start nodeC SQL node:
root@nodeD:~# /etc/init.d/mysql.server start

It is the end to our cluster configuration. To check cluster configuration go to the Management (nodeA) server and use the following command to see cluster configuartion:
root@nodeA:~# 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    @  (mysql-5.6.31 ndb-7.4.12, Nodegroup: 0, *)
id=3    @  (mysql-5.6.31 ndb-7.4.12, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @  (mysql-5.6.31 ndb-7.4.12)

[mysqld(API)]   1 node(s)
id=4    @  (mysql-5.6.31 ndb-7.4.12)

Look at the status of all nodes:
ndb_mgm> 1 STATUS
Node 1: connected (Version 7.4.12)

ndb_mgm> 2 STATUS
Node 2: started (mysql-5.6.31 ndb-7.4.12)

ndb_mgm> 3 STATUS
Node 3: started (mysql-5.6.31 ndb-7.4.12)

ndb_mgm> 4 STATUS
Node 4: connected (Version 7.4.12)

Go to the SQL node and check Cluster status from the SQL:
root@nodeD:~# mysql -uroot –p’password’
*************************** 1. row ***************************
Type: ndbcluster
Name: connection
Status: cluster_node_id=4, connected_host=, connected_port=1186, number_of_data_nodes=2, number_of_ready_data_nodes=2, connect_count=0

Create new database with NDB engine and test this database:
mysql> CREATE DATABASE cluster;

mysql> USE cluster;

mysql> CREATE TABLE cluster_test (name VARCHAR(20), value VARCHAR(20)) ENGINE=ndbcluster;

mysql> INSERT INTO cluster_test (name,value) VALUES(‘some_name’,’some_value’);

mysql> SELECT * FROM cluster_test;

Go back to the Management node and look at the reports:
root@nodeA:~# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> all report memory;
Connected to Management Server at: localhost:1186
Node 2: Data usage is 1%(27 32K pages of total 2560)
Node 2: Index usage is 1%(24 8K pages of total 2336)
Node 3: Data usage is 1%(27 32K pages of total 2560)
Node 3: Index usage is 1%(24 8K pages of total 2336)