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 the MySQL server with docker.

To deploy a MySQL cluster using Docker, you can use the MySQL official Docker images and Docker Compose. Here’s a step-by-step guide:

• 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 on 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 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, with 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, with a random password. Get the default password.

docker logs mysql2 2>&1 | grep PASSWORD

To change the password, first, Input the 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 successfully deployed a MySQL cluster using Docker. You can now use the cluster for your applications or explore additional configuration options for MySQL clustering, such as replication and high availability. I hope will this your helpful. Thank you for reading the DevopsRoles page!