Installation and Configuration of MySQL Cluster on CentOS 7

MySQL Cluster is designed to provide a MySQL compatible database with high availability and low latency. The MySQL Cluster technology is implemented through the NDB (Network DataBase) and NDBCLUSTER storage engines and provides shared-nothing clustering and auto-sharding for MySQL database systems.
To implement a MySQL Cluster, we have to install three types of nodes. Each node type will be installed on its own server. The components are:
1. Management Node - NDB_MGMD/MGM
    The Cluster management server is used to manage the other node of the cluster. We can create and configure new nodes, restart, delete, or backup nodes on the cluster from the management node.
2. Data Nodes - NDBD/NDB
    This is the layer where the process of synchronizing and data replication between nodes happens.
3. SQL Nodes - MySQLD/API
    The interface servers that are used by the applications to connect to the database cluster.
We will configure the management node, two data nodes, and two SQL nodes.

Prerequisites

• The OS is CentOS 7 - 64bit.

• 3 CentOS servers or virtual machines. I will use the hostnames and IP addresses as shown below:

• Management Node

        node0 = 192.168.34.3

• Data Nodes

        node1 = 192.168.34.4

        node2 = 192.168.34.5

• SQL Nodes

        node3 = 192.168.34.6

        node4 = 192.168.34.7

Step 1 - Setup Management Node

The first step is to create the "Management Node" with CentOS 7 node0 and IP 192.168.34.3. Make sure you are logged into the node0 server as the root user.

A. Download the MySQL Cluster software

Download it from the MySQL site with wget.

# cd ~
# wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.24-1.el7.x86_64.rpm-bundle.tar

# tar –xvf MySQL-Cluster-gpl-7.4.24-1.el7.x86_64.rpm-bundle.tar

B. Install and Remove Packages

Before you install the rpm package for MySQL Cluster, you need to install perl-Data-Dumper that is required by the MySQL-Cluster server. And you need to remove MariaDB-libs before we can install MySQL Cluster.

# yum -y install perl-Data-Dumper

# yum -y remove mariadb-libs

C. Install MySQL Cluster

Install MySQL Cluster package with these rpm commands:

cd ~

rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm

rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm

rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Note!! Do step A to C on all nodes on the clusters

D. Configure MySQL Cluster

Create a new directory for the configuration files.

mkdir -p /var/lib/mysql-cluster

Then create a new configuration file for the cluster management named "config.ini" in the mysql-cluster directory.

cd /var/lib/mysql-cluster

vi config.ini

Paste the configuration below:

[ndb_mgmd default]

# Directory for MGM node log files

DataDir=/var/lib/mysql-cluster

 

[ndb_mgmd]

#Management Node node0

HostName=192.168.34.3

 

[ndbd default]

NoOfReplicas=2      # Number of replicas

DataMemory=256M     # Memory allocate for data storage

IndexMemory=128M    # Memory allocate for index storage

#Directory for Data Node

DataDir=/var/lib/mysql-cluster

 

[ndbd]

#Data Node node1

HostName=192.168.34.4

 

[ndbd]

#Data Node node2

HostName=192.168.34.5

 

[mysqld]

#SQL Node node1

HostName=192.168.34.4

 

[mysqld]

#SQL Node node2

HostName=192.168.34.5

Save the file and exit.

E. Start the Management Node

Next start the management node with the command below:

# ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini

The result should be similar to this:

MySQL Cluster Management Server mysql-5.6.44 ndb-7.4.24

2019-06-06 10:20:49 [MgmtSrvr] INFO     -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...

2019-06-06 10:20:49 [MgmtSrvr] INFO     -- Sucessfully created config directory

The management node is started, now you can use command "ndb_mgm" to monitor the node:

# ndb_mgm

   show

You can see the management node has been started with: mysql-5.6 and ndb-7.4.

Step 2 - Setup the MySQL Cluster Data Nodes

We will use 2 CentOS servers for the Data Nodes.

·         node1 = 192.168.34.4

·         node2 = 192.168.34.5

Repeat A to C in Step 1

D. Configure Data Node

Create a new configuration file in the /etc directory with the vi editor:

# vi /etc/my.cnf

Paste configuration below:

[mysqld]

ndb-nodeid=5

ndbcluster

# Define default Storage Engine used by MySQL

default_storage_engine=ndbcluster

ndb-connectstring=192.168.34.3

[mysql_cluster]

ndb-connectstring=192.168.34.3     # IP address of Management Node

Save the file and exit.

Then create the new directory for the database data that we defined in the management node config file "config.ini".

# mkdir -p /var/lib/mysql-cluster

Now start the data node/ndbd:

# ndbd

NB!! Make sure firewall is disabled or configured to allow access on port 1186

Results:

E. Redo step 2.A - 2.D on node2 server.

As we have 2 data nodes, redo the steps 2.A - 2.D on our second data node.

Step 3 - Setup SQL Node

This step contains the setup for the SQL Node that provides the application access to the database. We use the 2 CentOS servers of data nodes for the SQL Nodes:

·         node1 = 192.168.34.6

·         node2 = 192.168.34.7

Repeat A to C in Step 1

D. Configure the SQL Node

Create a new my.cnf file in the /etc directory:

# vi /etc/my.cnf

Paste configuration below:

[mysqld]

ndbcluster

ndb-connectstring=192.168.34.3     # IP address of Management Node

[mysql_cluster]

ndb-connectstring=192.168.34.3     # IP address of Management Node

Save the file and exit.

Start the SQL Node by starting the MySQL server:

# mysqld_safe --user=mysql &

NB!! Do not start mysql as root

E. Redo step 3.A - 3.D on node2 server.

Please redo the steps 3.A - 3.D on the second mysql server (node2).

The SQL node is started, now you can use command "ndb_mgm" to monitor the node:

# ndb_mgm

show