MySQL Clustering with Docker

3 min readMay 2, 2019

This is what will be created
1- Management Node
2- Data Nodes
2- SQL nodes.
The nodes in the cluster will run on separate hosts in a network. We’ll need to create a network in docker and connect the containers to the network.

Step 1: Creating a docker network.

To create the docker network, run the following docker command on the terminal.

docker network create cluster — subnet=192.168.0.0/16

You can define your own subnet for this.

Step 2: Editing the MySQL docker repository

When we need to use different subnet other than the default one or add extra nodes, we will do the following

We clone the MySQL docker repository. git clone https://github.com/mysql/mysql-docker.git
Checkout the MySQL-cluster branch
Open mysql-docker/7.6/cnf/mysql-cluster.cnf
By default, mysql-cluster.cnf is configured to use a single MySQL node and the IP addresses are configured. We can add the extra MySQL node and change the IP addresses of each node to match the subnet.
For example, here is how my configuration looks like…
[ndb_mgmd]
NodeId=1
hostname=192.168.0.2
datadir=/var/lib/mysql
[ndbd]
NodeId=2
hostname=192.168.0.3
datadir=/var/lib/mysql
[ndbd]
NodeId=3
hostname=192.168.0.4
datadir=/var/lib/mysql
[mysqld]
NodeId=4
hostname=192.168.0.10

we can add the the extra mysql node

[mysqld]
NodeId=5
hostname=192.168.0.9
Open mysql-docker/7.5/cnf/my.cnf and modify the ndb-connectstring to match the ndb_mgmd node.
[mysqld]
ndbcluster
ndb-connectstring=192.168.0.2
user=mysql
[mysql_cluster]
ndb-connectstring=192.168.0.2
Build the docker image.
docker build -t <image_name> <Path to docker file>
docker build -t mysql/mysql-cluster mysql-docker/7.6

We can now create all the nodes for our cluster.

Step 3: Creating the manager node.

We create the manager node with the name, management1 and ip:192.168.0.2.

docker run -d — net=cluster — name=management1 — ip=192.168.0.2 mysql/mysql-cluster ndb_mgmd

Step 4: Creating the data nodes

docker run -d — net=cluster — name=ndb1 — ip=192.168.0.3 mysql/mysql-cluster ndbd
docker run -d — net=cluster — name=ndb2 — ip=192.168.0.4 mysql/mysql-cluster ndbd

Step 5: Creating the Mysql nodes.

docker run -d — net=cluster — name=mysql1 — ip=192.168.0.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql/mysql-cluster mysqld
docker run -d — net=cluster — name=mysql2 — ip=192.168.0.9 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql/mysql-cluster mysqld

We can verify everything went well by running

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

The cluster management console will be loaded with the following details.

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

We can run show command to see the configuration output.

ndb_mgm> show
Connected to Management Server at: 192.168.0:1186
Cluster Configuration
 — — — — — — — — — — -
[ndbd(NDB)] 2 node(s)
id=2 @192.168.0.3 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0, *)
id=3 @192.168.0.4 (mysql-5.7.19 ndb-7.5.7, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.2 (mysql-5.7.19 ndb-7.5.7)[mysqld(API)] 2 node(s)
id=4 @192.168.0.10 (mysql-5.7.19 ndb-7.5.7)
id=5 @192.168.0.9 (mysql-5.7.19 ndb-7.5.7)

Step 7. Changing the default passwords.

We can use the following command to get the default password

docker logs mysql1 2>&1 | grep PASSWORD

To change the password, we run the following command and login to the MySQL node.

docker exec -it mysql1 mysql -uroot -p

Then insert our password which we can change through the following command

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘root’;

exit from the node1 and do the same for MySQL node 2 as well.

Step 8: Login and create a new database.

Let’s try to login to the MySQL node from our local machine. Just to check the cluster functionality, you can create a new database on the first node

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

You can then exit and login to the other node to check if the database is available on the second node and you’ll be able to see that it also exists in the second node