Category: MySQL

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
    └── mysql3

All 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 … done

This 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
    └── sys

You 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-2

Testing

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_default

The 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.

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.