Backup docker volume or only mysqldump

Question

I have an mysql instance running in a docker container. I mount a volume in /var/lib/mysql to preserve the data after shutting down the container. I think i have two options to backup my database to my host system:

Backup the complete volume:

docker run --rm --volumes-from db -v {BACKUP_PATH_ON_HOST_SYSTEM}:/backup ubuntu tar cvf /backup/backup.tar /var/lib/mysql

Only backup a mysqldump

Basically run above command but instead of backing up the volume i create a mysqldump which i would copy to /backup.

Which option is better?

user_1938889 · Answer

I have a similar requirement. In my case, I'm using an old mysql Docker image on purpose like so:

  db:
    image: mysql:5.6
    container_name: ${COMPOSE_SITE_NAME}_mysql
    volumes:
      - db_files:/var/lib/mysql
      # Load the initial SQL dump into the DB when it is created.
      # This only runs once if the DB is empty.
      - ${SQL_DUMP_FILE}:/docker-entrypoint-initdb.d/dump.sql
    environment:
      MYSQL_ROOT_PASSWORD: ${WORDPRESS_DB_PASS}
      ...

volumes:
  db_files:
    name: ${COMPOSE_SITE_NAME}_db_files

If the volume is lost, then it can be recreated with a dump file. In my case, I prefer to make a dump file instead of preserving the cacophony of SQL files in that /var/lib/mysql folder.

docker-compose exec db sh -c '\
  mysqldump -uroot -p$MYSQL_ROOT_PASSWORD --all-databases --routines --triggers \
' | gzip -c > /path/outside/docker/backup-`date '+%Y-%m-%d'`.sql.gz

This will create a compressed dump file on your host outside Docker due to the stdout redirect (>). I use the sh -c '' so I can reuse the MYSQL_ROOT_PASSWORD env var in the container. Feel free to adjust this to suit your MySQL requirements, like specifying a limited user.

With the default flags, the dump file will have DROP TABLE IF EXISTS statements so you can replace an existing DB without deleting the volume (docker-compose down then docker volume rm ...).

Backup docker volume or only mysqldump

1 Answers