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
Docker deploy MySQL cluster

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
Docker deploy MySQL cluster 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!

,

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published.

Comment moderation is enabled. Your comment may take some time to appear.

This site uses Akismet to reduce spam. Learn how your comment data is processed.