Deploying MySQL NDB cluster using Docker and Integrating with WSO2 IS.

Welcome to my new blog post on medium in awhile:) In this blog, i’m hoping to help you get started with setting up and using a MySQL NDB cluster with WSO2 IS.

What is MySQL NDB cluster?

When a database is created, one of the most overlooked factors is the storage engine that we use, which can lead to performance and data integrity issues. MySQL uses an architecture that allows for a range of pluggable storage engines, out of which InnoDB and NDB are the most notable ones.

NDB engine is used when your database is in a cluster environment, which will cater high-availability, distributed computing, high-redundancy and highest possible up-times.

In summary, MySQL NDB Cluster is the standard MySQL server integrated with an in-memory clustered NDB storage engine.

Refer this documentation to learn more about MySQL NDB cluster.

Architecture of a NDB cluster

A simple NDB cluster setup

There are three types of cluster nodes in a NDB cluster:

• Management node: The role of this type of node is to manage the other nodes within the NDB Cluster, performing functions such as providing configuration data and, starting and stopping nodes.
• Data node: These nodes store all the cluster data.
• SQL node: This type of nodes access the cluster data. In the case of NDB Cluster, an SQL node is a traditional MySQL server that uses the NDB storage engine.

Every MySQL Server has access to the data in all of the data nodes. If one MySQL server changes a piece of data then it is instantly visible to all of the other MySQL Servers. Having more than 1 data nodes and sql nodes is recommended to benefit from the NDB Cluster’s high-availability features.

Now, lets see how we can configure a MySQL NDB cluster environment using Docker, in our local machine.

Configuring NDB cluster with Docker.

Pre-requisites:

Before starting off, you need to install docker to your local machine. You can refer this documentation as the installation guide.

In this blog, I’m going to show you how to configure a NDB cluster with 1 management node, 2 data nodes and 2 SQL nodes.

Generally, the cluster nodes are running on separate hosts in a network. So first of all, we need to create a network in docker and connect the containers to the network. Let’s get started.

Step 1: Create the docker network by running the following command on a terminal.

docker network create cluster --subnet=10.100.0.0/16

Step 2: Get the MySQL docker repository.

• First, clone the MySQL docker repository from here.
• Checkout the ‘mysql-cluster’ branch.
• Configure the cnf files of your prefered version. Here I am using NDB 8.0 version. Open the mysql-cluster.cnf file in the mysql-docker/8.0/cnf path.

By default mysql-cluster.cnf is configured to use a single mysql node and the ip addresses are already configured. Hence you need to configure the IP-addresses of all 5 nodes to match your subnet, as shown below.

[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

Note that in the 2 data nodes of the above configuration, additional 3 parameters are used, which is required when configuring NDB cluster with WSO2 products.

MaxNoOfAttributes=8192
MaxNoOfOrderedIndexes=512
MaxNoOfTables=256

• Open mysql-docker/8.0/cnf/my.cnf and modify the ndb-connectstring to match the ndb_mgmd node.

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

• Build the docker image after completing the above configurations.

docker build -t <image_name> <Path_to_docker_file>
eg:
docker build -t mysql-cluster mysql-docker/8.0

As the next step, let’s create the nodes in the cluster.

Step 3: Create the manager node.

Create a manager node with the name ‘management1’ and IP-address ‘10.100.0.2’.

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

Step 4: Create the 2 data nodes, ndb1 and ndb2.

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

Step 5: Create the 2 SQL nodes, mysql1 and mysql2.

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/mysql-cluster mysqld

Now we are done with creating all the nodes in the NDB cluster. Run the below command to verify whether the cluster setup is working fine.

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

Then you will see the cluster management console as follows.

[Entrypoint] MySQL Docker Image 8.0.23-1.1.19-cluster
[Entrypoint] Starting ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm>

Run the ‘show’ command to see the following result.

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-8.0.23 ndb-8.0.23, Nodegroup: 0, *)
id=3 @10.100.0.4  (mysql-8.0.23 ndb-8.0.23, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.100.0.2  (mysql-8.0.23 ndb-8.0.23)
[mysqld(API)] 2 node(s)
id=4 @10.100.0.10  (mysql-8.0.23 ndb-8.0.23)
id=5 @10.100.0.11  (mysql-8.0.23 ndb-8.0.23)

If you get an output similar to the above, that means your NDB cluster setup is working fine :)

