In a previous article, I showed how I build a Docker-based MySQL Test Environment when testing out various MySQL features. In this article, I will expand on this and prepare three separate MySQL instances, each with their own persistent data directories which will survive a full Docker image re-build, and connect them together in a regular MySQL replication topology of primary and two read-only replicas.
Folder structure
Assuming you already have Docker installed on your machine (go here if you have not yet done this), create the following folder structure as shown below.
$ tree -d test
test
└── data
├── mysql1
├── mysql2
└── mysql3All the files we create for this test environment will go inside the test folder, and all the MySQL data files for the test instances will be stored within the data/mysqlx folders.
Docker parameter file
Create a file in the test folder called docker-compose.yml containing the following content. This file defines three separate Docker containers based on the same starting MySQL image (mysql:5.7). Each of these containers will mount the data folders under the data folder into the default data file location for MySQL on each of the containers when they are created. They all use the same default password for the root user, and have sequential ports (33061 to 33063) mapped to allow access from the host machine.
version: '2'
services:
# --------------------------------------------------------------
mysql1:
image: mysql:5.7
container_name: mysql-test-1
volumes:
- ./data/mysql1:/var/lib/mysql
environment:
- "MYSQL_ROOT_PASSWORD=secret"
ports:
- "33061:3306"
# --------------------------------------------------------------
mysql2:
image: mysql:5.7
container_name: mysql-test-2
volumes:
- ./data/mysql2:/var/lib/mysql
environment:
- "MYSQL_ROOT_PASSWORD=secret"
ports:
- "33062:3306"
# --------------------------------------------------------------
mysql3:
image: mysql:5.7
container_name: mysql-test-3
volumes:
- ./data/mysql3:/var/lib/mysql
environment:
- "MYSQL_ROOT_PASSWORD=secret"
ports:
- "33063:3306"Build and run
From within the test folder, you can now fire up this environment by downloading the MySQL 5.7 Docker image (if required) and starting up the three containers in background ‘daemon’ mode.
$ docker-compose up -d
Creating network "test_default" with the default driver
Pulling mysql1 (mysql:5.7)…
5.7: Pulling from library/mysql
d599a449871e: Pull complete
f287049d3170: Pull complete
08947732a1b0: Pull complete
96f3056887f2: Pull complete
871f7f65f017: Pull complete
1dd50c4b99cb: Pull complete
5bcbdf508448: Pull complete
02a97db830bd: Pull complete
c09912a99bce: Pull complete
08a981fc6a89: Pull complete
818a84239152: Pull complete
Digest: sha256:5779c71a4730da36f013a23a437b5831198e68e634575f487d37a0639470e3a8
Status: Downloaded newer image for mysql:5.7
Creating mysql-test-1 … done
Creating mysql-test-3 … done
Creating mysql-test-2 … doneThis will automatically start three instances of MySQL, which will, in turn, create all the necessary folders and files required in the data folders to allow these instances to run.
$ tree -d data
data
├── mysql1
│ ├── mysql
│ ├── performance_schema
│ └── sys
├── mysql2
│ ├── mysql
│ ├── performance_schema
│ └── sys
└── mysql3
├── mysql
├── performance_schema
└── sysYou can also confirm the running MySQL containers with the following command, showing the base image used and what ports are mapped to each of the containers.
$ docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9966a7c53eca mysql:5.7 "docker-entrypoint.s…" 11 hours ago Up 11 hours 33060/tcp, 0.0.0.0:33061->3306/tcp mysql-test-1
ed2625f793ed mysql:5.7 "docker-entrypoint.s…" 11 hours ago Up 11 hours 33060/tcp, 0.0.0.0:33063->3306/tcp mysql-test-3
b6d37464375e mysql:5.7 "docker-entrypoint.s…" 11 hours ago Up 11 hours 33060/tcp, 0.0.0.0:33062->3306/tcp mysql-test-2Testing
You can see that each of the containers has the port 33060/tcp open, and an additional port has been opened on each container that is mapped to the regular MySQL port 3306. This allows us to connect to each of the MySQL instances using an address such as localhost:33061.
$ mysqlsh --sql -h localhost -P 33061 -u root -psecret
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
Creating a session to 'root@localhost:33061'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 7
Server version: 5.7.28 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.12
Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL localhost:33061 ssl SQL > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.0174 sec)I wouldn’t normally supply the password on the command line using -p{password} as it would be stored in the shell history and visible on the system process list, but as this is just a test environment, I’m happy to do so.
Stop and clean-up
When you are happy that everything is working as expected, stop the Docker container by running the following command. This helps to free up resources on your host machine – many times I’ve realised I’ve still got a number of Docker containers still running that I’d forgotten about 🙂
$ ./docker-stop.sh
Stopping mysql-test-1 ... done
Stopping mysql-test-3 ... done
Stopping mysql-test-2 ... done
Removing mysql-test-1 ... done
Removing mysql-test-3 ... done
Removing mysql-test-2 ... done
Removing network test_defaultThe next post in this series will add regular replication configuration between the hosts, and updates to the Docker compose file to supply tailored MySQL configuration files to each of the containers so that they will work as intended.