Skip to main content

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
Mystro Ken Avatar

Mystro Ken

Cras mattis consectetur purus sit amet fermentum. Nullam id dolor id nibh ultricies vehicula ut id elit. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Follow me on Twitter