MariaDB mySQL backup and restore in Docker
As soon as you use Docker for a MariaDB or MySQL database, importing or backing up an existing database is somewhat more complex, as the containers are separate from the host system. However, you can still create and restore backups easily using a simple trick.
For example, you can mount a folder from the host to the container in your docker-compose.yml, then connect to the database container and work via the command line. Alternatively, you can use the following one-liners to export or import your database directly.
Breaking Change (May 2024):
MariaDB introduced a new directive in the dumps of mariadb-dump/mysqldump in May 2024. Dumps created with current MariaDB versions can no longer be imported by older MySQL/MariaDB clients and lead to errors.
Backup using mysqldump
docker exec db_container_name mysqldump [--user username] [--password=password] database_name > /your/backup/path/db.sqlNote:
mysqldumpcan still be used for older MariaDB or MySQL images; the command works for both MariaDB and MySQL, provided the respective tool is installed in the container.
Backup with mariadb-dump
As of MariaDB 11.0.1, mysqldump is no longer included in the official Docker image and has been replaced by mariadb-dump. You should therefore preferably use mariadb-dump for backups. The procedure is identical:
docker exec db_container_name mariadb-dump [--user username] [--password=yourpassword] database_name > /your/backup/path/db.sqlRestore the database
To restore a backup, use the following command:
docker exec -i db_container_name mysql [--user username] [--password=password] database_name < /your/backup/path/db.sqlHere the command for MariaDB is still the same!
Backup and restore in one command
If you ever come to the case where you want to export the database from one database container and import it into a new one, the following command will help you (MYSQL):
docker exec db_container_name mysqldump [--user username] [--password=password] database_name | docker exec -i second_db_container_name mysql [--user username] [--password=password] -C database_nameThe following command is now used for MariaDB databases:
docker exec db_container_name mariadb-dump [--user username] [--password=password] database_name | docker exec -i second_db_container_name mysql [--user username] [--password=password] database_nameCheers!