MySQL NDB Cluster Installation

The following guide outlines installation of MYSQL NDB Cluster on UBUNTU Linux 20.04 (64 Bit). The NDB Cluster requires minimum of 3 machines, 2 for SQL Data Nodes and 1 for Management and SQL Node combined. This installation guide covers installation of NDB Cluster on 4 machines, 2 for Data nodes and 2 for Management and SQL nodes.

Following are the machines and IPs which will be used in this deployment

Installation and Configuration of MySQL Repo on all Nodes

Download the MySQL repo for Ubuntu 20.04 from the official MySQL download site https://dev.mysql.com/downloads/repo/apt/ on all Nodes. Install the repo file.

$ sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb

During installation the package manager will ask to select the type of mysql installation. Under the MySQL Server & Clusteroption select mysql-cluster-8.0. Leave rest options as it as and select OK to confirm.

Installation and Configuration of Management Nodes

Install the cluster management server package on the Management nodes A and B.

$ sudo apt install mysql-cluster-community-management-server

The Cluster Manager should be the first component launched in MySQL cluster. On the Cluster Manager node A and B, create the /var/lib/mysql-cluster directory and edit the configuration file /var/lib/mysql-cluster/config.ini.

$ sudo mkdir /var/lib/mysql-cluster
$ sudo vim /var/lib/mysql-cluster/config.ini

Paste the following text into text editor:

# TCP PARAMETERS
[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M
# Increasing the sizes of these 2 buffers beyond the default values
# helps prevent bottlenecks due to slow disk I/O.

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2  # Number of replicas
DataMemory=512M
MaxNoOfConcurrentOperations=100000
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
# Setting these parameters allows you to take advantage of real-time scheduling
# of NDB threads to achieve increased throughput when using ndbd.

# Management process options:
[ndb_mgmd]
hostname=192.168.100.10 # Hostname of the manager A
datadir=/var/lib/mysql-cluster  # Directory for the log files
NodeId=1

[ndb_mgmd]
hostname=192.168.100.11 # Hostname of the manager B
datadir=/var/lib/mysql-cluster  # Directory for the log files
NodeId=2


[ndbd]
hostname=192.168.100.12 # Hostname/IP of the first data node
datadir=/usr/local/mysql/data   # Remote directory for the data files
MaxNoOfExecutionThreads=8
NodeId=3

[ndbd]
hostname=192.168.100.13 # Hostname/IP of the second data node
datadir=/usr/local/mysql/data   # Remote directory for the data files
MaxNoOfExecutionThreads=8
NodeId=4

# SQL node options:
[mysqld]
hostname=192.168.100.10
NodeId=5

[mysqld]
hostname=192.168.100.11
NodeId=6

Start the manager by executing the ndb_mgmd binary and specifying its config file using the -f flag:

$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini

Before we create the service, we need to kill the running server:

$ sudo pkill -f ndb_mgmd

Open and edit the following systemd Unit file:

$ sudo vim /etc/systemd/system/ndb_mgmd.service

Paste in the following code:

[Unit]
Description=MySQL NDB Cluster Management Server
After=network.target auditd.service

[Service]
Type=forking
ExecStart=/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target

Reload systemd’s manager configuration using daemon-reload:

$ sudo systemctl daemon-reload

Enable the service so that the MySQL Cluster Manager starts on reboot:

$ sudo systemctl enable ndb_mgmd

Start the NBD management service:

$ sudo systemctl start ndb_mgmd

Verify that the NDB Cluster Management service is running:

$ sudo systemctl status ndb_mgmd

Installation and Configuration of Data Nodes

Download the Data Node, ndbd installer file DEB Package, NDB Data Node Binaries for Ubuntu Linux 18.04 (x86, 64-bit) from the the official MySQL Cluster download page. Install dependency libclass-methodmaker-perl and then install ndbd using dpkg on the Data nodes A and B.

$ sudo apt install libclass-methodmaker-perl
$ sudo dpkg -i mysql-cluster-community-data-node_7.6.11-1ubuntu18.04_amd64.deb

Create the data node configuration file /etc/my.cnf on both nodes A and B. Add the following configuration:

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.100.10,192.168.100.11 # location of cluster manager A,B

Create data directory on both A and B

$ sudo mkdir -p /usr/local/mysql/data

Create the systemd Unit file /etc/systemd/system/ndbd.service on both A and B for the data node service.

[Unit]
Description=MySQL NDB Data Node Daemon
After=network.target auditd.service

[Service]
Type=forking
ExecStart=/usr/sbin/ndbd
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target

Reload the systemd daemon, enable the ndbd service and start ndbd.

$ sudo systemctl daemon-reload
$ sudo systemctl enable ndbd
$ sudo systemctl start ndbd

Installation and Configuration of MYSQL Server and Client

Download the Debian bundle for Ubuntu Linux from the the official MySQL Cluster download page. Untar the bundle, install the dependencies and MYSQL packages.

$ sudo apt install libaio1 libmecab2
$ sudo dpkg -i mysql-common_7.6.11-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-cluster-community-client_7.6.11-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-client_7.6.11-1ubuntu18.04_amd64.deb
$ sudo dpkg -i mysql-cluster-community-server_7.6.11-1ubuntu18.04_amd64.deb

Installing the mysql-cluster-community-server requires a password to be set for the MYSQL root account.
Install the MYSQL server package.

$ sudo dpkg -i mysql-server_7.6.11-1ubuntu18.04_amd64.deb

Add the ndb cluster configuration in the /etc/mysql/my.cnf file.

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.100.10,192.168.100.11  # management servers A,B

Enable the mysql server to start on boot and restart the server to enable configuration changes.

$ sudo systemctl enable mysql
$ sudo systemctl restart mysql

References

https://dev.mysql.com/doc/refman/5.7/en/
https://www.apress.com/us/blog/all-blog-posts/what-is-mysql-ndb-cluster/15454530
https://www.digitalocean.com/community/tutorials/how-to-create-a-multi-node-mysql-cluster-on-ubuntu-18-04