How to backup data from docker-compose MariaDB container using mysqldump

4 ans ago
admin
2 minutes

For containers with a MYSQL_ROOT_PASSWORD stored in .env

This is the recommended best practice. For this example, we will assume that .env looks like this:

.env 📋 Copy to clipboard⇓ Download
MARIADB_ROOT_PASSWORD=mophur3roh6eegiL8Eeto7goneeFei

To create a dump:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
source .env && docker-compose exec mariadb mysqldump -uroot -p${MARIADB_ROOT_PASSWORD} –all-databases > mariadb-dump-$(date +%F_%H-%M-%S).sql

To restore a dump from mariadb-dump.sql:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
source .env && docker-compose exec -T mariadb mysql -uroot -p${MARIADB_ROOT_PASSWORD} < mariadb-dump.sql

Note that you have to replace mariadb by the name of your container in docker-compose.yml.

For containers with a MYSQL_ROOT_PASSWORD set to some value not stored in .env

This is secure but you typically have to copy the password multiple times: One time for the mariadb container, one time for whatever container or application uses the database, and one time for any backup script that exports a SQL dump of the entire database

To create a dump:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
docker-compose exec mariadb mysqldump -uroot -pYOUR_MARIADB_ROOT_PASSWORD –all-databases > dump-$(date +%F_%H-%M-%S).sql

To restore a dump from mariadb-dump.sql:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
docker-compose exec -T mariadb mysql -uroot -pYOUR_MARIADB_ROOT_PASSWORD < mariadb-dump.sql

Replace YOUR_MARIADB_ROOT_PASSWORD by the password of your installation.

Furthermore, you have to replace mariadb by the name of your container in docker-compose.yml

For containers with MYSQL_ALLOW_EMPTY_PASSWORD=yes

This configuration is a security risk – see The security risk of running docker mariadb/mysql with MYSQL_ALLOW_EMPTY_PASSWORD=yes.

To create a dump:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
docker-compose exec mariadb mysqldump -uroot –all-databases > mariadb-dump-$(date +%F_%H-%M-%S).sql

To restore a dump from mariadb-dump.sql:

backup-data-from-docker-compose-mariadb-container-using-mysqldump.sh 📋 Copy to clipboard⇓ Download
docker-compose exec -T mariadb mysql -uroot < mariadb-dump.sql