Backup and Restore MySQL in Docker

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. 😉

3. Improvement

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