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:

SQL


1
2
3
4
5
6


-- create a database  
create database if not exists `imdb`;  
-- set up a new user with all privs for remote access:  
create user 'user-foo'@'%' identified with mysql_native_password by 'xxx';  
grant all privileges on *.* to 'user-foo'@'%' with grant option;  
flush privileges;  

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

Python


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77


#  
# pip install mysql.connector.python  
# and: https://dev.mysql.com/doc/connector-python/en/  
# and: https://dev.mysql.com/doc/connector-python/en/connector-python-reference.html  
#  
# docker API:  
# https://docker-py.readthedocs.io/en/stable/index.html  
#  

import docker  
import mysql.connector  
import time  
from datetime import datetime  

def get_container(container_name):  
    return client.containers.get(container_name)  

def get_status(container_name):  
    return get_container(container_name).status  

def log(msg):  
    print(datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3] + ' - ' + msg)  

client = docker.from_env()  
container_name = 'imdb_test'  

print('')  
log('starting container ' + container_name)  

get_container(container_name).start()  
while get_status(container_name) != 'running':  
    log('waiting for container')  
    time.sleep(1)  

log('container started')  
mysqld_is_running = False  

while not mysqld_is_running:  
    # get a JSON object with running process info:  
    result = get_container(container_name).top(ps_args='-o pid,comm')  
    # see if mysqld is in the list:  
    for proc in result["Processes"]:  
        # proc example: ['8949', 'mysqld']  
        if proc[1] == 'mysqld':  
            mysqld_is_running = True  
        else:  
            log('waiting for mysqld')  
        time.sleep(1)  
log('mysqld is running')  

connect_args = {  
    'user': 'user',   
    'password': 'pass',  
    'host': 'localhost',  
    'port': 3376,  
    'database': 'imdb'  
}  

cnx = mysql.connector.connect(**connect_args)  
cursor = cnx.cursor(prepared=True)  
log('fetching query results:\n')  
sql = "SELECT * FROM imdb.language WHERE language_name like %s"  
cursor.execute(sql, ("F%",)) # comma needed for 1-item tuple  

for (language_id, language_name) in cursor:  
    print(language_id + " - " + language_name)  


#with open('some_script.sql', encoding='utf-8', mode='r') as f:  
#    cursor.execute(f.read(), multi=True)  


cursor.close()  
cnx.close()  

print('')  
log('finished')  

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:

XML


1
2
3
4
5


<dependency>  
    <groupid>com.konghq</groupid>  
    <artifactid>unirest-java</artifactid>  
    <version>3.4.02</version>  
</dependency>  

The code lists all images, and starts one container:

Java


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


import kong.unirest.HttpResponse;  
import kong.unirest.Unirest;  
import kong.unirest.JsonNode;  
import kong.unirest.json.JSONArray;  
import kong.unirest.json.JSONObject;  

public class App {  

    // NOT suitable for use in production - just a quick demo.  
    public static void main(String[] args) {  
        App app = new App();  
        app.listImages();  
        app.startContainer();  
    }  

    private void listImages() {  
        String url = "http://localhost:2375/images/json";  
        JSONArray array = Unirest.get(url)  
                .header("accept", "application/json")  
                .asJson()  
                .getBody()  
                .getArray();  

        for (int i = 0; i < array.length(); i++) {  
            JSONObject image = array.getJSONObject(i);  
            String s = image.getString("RepoTags");  
            System.out.println(s);  
        }  
    }  

    private void startContainer() {  
        String url = "http://localhost:2375/containers/imdb_test/start";  
        HttpResponse<JsonNode> response = Unirest.post(url)  
                .header("accept", "application/json")  
                .asJson();  
        System.out.println(response.getStatus() + " - "  
                + response.getStatusText());  
    }  

}  

The “start container” command returns one of the following codes:

204 – no error
304 – container already started
404 – no such container
500 – server error