MySQL Backup and Restore in Docker

Sometimes it’s useful to export and import your database data. In a dockerised world this adds a layer of complexity. Here are my methods.

Creating a database dump from a MySQL Docker container

docker run -it --rm --link my-old-database:mysql mysql sh -c 'echo "[client]\n host=\"$MYSQL_PORT_3306_TCP_ADDR\"\n user=root\n password=\"$MYSQL_ENV_MYSQL_ROOT_PASSWORD\"" > my.cnf && exec mysqldump --defaults-file=my.cnf --databases my_database_name' > db_dump.sql

This looks like a long one-liner, but it’s not that tricky.

It runs a new MySQL client, connected to your existing old database with the --link option (named “my-old-database” here, but you can run “docker ps” to find out what it’s called). It then executes a command on that MySQL client container.

The command generates a my.cnf file inside the Docker container using a simple echo, inserting the shared environment variables from the link. Note here, that these shared environment variables through link are being deprecated, so you may need to hard code your database credentials if you start getting “Unknown MySQL server host ‘-P’ (0)” errors.

That file is then used when running the mysqldump command via the defaults-file option. This is useful to bypass the “Using a password on the commandline can be insecure” warning being put into you dump file.

Lots of options are available for mysqldump depending on what you need.

Now that you have a dump of your database on your host, you can go ahead and move that to a new host or run it into a non-docker database.

Restoring a database dump to a MySQL Docker container

Make a new container called my-new-database

docker run -d --name my-new-database -e MYSQL_ROOT_PASSWORD=new-db-password mysql

Use docker exec with the -i option to feed your dump file in.

docker exec -i my-new-database mysql -uroot -pnew-db-password --force < db_dump.sql

Note, the --force option might not be necessary for you here, I use it to force my way around the problem that mysqldump still has with create table statements and foreign keys dependencies. You may get “Table x doesn't exist” and “Cannot add foreign key constraint” errors on the first run, but the dependant tables should exist on the second run, allowing a complete restoration.

To see you restored data, make a client container to connect to it

docker run -it --rm --link my-new-database:mysql mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

This will open up a MySQL terminal where you can “SHOW databases;” and “SELECT *” as you wish.