MySQL in Docker (for Testing)
These notes are stitched together from various sources - I was surprised I was not able to find all the relevant info in one place.
The objective is to launch a Docker container running a MySQL database server, and to enable access to the database from the host OS - for example, using JDBC, ODBC and so on.
The database will be used to support integration testing of an application. In this way, it becomes easy to ensure that any development environment has an up-to-date schema running on the same database version as production (albeit a slimmed-down version in terms of data volumes). No worrying about incompatible versions of SQL between dev and production, either.
The database is initialized on start-up by placing a SQL script file in the container’s
entrypoint directory. The script ensures the creation of a user with sufficient privileges to execute scripts at the command line from the host OS. It also creates an empty schema.
This, in turn, allows for the database to be configured with a set of database objects - tables, views, etc. - and for the tables to be populated with a set of test data. Population of the schema can happen after the container has been launched and the database server has started. (Alternatively, you can always include a database dump as one of the files placed in the container’s
entrypoint directory, and then have that file loaded automatically when the database starts.)
I am using MySQL 8, running on Docker 19.03.
My dockerfile is very simple:
FROM mysql:8.0.19 COPY mysql_*.sql /docker-entrypoint-initdb.d/
The mysql image is built using this parent dockerfile.
The entrypoint directory shown in the dockerfile is where I place a MySQL SQL script containing the following:
MySQL will execute all SQL files it finds in this directory, upon start-up. It will look not only for
.sql files, but also for
.sh script files. All such files will be executed in alphabetical order.
I created a series of scripts using the Docker command line - for example:
To create an image (a one-time task):
docker build --tag "imdb_test:Dockerfile" .
To create a container, but not start it (another one-time task):
docker create \ --publish 3376:3306 \ --name imdb_test \ --env MYSQL_ROOT_PASSWORD=your_root_pw_here \ imdb_test:Dockerfile
Note the use of a root password in the script. This is to ensure a known password is assigned when the server is first created. Otherwise, you will have to manually retrieve the auto-generated password from the server’s MySQL logs (more details here):
docker logs mysql1 2>&1 | grep GENERATED
However, for convenience, I created a basic Python script, using the Python Docker API, to:
- start the container
- wait for MySQL to start
- run a script
Currently, native API libraries are available for Python and Go. However, given all the Docker commands are resolved into REST calls, you can use any language that has access to a decent HTTP library. So, for example, Kong-Unirest for Java comes to mind.
To enable HTTP access in my Windows host environment, I use the following setting (Docker Desktop > Settings > General):
There is a security warning associated with doing this, as shown above - so, use with caution.
A bare-bones (not production-ready!) example of using the API with Java is provided below.
It uses the following Maven dependency:
The code lists all images, and starts one container:
The “start container” command returns one of the following codes:
204 – no error
304 – container already started
404 – no such container
500 – server error