Docker-based MySQL Test Environment

Preparing a MySQL test environment using Docker images.

When preparing or changing a MySQL environment, it is essential to test many aspects of the build prior to rolling it out to a production system. For this, I use a Docker-based test environment which I spin up to perform the relevant testing.

In this example, I will prepare a MySQL instance, with its own persistent data directory which will survive a full Docker image re-build. This then becomes the foundation of future articles and examples.

Assuming you already have Docker installed on your machine (install docker if you have not yet done this), create the following folder structure as shown below.

$ tree -d test
test
└── data
    └── mysql

All the files we create for this test environment will go inside the test folder, and all the MySQL data files for the test instance will be stored within the data/mysql folder.

Create a file in the test folder called docker-compose.yml containing the following content. This file defines a Docker container based on the MySQL image (mysql:5.7). The container will mount the MySQL data folder located in the data folder into the default data file location for MySQL on the container when it is created. The default password for the root user is provided within the config file, which is then used by the MySQL setup scripts. Ports 33060 is mapped to 3306 to allow access from the host machine.

version: '2'
services:

  # --------------------------------------------------------------
  mysql:
    image: mysql:5.7
    container_name: mysql_test
    volumes:
      - ./data/mysql:/var/lib/mysql
    environment:
      - "MYSQL_ROOT_PASSWORD=secret"
    ports:
        - "3306:3306"

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 container in background ‘daemon’ mode.

$ docker-compose up -d
Creating network "single_test_default" with the default driver
Pulling mysql (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 ... done

This will automatically start the MySQL instance, which will, in turn, create all the necessary folders and files required in the data folder to allow the instance to run.

$ tree -d data
data
└── mysql
    ├── mysql
    ├── performance_schema
    └── sys

You can also confirm the running MySQL container with the following command, showing the base image used and what port is mapped to the container.

$ docker container ls
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
18fc7741b5ee        mysql:5.7           "docker-entrypoint.s…"   58 seconds ago      Up 57 seconds       33060/tcp, 0.0.0.0:33060->3306/tcp   mysql_test

You can see that the container 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 use MySQL Shell (mysqlsh) to connect to the MySQL instance using the address localhost:33060.

$ mysqlsh --sql -h localhost -P 33060 -u root -psecret
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
Creating a session to 'root@localhost:33060'
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:33060 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.

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-compose down
Stopping mysql_test ... done
Removing mysql_test ... done
Removing network single_test_default

To extend this to multiple instances to test replication, take a look at my article Docker-based Multiple MySQL. In this and future articles, I will be expanding this environment with additional replica hosts, management and monitoring tools.

Leave a Reply