Using Docker to create "restorable" MySQL database for UI testing

This is a great question, and potentially a really great use case for Docker. There are as many ways to do this as there are ways to backup a MySQL database. I'll explain a few of them below.

Be warned, however, that you're making trade-offs. The downside of this approach is that your image can become quite large, and will take a longer time to pull.

Also, a problem that you will run into is that most MySQL containers use a volume for /var/lib/mysql (where data is stored). So destroying the container is not enough to clear the data - you also need to clear the volume. So when you're doing docker rm to clear your old container, pass the -v flag to remove volumes too.

Option 1: Build the data into the container

It is possible to build the data into the container. The advantage of this is that your container will not spend any time setting up data each time it's run. This advantage becomes much more significant with a big data set that takes a long time to set up or tear down. In other words, "resetting" this database is nearly instantaneous.

On a basic level, we want something like this:

ADD mysql_data.tar.gz /var/lib/mysql

The tricky part here is creating that mysql_data.tar.gz file (which is just a tar.gz backup of /var/lib/mysql). We can do it like this:

1. Run your container (I'll just use mysql:latest here) with an empty database. Note that we're using a named volume and we're forwarding port 3306.

   $ docker run -d --name my-mysql -v my-mysql-data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql:latest

2. Set up your database. Build the schema and insert all of your test data. Let's say you have a database backup, backup.sql.

   $ cat backup.sql | mysql -u root -ppassword -h 127.0.0.1

3. Stop the container. We don't want MySQL running. The data will remain in the named volume.

   $ docker stop my-mysql

4. Create the backup of /var/lib/mysql. Note that we're using the same named volume.

   $ docker run --rm -v my-mysql-data:/var/lib/mysql -v $(pwd):/backup mysql:latest tar czvf /backup/mysql_data.tar.gz /var/lib/mysql

5. Now that you have the gzipped data from /var/lib/mysql, use that in your Dockerfile. Note that we need to copy it at / because of the way we zipped it:

   ADD mysql_data.tar.gz /

   If you don't already have a Dockerfile, make one with the first line

   FROM mysql:5.7

6. (See it working) Build your Dockerfile into a container image that has your data. Then run the container.

   $ docker build -t my-data-image:latest .

   $ docker run -d -p 3306:3306 my-data-image:latest

Docker will automatically extract the file as part of the build. You're done. The container from the Dockerfile will always have your clean data in it. To "reset" the container, just stop it & delete the volume it was using for /var/lib/mysql.

To edit the data, repeat the process, but substitute your existing container in step 1. For step 2, make your changes. You'll produce a new mysql_data.tar.gz, which you can version control if you like. After rebuilding the Dockerfile, you can publish it under a new tag if you like.

Option 2: Use docker-entrypoint-initdb.d

The MySQL Docker image has a feature that it will run SQL files in /docker-entrypoint-initdb.d when the container is run for the first time. The advantage of this is it can use regular MySQL dumps to create the data. The disadvantage is it is slower for the database to start, since it's restoring all of your data each time.

If you have a mysqldump of your data at ./backup.sql, you can do something like this:

$ docker run -e MYSQL_DATABASE=DB_NAME -e MYSQL_ROOT_PASSWORD=password -d --name my-mysql -v $(pwd)/backup.sql:/docker-entrypoint-initdb.d/backup.sql -p 3306:3306 mysql:latest

When you're done, remove the container with its volumes.

$ docker rm -v my-mysql

I will use an example with a golang application server and a mysql database, because this is my primary use case:

version: '2'services:  app_test:    image: golang:1.7-alpine    volumes:      - ./:/go/path/to/src    links:      - database_test    environment:      GOBIN: /go/bin      APP_ENVIRONMENT: test      APP_DB_HOST: database_test      APP_DB_USERNAME: root      APP_DB_DATABASE: app    entrypoint:      - /bin/sh      - -c      - /go/path/to/src/build_and_test.sh  database_test:    image: mysql:5.7    volumes:      - ./schema/test/auto_tests_structure.sql:/docker-entrypoint-initdb.d/a.sql      - ./schema/test/auto_tests_data.sql:/docker-entrypoint-initdb.d/b.sql    ports:      - "3307:3306"    environment:      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"      MYSQL_DATABASE: app

The important parts are mounting the .sql files into the mysql container, which automatically populates the selected database (via environment variable MYSQL_DATABASE - this is in the docs for the official mysql images), and also the links item.

Running the tests looks like this:

#!/bin/bashPASSED_ARGS="${@}"docker-compose -f docker-compose.test.yml stop database_testdocker-compose -f docker-compose.test.yml rm -vf database_testdocker-compose -f docker-compose.test.yml run -e PASSED_ARGS="${PASSED_ARGS}" app_test

The main point is the first two docker-compose commands, which stop and destroy the database-test container with associated volumes. Then you run the container, which creates it anew.

As for speed, I am not satisfied with it, running Docker for Mac. But a guy on my team is running linux, and it's considerably faster for him.