Docker deploy MySQL cluster

#Introduction

In this tutorial, How to use Docker deploy MySQL cluster and connect to the nodes from your local machine. We will be deploying MySQL server with docker.

• 1 Management node
• 2 Data nodes
• 2 SQL nodes

The nodes in the cluster are running on separate hosts in a network.

First, You have installed docker in your machine.

Docker deploy MySQL cluster

Step 1: Create the docker network.

I will create a network for MySQL cluster with the following docker command.

docker network create cluster --subnet=192.168.4.0/24

Step 2: Get the mysql docker repository

git clone https://github.com/mysql/mysql-docker.git
cd mysql-docker
git checkout -b mysql-cluster

I will change the IP address of each node to match the subnet. Open mysql-cluster/8.0/cnf/mysql-cluster.cnf file

For example

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M


[ndb_mgmd]
NodeId=1
hostname=192.168.4.2
datadir=/var/lib/mysql

[ndbd]
NodeId=2
hostname=192.168.4.3
datadir=/var/lib/mysql

[ndbd]
NodeId=3
hostname=192.168.4.4
datadir=/var/lib/mysql

[mysqld]
NodeId=4
hostname=192.168.4.10

[mysqld]
NodeId=5
hostname=192.168.4.11

Open mysql-cluster/8.0/cnf/my.cnf and the modify as below

[mysqld]
ndbcluster
ndb-connectstring=192.168.4.2
user=mysql

[mysql_cluster]
ndb-connectstring=192.168.4.2

Docker image build

docker build -t <image_name> <Path to docker file>
docker build -t mysql-cluster mysql-cluster/8.0

Step 3: Create the manager node.

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

Step 4: Create the data nodes

docker run -d --net=cluster --name=ndb1 --ip=192.168.4.3 mysql-cluster ndbd
docker run -d --net=cluster --name=ndb2 --ip=192.168.4.4 mysql-cluster ndbd

Step 5: Create the sql nodes.

docker run -d --net=cluster --name=mysql1 --ip=192.168.4.10 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld
docker run -d --net=cluster --name=mysql2 --ip=192.168.4.11 -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql-cluster mysqld
docker run -it --net=cluster mysql-cluster ndb_mgm

The cluster management console will be loaded.

[Entrypoint] MySQL Docker Image 8.0.28-1.2.7-cluster
[Entrypoint] Starting ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm>

Run show command

ndb_mgm> show
Connected to Management Server at: 192.168.4.2:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.4.3  (mysql-8.0.28 ndb-8.0.28, Nodegroup: 0, *)
id=3    @192.168.4.4  (mysql-8.0.28 ndb-8.0.28, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.4.2  (mysql-8.0.28 ndb-8.0.28)

[mysqld(API)]   2 node(s)
id=4    @192.168.4.10  (mysql-8.0.28 ndb-8.0.28)
id=5    @192.168.4.11  (mysql-8.0.28 ndb-8.0.28)

ndb_mgm>

Step 7. Change the default passwords.

mysql node 1:

The SQL nodes are created initially, a random password. Get the default password.

docker logs mysql1 2>&1 | grep PASSWORD

To change the password, first, Input password default at Step 7

docker exec -it mysql1 mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

mysql node 2:

The SQL nodes are created initially, a random password. Get the default password.

docker logs mysql2 2>&1 | grep PASSWORD

To change the password, first, Input password default at Step 7

docker exec -it mysql2 mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

Step 8: Login and create a new database.

For example, I will create huupv account on mysql1 and mysql2 containers and access any hosts.

# For mysql1
docker exec -it mysql1 mysql -uroot -p
CREATE USER 'huupv'@'%' IDENTIFIED BY '123456789';
GRANT ALL ON *.* TO 'huupv'@'%';
FLUSH PRIVILEGES;

# For mysql2
docker exec -it mysql2 mysql -uroot -p
CREATE USER 'huupv'@'%' IDENTIFIED BY '123456789';
GRANT ALL ON *.* TO 'huupv'@'%';
FLUSH PRIVILEGES;

Create new a database.

create schema test_db;

The output terminal is as below:

vagrant@devopsroles:~/mysql-docker$ docker exec -it mysql1 mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.28-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'huupv'@'%' IDENTIFIED BY '123456789';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO 'huupv'@'%';
Query OK, 0 rows affected (0.11 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
vagrant@devopsroles:~/mysql-docker$ docker exec -it mysql2 mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28-cluster MySQL Cluster Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'huupv'@'%' IDENTIFIED BY '123456789';
ERROR 1396 (HY000): Operation CREATE USER failed for 'huupv'@'%'
mysql> GRANT ALL ON *.* TO 'huupv'@'%';
Query OK, 0 rows affected (0.10 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
vagrant@devopsroles:~/mysql-docker$

Login from my machine.

 mysql -h192.168.4.10 -uhuupv -p
 mysql -h192.168.4.11 -uhuupv -p

Via My Youtube

Conclusion

You have to deploy a MySQL database server with docker. I hope will this your helpful. Thank you for reading the DevopsRoles page!