Moving on, let’s configure our 2 mysql nodes, in order to access them remotely and create databases.(Basically, mysql nodes are the access points to the whole cluster externally.)

Step 6: Change the default passwords of sql nodes.

• When we created the mysql nodes in step 5, default random passwords are set. You can retrieve those default passwords from the following command.

For mysql1 node:

docker logs mysql1 2>&1 | grep PASSWORD

For mysql2 node:

docker logs mysql2 2>&1 | grep PASSWORD

• Run the following command to change the password for the mysql1 node.

docker exec -it mysql1 mysql -uroot -p

Then paste the default password for this node you got from the previous step and press enter. You will then be logged into mysql1 node.

Enter the below commands to change the password of the root user.

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

• Repeat the same process to change the password of the mysql2 node too.

docker exec -it mysql2 mysql -uroot -p

Paste the previous password and proceed to execute the same SQL commands to change the password.

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

Step 7: Login to the mysql nodes and create databases.

Run the below command in a terminal with the relevant IP-address, username and give the password(‘root’ in our case) given in step 6, when asked for.

mysql -h10.100.0.10 -uroot -p

Exit from the sql window using the ‘exit’ command.

mysql> exit

Try logging in to the 2nd mysql node in the same manner.

mysql -h10.100.0.11 -uroot -p

As I mentioned in the beginning of this blog, sql nodes of the NDB cluster replicate the exact same data. Henceforth, once we create the databases in one sql node, that can be seen from the other sql nodes too.

Let’s create 2 databases ‘test_db’ and ‘test_db_shared’ in the mysql nodes. First you need to log in to mysql1 node. Then execute the following SQL queries.(You can use any character set and collation based on the requirement.)

mysql> CREATE DATABASE test_db CHARACTER SET latin1 COLLATE latin1_swedish_ci;
mysql> CREATE DATABASE test_db_shared CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Log in to the 2nd mysql node and run the below command where you’ll see the same 2 tables there as well.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| sys                |
| test_db            |
| test_db_shared     |
+--------------------+
7 rows in set (0.03 sec)

We’ve now come to the final part of this blog, which is integrating our NDB cluster with WSO2 IS product.

How to integrate WSO2 IS with the newly created database…

1. Download the latest WSO2 Identity Server from here. For details on running the Identity Server, see Running the Product.

2. Navigate to <PRODUCT_HOME>/repository/conf in your downloaded IS pack and open the deployment.toml file to configure the database. I’m going to use ‘test_db’ as my identity DB and ‘test_db_shared’ as the shared DB.

[user_store]
type = "database_unique_id"
[database.identity_db]
type = "mysql"
url = "jdbc:mysql://10.100.0.10:3306/test_db?autoReconnect=true&relaxAutoCommit=true&useSSL=false&failOverReadOnly=false"
username = "root"
password = "root"
[database.shared_db]
type = "mysql"
url = "jdbc:mysql://10.100.0.10:3306/test_db_shared?autoReconnect=true&relaxAutoCommit=true&useSSL=false&failOverReadOnly=false"
username = "root"
password = "root"

Make sure to pass the follwing parameters “autoReconnect=true”, “relaxAutoCommit=true”, “useSSL=false” and “failOverReadOnly=false” with the connection URL to establish the connection successfully between the product and the MySQL servers. Refer this documentation for more information on configuring a MySQL DB in WSO2IS.

3. Next, add the MySQL JDBC driver to <PRODUCT_HOME>/repository/component/lib directory.

4.Final step is to create the WSO2 identity DB and WSO2 shared DB. I used MySQL Workbench for this purpose.

Open a connection with the relevant hostname, port number, username and password. There you will see the created 2 databases.

5. Execute the database scripts.

Select the ‘test_db’ in MySQL workbench and execute the DB scripts in the following paths.

• <IS-HOME>/dbscripts/identity/mysql-cluster.sql
• <IS-HOME>/dbscripts/identity/uma/mysql-cluster.sql
• <IS-HOME>/dbscripts/consent/mysql-cluster.sql

Select the ‘test_db_shared’ and execute the DB script the following path.

• <IS-HOME>/dbscripts/mysql-cluster.sql

You have reached the end of this blog post. In summary, I have guided you on configuring a MySQL NDB cluster with 5 nodes using docker and finally integrating it with WSO2 IS.

Hope you find this blog post useful!