Backup (and Restore) MySQL Data in Docker

Even while in development, making a backup of your MySQL database is a necessary habit. If you happen to be running your database in a Docker container, things are necessarily a tad more complex.

The advantage to the methods below is that they work whether your Docker is running in a Linux environment or within one of the Docker for Desktop implementations. The latter make things a bit more complex because Docker is running within a virtual machine which isn’t easily accessible to you via the command line.

Backup MySQL to SQL File

Backup to SQL

This method takes advantage of the mysqldump utility already installed in the official MySQL Docker image. You’ll need the container_id of the MySQL instance which you can easily get from docker ps. If you can’t use the root MySQL user, include the --no-tablespaces flag since you might not have the necessary privileges. If you haven’t explicitly worked with tablespaces, this is safe to do so.

docker exec <container_id> /usr/bin/mysqldump -u <username> --password=<password> --no-tablespaces <database_name> > backup.sql

Restore from SQL

And to restore that SQL file, we can simply use the mysql command line tool to run the SQL statements in it.

docker exec -i <container_id> mysql -u <user> --password=<password> blog < backup.sql

Backup Entire MySQL Volume

Another method for backing up your MySQL data is to backup the entire named volume where your data lives. It can be easier to work with SQL files but this method is helpful when making simpler changes. Such as renaming your Docker stack or the named volume itself. There are Docker images created specifically for this purpose since the method from the official documentation can be a bit complicated. That method assumes you have a container running with the volume already mounted. Below, I’m using the loomchild/volume-backup image for this purpose but you can find others on Docker Hub.

Backup Volume

The command below stores the backup in the home directory of the current user as a tar.bz2 archive. If you want to store the volume somewhere else, simply change the ~/ to your desired location. Remember to also change the location when you go to restore the volume using the restore command below.

docker run -v <volume_name>:/volume -v ~/:/backup --rm loomchild/volume-backup backup <backup_name>

Restore Volume

docker run -v <volume_name>:/volume -v ~/:/backup --rm loomchild/volume-backup restore <backup_name>