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

Question

We have a number of selenium tests that run on a production-like setup of our webapp. The problem is that some of the tests do stuff in the application that affects the database.

Would it be possible to have a data volume or similar, that we can "clone" and attach to a container before every test?

We really just need a MySQL database that can be quickly recreated before every test. And once in a while we would run schema migrations to that database.

Or is there another approach that would be more suited for this?

I basically use this exact approach. I just destroy the container after each use. — trey-jones
– trey-jones, Commented Jan 31, 2017 at 19:25
Hey @threeve. Can you explain how it's done? I'm a Docker newbie. And how fast is it? — John Knoop
– John Knoop, Commented Jan 31, 2017 at 19:36

TomNash · Accepted Answer · 2020-04-25 22:22:22Z

11

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:

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
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
Stop the container. We don't want MySQL running. The data will remain in the named volume.

$ docker stop my-mysql
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
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
(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

edited Apr 25, 2020 at 22:22

TomNash

3,3272 gold badges28 silver badges68 bronze badges

answered Jan 31, 2017 at 19:48

mkasberg

17.7k3 gold badges46 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

John Knoop Over a year ago

Thanks! Option 1 sounds great. I was doing some research down that path at first, because I though it sounded nice to be able to commit each change to the database as a separate version of the image. But then I read that storing data in containers is a bad idea, and that volumes are the way to go. Or am I misinterpreting you?

mkasberg Over a year ago

For most use cases, storing data into containers is a bad idea. But there's nothing inherently wrong about it, depending on circumstance. Volumes are great for most use cases, but the problem with volumes in this case is that data in volumes is mutable. To achieve what we're talking about using volumes, you'd have to keep a "clean" volume and copy it every time you wanted to use it. Now we're back to copying data on each run, slowing the process down. Perhaps I'll still add that as "Option 3". I'm also interested to see if anyone else has come up with cool solutions for this.

John Knoop Over a year ago

Okay. One thing about step 5: I didn't realize I need a dockerfile for this. Wont we just save it as an image? Also: how would I go about making changes to the data? Thanks again.

mkasberg Over a year ago

By "save it as an image", I assume you mean with docker commit. Best practice is to prefer using Dockerfiles over docker commit because it allows your docker images to be version controlled more easily. If you want to use docker commit anyway, you should be able to just run docker commit after step 3, skipping the rest of the steps. (I'm not entirely sure how docker commit interacts with the volume at /var/lib/mysql, but I would expect this to work).

mkasberg Over a year ago

I fixed the command for step 4. ./ should have been $(pwd) because docker requires full paths. Also, my arguments to tar were in the wrong order. That's also been corrected.

|

Pang · Accepted Answer · 2024-05-29 07:39:10Z

1

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/bash
PASSED_ARGS="${@}"

docker-compose -f docker-compose.test.yml stop database_test
docker-compose -f docker-compose.test.yml rm -vf database_test
docker-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.

edited May 29, 2024 at 7:39

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Jan 31, 2017 at 19:57

trey-jones

3,4671 gold badge29 silver badges37 bronze badges

2 Comments

John Knoop Over a year ago

Hi and thanks! I'm not really sure how to interpret the yaml file, but do I understand you correctly if I say that you store your data in .sql files, rather than images?

trey-jones Over a year ago

That's right, the image is just the official mysql image and the data is populated when the container is created.

Collectives™ on Stack Overflow

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

2 Answers 2

Option 1: Build the data into the container

Option 2: Use docker-entrypoint-initdb.d

14 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Option 1: Build the data into the container

Option 2: Use docker-entrypoint-initdb.d

14 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related