Before you begin

In this tutorial will learn how to backup a MySQL or a MariaDB database. A Linux machine and Docker will be required to follow this tutorial.

Backup a local non-dockerized or remote MySQL database

Command to backup a local non-dockerized or remote MySQL database 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 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 popular options:

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 DBMS

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