backup dockerise mysql database from host volume

Question

I use a VPS (my host) where a LAMP server was installed. I already have a database associated to host mariaDB server Ver 15.1 Distrib 10.1.47 and automysqlbackup backups it. This part works well.

For a "new project", we use now docker servers and one with a mysql 5.7 and a volume to save this new project database in a host folder, for exemple: /path/on/host/dockerise/db/

I want to backup too this new database.

What is the right way?

1. Is there a way with automysqlbackup? I think no because it uses host mariaDb server and mysqldump, and you shouldn't have two servers working on the same database (https://dev.mysql.com/doc/refman/5.7/en/multiple-data-directories.html)
2. Directly with mysqldump ?
3. Or just create a bash script and CRON it?

Answer 1

I have a little script for my MariaDb database inside a docker container. The setup is comparable. I have a database container with a mounted volume. Furthermore I have a second volume for backups mounted under /backup.

The script is run on the host via cron and uses the database instance inside the container, so only one database accesses the data. That is the docker exec part. I'm sure, you can adopt it to mysql. You only have to configure the CONTAINER_NAME and change the command inside the container.

#!/bin/bash
DATE_TIME=`date +"%Y_%m_%d_%H_%M"`
CONTAINER_NAME="mariadb"
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUPFILE=${DATE_TIME}_${CONTAINER_NAME}.backup.gz

echo "Backup of MariaDb"
echo ""
echo "Old backps will be deleted."
echo "Datenbank Container Name: $CONTAINER_NAME"
echo "Datenbank Container:  $CONTAINER"

echo "Writing backup to ${BACKUPFILE}"
echo ""
docker exec ${CONTAINER} sh -c "find /backup/ -mindepth 1 -mtime +8 -delete; /usr/bin/mariabackup --backup --user root --password=${PASSWORD} --stream=xbstream | gzip > /backup/${BACKUPFILE}"
echo ""
echo "Backup finished"

Answer 2

based on @ulrich answer, but with mysqldump :

DATE_TIME=`date +"%Y%m%d_%H%M"
CONTAINER_NAME="mysql_server"
CONTAINER=`docker ps -q -f name=${CONTAINER_NAME}`
PASSWORD=`docker exec ${CONTAINER} sh -c 'echo $MYSQL_ROOT_PASSWORD'`
BACKUP_FILE=${DATE_TIME}_${CONTAINER_NAME}.backup.sql.gz
CONTAINER_BACKUP_DIR="/var/lib/mysql/"
HOST_DATA_DIR="/path/on/my/VM/correspond/to/docker/volume/"
HOST_BACKUP_DIR="/other/path/on/VM/"
## run backup in container
docker exec ${CONTAINER} sh -c "/usr/bin/mysqldump -h localhost -u root -p${PASSWORD} --all-databases | gzip -9 -c > ${CONTAINER_BACKUP_DIR}${BACKUP_FILE}"
## move to final folder 
mv ${HOST_DATA_DIR}${BACKUP_FILE} ${HOST_BACKUP_DIR}

HOST_DATA_DIR and HOST_BACKUP_DIR are differents because I have an other daemon to save backup to a data center, and because in HOST_DATA_DIR, root can only read and execute (r_x).
For all folder paths, be careful to have a final '/', otherwise you should be surprised!

and if you run it automatically with a CRON job, it should be helpfull to manage rotate to delete old backup:

## rotate save management to keep only last MAX_BACKUP files
MAX_BACKUP=3
${HOST_DATA_DIR}rotate-databases-backup.log
echo ${HOST_BACKUP_DIR}${BACKUP_FILE} >> ${HOST_DATA_DIR}rotate-databases-backup.log
BACKUPED_NB=$(ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz | wc --lines)
TOTAL=$(wc --lines < ${HOST_DATA_DIR}rotate-databases-backup.log)
FILES_NB_TO_DELETE=$(expr $TOTAL - $MAX_BACKUP)
if [[ $FILES_NB_TO_DELETE -gt 0 ]]; then
    head --lines=$FILES_NB_TO_DELETE ${HOST_DATA_DIR}rotate-databases-backup.log | xargs rm
    ls ${HOST_BACKUP_DIR}*${CONTAINER_NAME}.backup.sql.gz > ${HOST_DATA_DIR}rotate-databases-backup.log
fi