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

we have number of selenium tests run on production-like setup of our webapp. problem of tests stuff in application affects database.

would possible have data volume or similar, can "clone" , attach container before every test?

we need mysql database can recreated before every test. , once in while run schema migrations database.

or there approach more suited this?

this great question, , potentially great use case docker. there many ways there ways backup mysql database. i'll explain few of them below.

in general, problem run mysql containers use volume /var/lib/mysql (where data stored). destroying container not enough clear data - need clear volume. when you're doing docker rm clear old container, pass -v flag remove volumes too.

option 1: build data container

it possible build data container. advantage of container not spend time setting data each time it's run. advantage becomes more significant big data set takes long time set or tear down. in other words, "resetting" database instantaneous.

on basic level, want this:

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

the tricky part here creating mysql_data.tar.gz file (which tar.gz backup of /var/lib/mysql). can this:

1. run container (i'll use mysql:latest here) empty database. note we're using named volume , 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 database. build schema , insert of test data. let's have database backup, backup.sql.

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

3. stop container. don't want mysql running. data remain in named volume.

   $ docker stop my-mysql

4. create backup of /var/lib/mysql. note we're using 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 have gzipped data /var/lib/mysql, use in dockerfile. note need copy @ / because of way zipped it:

   add mysql_data.tar.gz /

   if don't have dockerfile, make 1 first line

   from mysql:5.7

6. (see working) build dockerfile container image has data. run container.

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

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

docker automatically extract file part of build. you're done. container dockerfile have clean data in it. "reset" container, stop & delete volume using /var/lib/mysql.

to edit data, repeat process, substitute existing container in step 1. step 2, make changes. you'll produce new mysql_data.tar.gz, can version control if like. after rebuilding dockerfile, can publish under new tag if like.

option 2: use docker-entrypoint-initdb.d

the mysql docker image has feature run sql files in /docker-entrypoint-initdb.d when container run first time. advantage of can use regular mysql dumps create data. disadvantage is slower database start, since it's restoring of data each time.

if have mysqldump of data @ ./backup.sql, can this:

$ docker run -e mysql_database=db_name -e mysql_root_password=password -d --name my-mysql -v ./backup.sql:/docker-entrypoint-initdb.d/backup.sql -p 3306:3306 mysql:latest 

when you're done, remove container volumes.

$ docker rm -v my-mysql