Configuring MySQL NDB Cluster by using Docker and pointing with WSO2 APIM

To achieve the high availability and scaling of MySQL they have introduced two types of MySQL clusters as MySQL InnoDB Cluster(using InnoDB as the engine) and MySQL NDB Cluster (using NDB engine). Currently, WSO2 products are supporting for this NDB Cluster, and today I am going to explain how to set up an NDB Cluster by using a Docker image of NDB Cluster.

Let’s start to configure the NDB cluster on your local machine by using Docker. Please note you need to install[1][2] docker to your machine before starting the below steps.

An NDB Cluster contains one Management node, two Data nodes and two SQL nodes.

Structure of an NDB Cluster [3]

The nodes in the cluster are running on separate hosts in a network. So we want to create a network in docker and connect the containers to the network. Let’s begin from this step.

1. As the first step, you need to create the docker network by running the below command on a terminal.

docker network create cluster — subnet=10.100.0.0/16

2. Now you need to clone the MySQL cluster from the git repository[4] and check out the “mysql-cluster” branch.

Then you need to configure cnf files under the required version. Here I am using NDB 7.5.x version.

NDB 7.5.x has different minor versions if you want to set up such a minor version, you need to change the argument value of “MYSQL_CLUSTER_PACKAGE” which resides in here /mysql-docker/7.5/Dockerfile.

eg: NDB 7.5.7 (default value is 7.5.12)

ARG MYSQL_CLUSTER_PACKAGE=mysql-cluster-community-server-minimal-7.5.7

Let’s see how to configure the conf file of the docker.

2.1 Open the /mysql-docker/7.5/cnf/mysql-cluster.cnf file and configure as follows.

By default, mysql-cluster.cnf configured to use a single MySQL node. Also, the IP addresses set. Change the IP addresses of each node to match the subnet.

[ndb_mgmd]
NodeId=1
hostname=10.100.0.2
datadir=/var/lib/mysql
[ndbd]
NodeId=2
hostname=10.100.0.3
datadir=/var/lib/mysql
[ndbd]
NodeId=3
hostname=10.100.0.4
datadir=/var/lib/mysql
[mysqld]
NodeId=4
hostname=10.100.0.10
[mysqld]
NodeId=5
hostname=10.100.0.11

The above configuration is not enough when configuring the NDB cluster with WSO2 product. Other than the above details, we need to set the below attributes under the NDB data nodes to create the tables successfully which required for WSO2 products.

MaxNoOfAttributes=8192
MaxNoOfOrderedIndexes=512
MaxNoOfTables=256

eg: Modified mysql-cluster.cnf file,

[ndb_mgmd]
NodeId=1
hostname=10.100.0.2
datadir=/var/lib/mysql
[ndbd]
NodeId=2
hostname=10.100.0.3
datadir=/var/lib/mysql
MaxNoOfAttributes=8192
MaxNoOfOrderedIndexes=512
MaxNoOfTables=256
[ndbd]
NodeId=3
hostname=10.100.0.4
datadir=/var/lib/mysql
MaxNoOfAttributes=8192
MaxNoOfOrderedIndexes=512
MaxNoOfTables=256
[mysqld]
NodeId=4
hostname=10.100.0.10
[mysqld]
NodeId=5
hostname=10.100.0.11

2.2 Open mysql-docker/7.5/cnf/my.cnf and modify the ndb-connectstring to match the ndb_mgmd node as the below.

[mysqld]
ndbcluster
ndb-connectstring=10.100.0.2
user=mysql
[mysql_cluster]
ndb-connectstring=10.100.0.2

2.3 Build the docker image by using the below command with the above changes.

docker build -t <image_name> <Path to docker file>
eg:
docker build -t mysql-cluster mysql-docker/7.5

Once you complete the above steps, you can move the next level. As the third step, you have to create the necessary nodes for the cluster. Let’s begin with the manager node.

3. Create the manager node.

We want to create a manager node with the name, management1 and IP: 10.100.0.2.

docker run -d --net=cluster --name=management1 --ip=10.100.0.2 mysql-cluster ndb_mgmd

4. Create the data nodes

docker run -d --net=cluster --name=ndb1 --ip=10.100.0.3 mysql-cluster ndbd
docker run -d --net=cluster --name=ndb2 --ip=10.100.0.4 mysql-cluster ndbd

5. Create the SQL nodes.

docker run -d — net=cluster — name=mysql1 — ip=10.100.0.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld
docker run -d — net=cluster — name=mysql2 — ip=10.100.0.11 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld

Here if you want to allocate much memory, you can point a separate file location in the machine instead of using the docker memory also. (This is an optional step)

e.g.:

docker run -d --net=cluster --name=mysql1 -v <local_machine_path>:/var/lib/mysql --ip=10.100.0.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld
docker run -d — net=cluster — name=mysql1 -v /home/nadee/Documents/mysql1:/var/lib/mysql — ip=10.100.0.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld

Now you have created each node of the cluster and to verify whether is everything worked correctly, please run the below command,

docker run -it --net=cluster mysql-cluster ndb_mgm

