Configure MySQL Cluster to Increase throughput and higher availability

Prerequisites:-

To create a mysql cluster it requies a minimum of 3 systems from which one is considered as the main system i.e master and remaining ae used as the storage units

1. Downloading and Installing MySQL Cluster:-

a. Download the free, Generally Available (GA) MySQL cluster release from the official MySQL cluster download page. From this page, choose the Debian Linux platform package, which is also suitable for Ubuntu. Also make sure to select the 32-bit or the 64-bit version depending on the architecture of your Nodes. Upload the installation package to each of your Nodes.

b. Before you start the installation, the libaio1 package must be installed since it is a dependency:

sudo apt-get install libaio1

c. After that, install the downloaded MySQL cluster package:

sudo dpkg -i mysql-cluster-gpl-7.4.11-debian7-x86_64.deb

d. Now you can find the MySQL cluster installation in the directory /opt/mysql/server-5.6/. We’ll be working especially with the bin directory (/opt/mysql/server-5.6/bin/) where all the binaries are.

e. The same installation steps should be performed on all Nodes regardless of the fact that each will have different function — manager or data node.

f. Next, we will configure the MySQL cluster manager on each Node.

Configuring the Master nodes :-

In this step we’ll configure the MySQL cluster manager (manager.mysql.cluster). Its proper configuration will ensure correct synchronization and load distribution among the data nodes. All commands should be executed on manager.mysql.cluster.

a. The cluster manager is the first component which has to be started in any cluster. It needs a configuration file which is passed as an argument to its binary file. For convenience, we’ll use the file /var/lib/mysql-cluster/config.ini for its configuration.

b. On the manager.mysql.cluster , first create the directory where this file will reside (/var/lib/mysql-cluster):

sudo mkdir /var/lib/mysql-cluster

c. Then create a file and start editing it :

vim /var/lib/mysql-cluster/config.ini

d. This file should contain the following code:

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

[ndbd]
hostname=node1.mysql.cluster # Hostname of the first data node
datadir=/usr/local/mysql/data # Remote directory for the data files

[ndbd]
hostname=node2.mysql.cluster # Hostname of the second data node
datadir=/usr/local/mysql/data # Remote directory for the data files

[mysqld]
# SQL node options:
hostname=manager.mysql.cluster # In our case the MYSQL server/client is on same node as cluster manager

e. For each of the above components we have defined a hostname parameter. This is an important security measure because only the specified hostname will be allowed to connect to the manager and participate in the cluster as per their designated role.

f. In the above file you can add more redundant components such as data nodes (ndbd) or MySQL servers (mysqld) by just defining additional instances in the exactly the same manner.

g. Now you can start the manager for the first time by executing the ndb_mgmd binary and specifying the config file with the -f argument like this:

sudo /opt/mysql/server-5.6/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini

h. You should see a message about successful start similar to this:

Output of ndb_mgmd
MySQL Cluster Management Server mysql-5.6.29 ndb-7.4.11

i. You would probably like to have the management service started automatically with the server. The GA cluster release doesn’t come with a suitable startup script, but there are a few available online. For the beginning you can just add the start command to the /etc/rc.local file and the service will be automatically started during boot. First, though, you will have to make sure that /etc/rc.local is executed during the server startup. In Ubuntu 16.04 this requires running an additional command:

sudo systemctl enable rc-local.service

Then open the file /etc/rc.local for editing:

sudo nano /etc/rc.local

j. There add the start command before the exit line like this:

/etc/rc.local

...
/opt/mysql/server-5.6/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
exit 0

k. Save and exit the file.

The cluster manager does not have to run all the time. It can be started, stopped, and restarted without downtime for the cluster. It is required only during the initial startup of the cluster nodes and the MySQL server/client.

2. Configuring and Starting the Data Nodes:-

Next we’ll configure the data nodes (node1.mysql.cluster and node2.mysql.cluster) to store the data files and support properly the NDB engine. All commands should be executed on both nodes. You can start first with node1.mysql.cluster and then repeat exactly the same steps on node2.mysql.cluster.

