In this post, I share with you a straightforward trick to backup and restore MySQL data from and into a Docker container.
I usually need to transfer my apps from a server to another, so most of the time I need to backup and restore my MySQL data from and into other Docker containers. Here is how I proceed :
1. Create MySQL Backup
docker exec $CONTAINER_NAME /usr/bin/mysqldump --no-tablespaces -u$DB_USER --password=$DB_PASSWORD $DB_NAME> db_dump.sql
2. Restore MySQL Backup
For the restoration process, we have two choices :
2.1. Restore before container creation
Create a folder (
./backups/db/ in our example) where to place your dump file. Docker will use it to populate the mysql container on creation.
version: '3.3' services: db: image: mysql:5.7 restart: always volumes: - ./backups/db/:/docker-entrypoint-initdb.d
2.2. Restore after container creation
docker exec -i $CONTAINER_NAME mysql -u$DB_USER --password=$DB_PASSWORD $DB_NAME --force < db_dump.sql
Note : Think to put password inside quotes. 😉
With bash, you can also load your
.env file, so your credentials are not in a file in the repo, but just in your .env file.
#!/bin/bash # load .env set -o allexport; . ./.env; set +o allexport # setup TIMESTAMP=$(date +%Y-%m-%d__%H.%M) BACKUP_DIR="backups/db" CONTAINER_NAME="db" # dump docker exec $CONTAINER_NAME /usr/bin/mysqldump -u$DB_USER --password=$DB_PASSWORD $DB_NAME> $BACKUP_DIR/dump__$TIMESTAMP.sql