Then the cluster management console will be loaded as follows.

[Entrypoint] MySQL Docker Image 7.5.7-1.1.0
[Entrypoint] Starting ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm>

Now you need to run the “show” command, and you will see the following output.

ndb_mgm> show
Connected to Management Server at: 10.100.0.2:1186
Cluster Configuration
 — — — — — — — — — — -
[ndbd(NDB)] 2 node(s)
id=2 @10.100.0.3 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0, *)
id=3 @10.100.0.4 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.100.0.2 (mysql-5.7.19 ndb-7.5.7)
[mysqld(API)] 2 node(s)
id=4 @10.100.0.10 (mysql-5.7.19 ndb-7.5.7)
id=5 @10.100.0.11 (mysql-5.7.19 ndb-7.5.7)

If they are working fine, it will display as above. Otherwise, it will show errors like can’t to connect. Then you need to check the above steps carefully and set up correctly.

6. As the next step, let’s configure our MySQL nodes so that we can log in to them remotely and create the databases.

6.1. Change the default passwords.
- When the SQL nodes created initially, a random password set. To get the default password,

e.g., mysql1 node,

docker logs mysql1 2>&1 | grep PASSWORD

e.g., mysql2 node

docker logs mysql2 2>&1 | grep PASSWORD

6.2 Then you can change the password, run the following command list and login to the MySQL node.

e.g., mysql1 node

docker exec -it mysql1 mysql -uroot -p

Now copy and paste the password from the previous command and press enter.
Now you will be logged in to the MySQL node1. Change the password of the root user.

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘root’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

e.g., mysql2 node

docker exec -it mysql2 mysql -uroot -p

Now copy and paste the password from the previous command and press enter.
Now you will be logged in to the MySQL node2. Change the password of the root user.

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘root’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

6.3 Now you can log in and create databases in the MySQL nodes.

Let’s try to login to the MySQL node from the local machine.
Run the following command with particular IP, user and password.

e.g., mysql1 node,

mysql -h10.100.0.10 -uroot -p
.
.
.
mysql>

e.g., mysql2 node

mysql -h10.100.0.11 -uroot -p
.
.
.
mysql>

To check whether this is working fine, first create a database form one node, then exit and log to the other SQL node. Then execute the show databases command and you will able to see the databases which you created from the other MySQL node.

e.g., test_db

CREATE SCHEMA TEST_DB;mysql> create schema test_db;
Query OK, 1 row affected (0.04 sec)
Now login to the other mysql node and run
SHOW DATABASES;
mysql> show databases;
+ — — — — — — — — — — +
| Database            |
+ — — — — — — — — — — +
| information_schema  |
| mysql               | 
| ndbinfo             |
| performance_schema  |
| sys                 |
| test_db             |
+ — — — — — — — — — — +
8 rows in set (0.00 sec)

7. Configure the database with the WSO2 product.

In WSO2 products, we need to configure the database in the “master-datasources.xml”(<PRODUCT_HOME>/repository/conf/datasources) as a data source. Here I am going to point the above test_db as the api management database. Please see the below sample data source.

<datasource>
 <name>WSO2AM_DB</name>
 <description>The datasource used for API Manager database</description>
 <jndiConfig>
 <name>jdbc/WSO2AM_DB</name>
 </jndiConfig>
 <definition type=”RDBMS”>
 <configuration>
 <url>jdbc:mysql://10.100.0.10:3306/test_db?autoReconnect=true&amp;relaxAutoCommit=true&amp;useSSL=false&amp;failOverReadOnly=false</url>
 <username>root</username>
 <password>root</password>
 <defaultAutoCommit>true</defaultAutoCommit>
 <driverClassName>com.mysql.jdbc.Driver</driverClassName>
 <maxActive>50</maxActive>
 <maxWait>60000</maxWait>
 <testOnBorrow>true</testOnBorrow>
 <validationQuery>SELECT 1</validationQuery>
 <validationInterval>30000</validationInterval>
 </configuration>
 </definition>
 </datasource>

Here you need to pass the “autoReconnect=true”, “relaxAutoCommit=true”, “useSSL=false” and “failOverReadOnly=false” as parameters with the connection URL to establish the connection successfully between the product and the MySQL servers. You need to copy the MySQL JDBC driver to <PRODUCT_HOME>/repository/component/lib directory to work this correctly.

You can refer to this[5], to aware more about this NDB Cluster.

I hope this will be a useful blog for you. Appreciate your claps and will meet with another blog soon :)

References

[1]. https://docs.docker.com/v17.09/engine/installation/linux/docker-ce/ubuntu/#install-docker-ce

[2]. https://www.digitalocean.com/community/tutorials/how-to-install-and-use-docker-on-ubuntu-18-04

[3]. https://stackoverflow.com/questions/48162216/mysql-ndb-cluster-and-master-master-replication/48663890

[4]. https://github.com/mysql/mysql-docker

[5]. https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html

[6]. https://medium.com/@menakajayawardena/how-to-deploy-a-mysql-cluster-from-scratch-with-docker-a2452a56fc33