Docker MySQL Backup to Local

Doing a backup of your mysql container's DB can be automated just like any other task.

Script below is one way to do it.

You may or may not provide a parameter to the script and you'll always get the gzipped version of the backup + its sha256 checksum.

Sample Use Case:

  • use it without parameters
1~$ ./backup_db.sh
2[INFO] DB backup for 'cooltool_db' copied to /home/icasimpan/work/backups...
3[INFO] Checking for backup integrity:
4cooltool_db-backup-2022.03.28.084916.sql.gz: OK
  • with a parameter
1~$ ./backup_db.sh DONOTDELETE
2[INFO] DB backup for 'cooltool_db' copied to /home/icasimpan/work/backups...
3[INFO] Checking for backup integrity:
4cooltool_db-backup-DONOTDELETE.sql.gz: OK

The backup script

  1#!/bin/bash
  2
  3## Script can be run with or without parameters.
  4## TIMESTAMP variable is used to create the backup file in db container
  5##
  6##   * with parameter    - $1 is used as value
  7##   * without parameter - output of $(date +%Y.%m.%d.%H%M%S) is used.
  8##
  9TIMESTAMP=${1:-"$(date +%Y.%m.%d.%H%M%S)"}
 10
 11## --------------------- MODIFICATION AREA HERE -----------------------
 12##
 13#~PROJECT_DIR="$HOME/projects"
 14#~PROJECT_ROOT="${PROJECT_DIR}/COOL"
 15PROJECT_DIR="$HOME/work"
 16PROJECT_ROOT="${PROJECT_DIR}/cooltool"
 17##
 18##
 19DB_SERVICE_NAME='mysql'    ## Different service name? Change here
 20##
 21## --------------------- END: MODIFICATION AREA HERE -----------------------
 22
 23## ---------------------------------------------
 24## MAIN_ENV_FILE - expected to have the following vars defined:
 25##   * MYSQL_ROOT_PASSWORD
 26##   * MYSQL_DATABASE
 27##
 28MAIN_ENV_FILE=${PROJECT_ROOT}/.env
 29. $MAIN_ENV_FILE
 30
 31## Make sure backup folder exist
 32BACKUP_DIR="${PROJECT_DIR}/backups"
 33mkdir -p ${BACKUP_DIR}
 34
 35DOCKER_COMPOSE_BIN="docker-compose -f ${PROJECT_ROOT}/docker-compose.yml"
 36
 37## TODO: Check if docker service 'mysql' is up
 38
 39## ---------------------------------------------------------------------
 40## Generate credential file .my.cnf
 41## Makes backup easier as you need not provide credentials each time.
 42## ---------------------------------------------------------------------
 43tmp_mysql_creds=$(mktemp '/tmp/dbbackup_XXXX')
 44cat > $tmp_mysql_creds << _BLOCK_
 45[client]
 46user=root
 47password=$MYSQL_ROOT_PASSWORD
 48_BLOCK_
 49
 50### TODO: Cross-check created temp file to detect for password changes.
 51###  Maybe, check the $MAIN_ENV_FILE & that of BACKEND & FRONTEND to detect for changes.
 52##~ c752afe39bdb780ae860eca0d42293a3d1068256c5bf1d04214ccb256de0b69b  /tmp/dbbackup_kKXN
 53
 54## Copy credentials file to mysql service as /root/.my.cnf'
 55##
 56$DOCKER_COMPOSE_BIN cp $tmp_mysql_creds ${DB_SERVICE_NAME}:/root/.my.cnf
 57
 58## -------------------------------------------------------
 59## Generate the backup script
 60## -------------------------------------------------------
 61##
 62## NOTE: For those not too familiar with shell scripting, notice that some variable is escaped (e.g. \${BACKUPFILE})
 63##       while others are not (e.g. ${MYSQL_DATABASE}).
 64##
 65##       Those un-escaped are intended to use the values within this (parent) script, which means, it will no longer
 66##       be a variable in final backup script that would be copied to mysql container/service.
 67##
 68##       For those escaped ones, you may already have guessed by now. Yes, correct: it is intended to be a variable
 69##       in the final backup script.
 70##
 71tmp_dbdump_script=$(mktemp '/tmp/dbbackup_XXXX')
 72cat > $tmp_dbdump_script << _BLOCK_
 73#!/bin/bash
 74
 75timestamp=\$1
 76
 77BACKUPFILE="/root/${MYSQL_DATABASE}-backup-\${timestamp}.sql.gz"
 78mysqldump $MYSQL_DATABASE | gzip > \${BACKUPFILE}
 79sha256sum \${BACKUPFILE} > \${BACKUPFILE}.sha256
 80_BLOCK_
 81chmod +x $tmp_dbdump_script
 82
 83DBDUMP_BIN='/root/db-dump.sh'
 84## Copy the backup script to mysql container
 85$DOCKER_COMPOSE_BIN cp $tmp_dbdump_script ${DB_SERVICE_NAME}:${DBDUMP_BIN}
 86
 87## Dump sql database
 88$DOCKER_COMPOSE_BIN exec ${DB_SERVICE_NAME} ${DBDUMP_BIN} "$TIMESTAMP"
 89
 90## Copy the file to Docker host machine, including the checksum file
 91##
 92BACKUPFILE="/root/${MYSQL_DATABASE}-backup-${TIMESTAMP}.sql.gz"
 93$DOCKER_COMPOSE_BIN cp ${DB_SERVICE_NAME}:${BACKUPFILE} ${BACKUP_DIR}
 94$DOCKER_COMPOSE_BIN cp ${DB_SERVICE_NAME}:${BACKUPFILE}.sha256 ${BACKUP_DIR}
 95##
 96## ...and check if file was not modified in any way:
 97##
 98echo "[INFO] DB backup for '${MYSQL_DATABASE}' copied to ${BACKUP_DIR}..."
 99echo "[INFO] Checking for backup integrity:"
100cd $BACKUP_DIR
101sha256sum -c <(sed 's/\/root\///g' $(basename ${BACKUPFILE}.sha256))
102
103## Delete temp files
104rm $tmp_dbdump_script
105rm $tmp_mysql_creds