a. The data nodes read the configuration from the standard MySQL configuration file /etc/my.cnf and more specifically the part after the line [mysql_cluster]. Create this file with vim and start editing it:

sudo vim /etc/my.cnf

b. Specify the hostname of the manager like this:

/etc/my.cnf

[mysql_cluster]
ndb-connectstring=manager.mysql.cluster

c. Save and exit the file.

d. Specifying the location of the manager is the only configuration needed for the node engine to start. The rest of the configuration will be taken from manager directly. In our example the data node will find out that its data directory is /usr/local/mysql/data as per the manager’s configuration. This directory has to be created on the node. You can do it with the command:

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

e. After that you can start the data node for the first time with the command:

sudo /opt/mysql/server-5.6/bin/ndbd

f. After a successful start you should see a similar output:

Output of ndbd

2016-05-11 16:12:23 [ndbd] INFO -- Angel connected to 
'manager.mysql.cluster:1186'

2016-05-11 16:12:23 [ndbd] INFO -- Angel allocated node id: 2

g. You should have the ndbd service started automatically with the server. The GA cluster release doesn’t come with a suitable startup script for this either. Just as we did for the cluster manager, let’s add the startup command to the /etc/rc.local file. Again, you will have to make sure that /etc/rc.local is executed during the server startup with the command:

sudo systemctl enable rc-local.service

h. Then open the file /etc/rc.local for editing:

sudo nano /etc/rc.local

i. Add the start command before the exit line like this:

/etc/rc.local

...
/opt/mysql/server-5.6/bin/ndbd
exit 0

j. Save and exit the file.

k. Once you are finished with the first node, repeat exactly the same steps on the other node , which is node2.mysql.cluster in our example.

3. Configuring and Starting the MySQL Server and Client:-

a. A standard MySQL server, such as the one that is available in Ubuntu’s default apt repository, does not support the MySQL cluster engine NDB. That’s why you need a custom MySQL server installation. The cluster package which we already installed on the three Nodes comes with a MySQL server and a client too. As already mentioned, we’ll use the MySQL server and client on the management node (manager.mysql.cluster).

b. The configuration is stored again the default /etc/my.cnf file. On manager.mysql.cluster, open the configuration file:

sudo vim /etc/my.cnf

Then add the following to it:

/etc/my.cnf

[mysqld]
ndbcluster # run NDB storage engine
...

c. Save and exit the file.

d. As per the best practices, the MySQL server should run under its own user (mysql) which belongs to its own group (again mysql). So let’s create first the group:

sudo groupadd mysql

e. Then create the mysql user belonging to this group and make sure it cannot use shell by setting its shell path to /bin/false like this:

sudo useradd -r -g mysql -s /bin/false mysql

f. The last requirement for the custom MySQL server installation is to create the default database. You can do it with the command:

sudo /opt/mysql/server-5.6/scripts/mysql_install_db --user=mysql

g. For starting the MySQL server we’ll use the startup script from /opt/mysql/server-5.6/support-files/mysql.server. Copy it to the default init scripts directory under the name mysqld like this:

sudo cp /opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysqld

h. Enable the startup script and add it to the default runlevels with the command:

sudo systemctl enable mysqld.service

i. Now we can start the MySQL server for the first time manually with the command:

sudo systemctl start mysqld

j. As a MySQL client we’ll use again the custom binary which comes with the cluster installation. It has the following path: /opt/mysql/server-5.6/bin/mysql. For convenience let’s create a symbolic link to it in the default /usr/bin path:

sudo ln -s /opt/mysql/server-5.6/bin/mysql /usr/bin/

k. Now you can start the client from the command line by simply typing mysql like this:

mysql

l. You should see an output similar to:

Output of ndb_mgmd
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-ndb-7.4.11-cluster-gpl MySQL Cluster Community Server (GPL)

The above is the first check to show that the MySQL cluster, server, and client are working.