Introduction

High availability in relation to a database means that the particular database has the ability to cope with errors from the host. It is also important for the database to be able to recover from the failures from the MySQL, the operating system or the hardware maintenance that may otherwise cause a downtime.

High availability solutions depend on the type of application being deployed, your environment’s best practices or the level of availability required. With MySQL, just like the typical cluster environment, there are:

Management node which is used to monitor and configure the clients
Data nodes which are used to store data and share/replicate
SQL nodes which are the interfaces to connect to all the nodes

Before You Get Started

At least three VPS instances running Ubuntu 18.04 configured in a virtual private network environment

Non root users with sudo privileges configured on each VPS.
wget and tar packages installed in the VPS.

Steps

Update the system packages in all 3 nodes.

$ sudo apt update -y && sudo apt upgrade -y

Configure the management node

$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz

Extract the compressed file into a new folder.

$ mkdir mysql
$ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1

Copy the cluster management server binary files; ndb_mgm and ndb_mgmd into your local binary files folder and change the mode of the files to executable.

$ sudo cp /mysql/bin/ndb_mgm* /usr/local/bin
$ sudo chmod +x /usr/local/bin/ndb_mgm*

Create a configuration file called config.ini in a newly created mysql-cluster directory for the management cluster node. This file will identify the SQL node and the management node’s IP addresses.

It will also identify the data node’s IP address and their data directories.

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

Special Note: Remember to replace the IP addresses with ones in your environment setup. As for the below configuration code, if you only have one data node, the NoOfReplicas should be set to 1 because you only have one data node where a table replica will be stored.

[ndbd default]
NoOfReplicas=1
DataMemory=80M
IndexMemory=18M
[mysqld default]
[ndb_mgmd default]
[tcp default]
# Cluster Control / Management node
[ndb_mgmd]
hostname=192.168.56.10
# Data Node
[ndbd]
hostname=192.168.56.11
DataDir= /var/lib/mysql-cluster
# Data Node 1
[ndbd]
HostName=192.168.56.12
DataDir=/var/lib/mysql-cluster
# SQL Node
[mysqld]
hostname=192.168.56.40

Now start the cluster management node and enable it on start up. Remember all the other nodes ought to be active otherwise you will receive an error on the non-existing IP addresses.

$ sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/MySQL Cluster Management Server mysql-5.6.31 ndb-7.4.12$ sudo echo'ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-cluster/' >> /etc/rc.local

Special Note: If you get a permissions error, try logging in as root (sudo su) and run the command again.

Configure the data node

Install the libaio1 package from linux which enables making multiple simultaneous system I/O calls.

$ sudo apt install libaio1

Create a new group and user called mysql

$ groupadd mysql
$ useradd -g mysql mysql

Download the mysql-cluster package just as you did in the management node.

$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
$ mkdir mysql
$ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1

This time, you will move the entire mysql folder to /usr/local directory.

$ sudo mv mysql/ /usr/local

Change directory into the moved folder and run the system databases creation script mysql_install_db

$ cd /usr/local/mysql
$ ./scripts/mysql_install_db --user=mysql

Copy the mysql service file to the init.d folder and enable the mysql service on start up.

$ sudo cp support-files/mysql.server /etc/init.d/mysql
$ sudo systemctl enable mysql

Move the local binary files to your local binary directory and create a new symlink.

$ sudo mv bin/* /usr/local/bin/
$ sudo rm -rf bin/
$ sudo ln -s /usr/local/bin /usr/local/mysql/

Change the mysql directory to root owner in the mysqlgroup and data directory to mysql owner in mysql group.

$ sudo chown -R root:mysql .
$ sudo chown -R mysql data

Now create a new configuration file for your data node to define the cluster and management node to which it belongs to.

$ sudo mkdir -p /var/lib/mysql-cluster
$ sudo chown -R mysql /var/lib/mysql-cluster

Start the data node

$ ndbd --initial 2018-11-01 00:51:08 [ndbd] INFO     -- Angel connected to '192.168.56.20:1186'
2018-11-01 00:51:23 [ndbd] INFO     -- Angel allocated nodeid: 2$ sudo systemctl start mysql

You can now create a password for your mysql usage

$ sudo mysql_secure_installation

Configure the SQL node

You need to first install the mysql cluster package and move the folder to your /usr/local directory.

$ wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz
$ mkdir mysql
$ tar -xzvf mysql-cluster-gpl-7.4.12-linux-glibc2.5-x86_64.tar.gz -C mysql --strip-components=1
$ mv mysql /usr/local/

Run the install database script file.

$ cd /usr/local/mysql
$ sudo ./scripts/mysql_install_db --user=mysql

Copy the mysql service file and enable it on boot

$ cp support-files/mysql.server /etc/init.d/mysql
$ sudo systemctl enable msql

Move the local binary files to your local binary directory and create a new symlink.

$ sudo mv bin/* /usr/local/bin/
$ sudo rm -rf bin/
$ sudo ln -s /usr/local/bin /usr/local/mysql/

Finally, create a my.cnf file and paste in the following code while replacing your management nodes IP address.

$ vim /etc/my.cnf

# MySQL Config
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql

# Run ndb storage engine
ndbcluster
# IP address management node
ndb-connectstring=192.168.56.20
[mysql_cluster]
# IP address management node
ndb-connectstring=192.168.56.20
# MySQL Pid and Log
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Save changes, exit then start the mysql service and configure your password.

$ sudo systemctl start mysql
$ sudo mysql_secure_installation
$ mysql -u root -p

To verify that your installation works, you will create a sample database in your SQL node and check whether it will be replicated in the data node.

In the same SQL VPS, create a new database (name it whatever your want)

mysql> CREATEDATABASE vegetables;
Query OK, 1 row affected (0.02 sec)

Log into the data node and check on the databases, the created database in the SQL node should have been replicated.

$ mysql -u root -p mysql> SHOWDATABASES;

Conclusion

You have successfully set up your MySQL cluster replication for high availability. You can shut down your cluster status from the management node from the ndb_mgm console by using the shutdown command.

Finally, to monitor the same from the ndb_mgm console, you can use the show command. The MySQL cluster replication has proved to provide fault tolerance, automated failover, and elasticity.