Docker MySQL backup

Before you begin

In this tutorial, we’ll learn how to backup a MySQL or a MariaDB database. A machine with Docker installed will be required to follow this tutorial. To backup the MySQL database we are going to use mysqldump from a Docker container. Please, make sure you can pull official images from Docker Hub like MySQL or MariaDB.

Backup a local non-dockerized or remote MySQL database

Command to backup a local non-dockerized or a MySQL database from a remote MySQL server using Docker:

docker run -it mysql:5.7 /usr/bin/mysqldump \
  -h [MYSQL_HOST] -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] > backup.sql

Command to backup a local non-dockerized or a remote MySQL database using Docker with compression (using gzip):

docker run -it mysql:5.7 /usr/bin/mysqldump \
  -h [MYSQL_HOST] -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] | gzip -9 > backup.sql.gz

Previous examples have been tested using MYSQL version 5.7 and MariaDB 10.3.

Backup an already containerized local MySQL database

Command to backup a containerized MySQL database into a plain backup.sql file:

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] > backup.sql

Command to backup a containerized MySQL database into a compressed backup.sql.gz file (using GNU zip):

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] | gzip -9 > backup.sql.gz

Other MySQL Backup options

In previous examples we’ve always tried to backup a single MySQL database but there are other options available. Let’s have a look at some other popular options, all running mysqldump from Docker:

Backup a MySQL table

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] [MYSQL_TABLE] > backup.sql

Backup a MySQL table with condition

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  [MYSQL_DATABASE] [MYSQL_TABLE] \
  --where="[CONDITION_HERE]" > backup.sql

Backup more than one MySQL database at a time

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  --databases [MYSQL_DATABASE_1] [MYSQL_DATABASE_N] > backup.sql

Backup all MySQL databases or the entire RDBMS MySQL server

docker exec [MYSQL_CONTAINER] /usr/bin/mysqldump \
  -u [MYSQL_USER] --password=[MYSQL_PASSWORD] \
  --all-databases > backup.sql

Finally, we have a post called “How to restore a MySQL database using Docker” that might be useful later on if you need to restore a MySQL backup.


Recommended books to expand your MySQL knowledge:

Finally, you should definitely take a look at these books to fuel your Docker knowledge: