How to Create a MySQL Docker Container for Testing

Docker is a container runtime environment that allows programs to operate in a jailed environment without any required external dependencies. Containers are similar in some concepts to virtual machines, however, they do not use a hypervisor and run in a single kernel instance, often sharing the instance with other containers. One of the prime features of containerization is the lack of external dependencies; the container has all of the runtime libraries and components needed to run the application.

Oracle has been providing Docker container images since MySQL 5.5.40 and thus makes Docker an excellent choice for performing testing across multiple MySQL versions and releases. Often times it is desirable to perform regression testing of a bug to determine what release(s) the bug affects or to quantify how the behavior changed between releases.

Docker enables fairly lightweight launching of MySQL instances with an automatic cleanup feature once testing is complete. In this post, I will demonstrate some of the ways you can use Docker to launch MySQL and use it for testing.

Before you can use Docker, it must be installed on your test machine. If you do not have access to a Linux system directly, I recommend you obtain VirtualBox and create a Linux virtual machine for running Docker images, some good choices for the OS would be CentOS 7 or Fedora (latest). Fedora can be considered the upstream to the enterprise Linux variants and thus will be guaranteed to have the latest support for technologies.

Installation

1. On systems that have YUM or DNF and have access to the operating system repositories, you can install docker with this command:

# yum install docker docker-common

After executing that command you should be prompted by YUM to install docker and any necessary dependencies.

2. To use Docker, you next need to start the service:

# service docker start

On CentOS/RHEL 7 distributions, use systemctl:

# systemct start docker

3. If you want Docker available after every reboot, enable it with this command:

# chkconfig docker on

On CentOS/RHEL 7 distributions, use systemctl:

# systemct enable docker

How Docker works

Docker by itself is just a container manager, to effectively utilize Docker you need to have access to docker.io, the site where Docker container images are published and can be downloaded from. The Docker containers at docker.io contain an image of the MySQL binaries and dependency information so that the Docker service can download the appropriate runtime files and virtual filesystem for the Docker container to run in.

When you create a Docker container instance it allocates a virtual filesystem for the container that is persistent across runs (unless you use the –rm switch). You can start and stop Docker containers at will and they will remain persistent over reboots. One of the features of Docker that makes it especially handy for test instances is the ability for the container to be erased once stopped. If you start a Docker container with the –rm option, when the container is shutdown it is also deleted from the filesystem and helps eliminate cruft.

Creating and launching containers

Here is the simplest command for launching the latest MySQL version in a container and removing it after shutdown:

# docker run --rm --name=mysql8 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:latest

Here’s the decode of that command:

• docker – docker is the executable name used to perform all docker functions.
• run – this is a command given to docker, it instructs docker to create a new container and run it.
• –rm – this instructs docker to remove the container files after it is stopped.
• –name=mysql8 – this tells docker to name the container ‘mysql8’ for all future command references. This name is arbitrary but should be descriptive to avoid clashing with multiple container instances.
• -e MYSQL_ALLOW_EMPTY_PASSWORD – this passes along an option to the container during the creation phase, telling the container to assign an empty root password during the creation process.
• -d mysql/mysql-server:latest – this tells docker the repository and version to download from docker.io when creating the container. The word ‘latest’ is a magic version that tells docker to download the newest version of all MySQL releases, you can also specify exact version numbers.

Some of the useful alterations of the above command might be to specify a MySQL version or give it a unique name. Here is an example where MySQL 8.0.13 is specified and a similarly unique name is used:

# docker run --rm --name=mysql8013 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:8.0.13

You can even pull up MySQL 5.5 instances this way too:

docker run --rm --name=mysql55 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:5.5

Using the Docker instance

So far we’ve only discussed launching the Docker instance, but without client program access it’s not very useful. Making connections using the standard MySQL client programs is fairly straightforward because they use local socket connections and connect via localhost. If you want to connect using a network IP, such as with MySQL Workbench, it’s a little more complicated.

Here’s an example of a local mysql CLI connection:

# docker exec -it mysql8 mysql -uroot

Let’s break down that command:

• exec – tells docker to execute a command inside a docker container instance.
• -it – tells docker to create an interactive session with a pseudo-tty.
• mysql8 – tells docker the command will be executed in the container instance named ‘mysql8’.
• mysql -uroot – this is the actual raw shell command line that docker executes inside the container instance.

The MySQL Docker containers have the MySQL command-line utilities included, such as mysql, mysqlpump, mysqldump, etc. If you want to pipe data into one of the command line tools, such as loading a dump, you will need to remove the -t from the command line, like this:

# docker exec -i mysql8 mysql -uroot < dumpfile.sql

TCP/IP access to Docker containers

Using the example commands above, Docker will assign non-routable private IP addresses to containers and create a virtual network interface on your system. The virtual interface and container form the endpoints for making TCP/IP connections to the service running in the container.

To find out the IP address of your virtual network interface on your Linux machine, run this command:

shell> ip addr show docker0

8: docker0:  mtu 1500 qdisc noqueue state UP group default
link/ether 02:42:f7:98:c4:d1 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:f7ff:fe98:c4d1/64 scope link
valid_lft forever preferred_lft forever

In the above example you can see that the IP address assigned to my virtual Docker interface is 172.17.0.1, this is the endpoint your client application will connect from; this is the IP address you will use to create a user in MySQL.

To determine the IP address assigned to your container instance, run this command, substituting your instance name for 'mysql' in the example:

shell> docker inspect mysql8 | grep IPAddress

"SecondaryIPAddresses": null,
"IPAddress": "172.17.0.2",
"IPAddress": "172.17.0.2",

In the above output the IP address of the container instance is 172.17.0.2, so that makes the endpoints 172.17.0.1 <-> 172.17.0.2. Next we need to create a user for the client program to connect as, otherwise you will get a denial because 172.17.0.1 is not an allowed host.

shell> docker exec -it mysql8 mysql -uroot

mysql> create user root@172.17.0.1 identified by '';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@172.17.0.1 with grant option;
Query OK, 0 rows affected (0.01 sec)

Once you've created the network user, you can connect to your container instance as root with no password. The 172.17.0.0/16 network is a non-routable private network that cannot be accessed from outside of your machine, so there is no danger of someone accessing your docker container from the outside.

Shutdown and cleanup

After you've completed testing, you'll need to shutdown the Docker instance and cleanup the files if you didn't use --rm when starting it.

To shutdown a Docker instance:

# docker stop mysql8

To remove the instance:

# docker rm mysql8

Conclusion

Hopefully I've illustrated the usefulness of Docker containers for testing and sandbox usage. Docker has the potential to change your workflow and make regression testing fast, easy, and convenient. The Docker hub contains images for release 5.5.40 and later.