With MySQL or MariaDB containers, you put the persistent database data on a Docker volume, but you still back up the database with mysqldump. This prevents the data you are backing up from being inconsistent, since you would have to make sure that no database operators are happening at the same time when you dump the database files. So you would have to stop the DB container before. Therefore it is best practice to backup the MySQL or MariaDB database running in the container via mysqldump. You simply pass the command into the container and let the output, which is sent via stdout, run to a file. If you want to compress it, run the output through gzip before. (more information here)

docker exec -e MYSQL_PWD=ROOTPASSWORT \
                DBCONTAINERNAME /usr/bin/mysqldump -u root DATABASENAME \
                | gzip > backup.sql.gz

If you want to know which containers are on top of the MySQL or MariaDB image you want to back up, the following command will do it:

docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1

--format '{{.Names}}:{{.Image}}'ensures that only the container name and the used Docker image of all running containers are output, the grep command then filters the output to MySQL and MariaDB images only. The backup-docker-mysql.sh script bundles the two commands and backs up all MySQL/MariaDB databases running as containers on the Docker host to the defined backup folder. The database backups are timestamped and can be automatically deleted after X days (DAYS). It is also important to note that since this is a local backup, you should definitely backup the backup files to some other system or to the cloud (Dropbox, Google Drive and co). The script is best placed in /usr/local/sbin/backup-docker-mysql.sh. This can then be started automatically, regularly via cron-job.

Script backup-docker-mysql.sh

The script can simply be created in /usr/local/sbin/. After the creation assign the execute right and define the following parameters in the script as desired:

Parameter Definition Beispiel
BACKUPDIR defines the backup folder, if it does not exist, it will be created automatically
BACKUPDIR=/backup/mysql
DAYS defines how long a backup is kept
DAYS=3
TIMESTAMP defines the timestamp used in the backup filename
TIMESTAMP=$(date +"%Y%m%d%H%M")
CONTAINER defines the containers whose databases are to be backed up. If there is more than one container, simply put a space between the different container names. Here you can also use

docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1

can be used, for example, to save all containers based on the mysql or mariadb image, or to filter them further with grep. (See comments in the script)

CONTAINER="mysqlcontainer 1 mysqlcontainer2 mysqlcontainer3"
VOLUME=CONTAINER=$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1)
CONTAINER=$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1 | grep -v 'container1\|container2')

Download of the script possible here: https://www.perdichizzi.net/repo/backup/backup-docker-mysql.sh

/usr/local/sbin/backup-docker-mysql.sh

#!/usr/bin/env bash
#Set the language
export LANG="en_US.UTF-8"
#Load the Pathes
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
# set the variables

# Where to store the Backup files?
BACKUPDIR=/backup/mysql

# How many Days should a backup be available?
DAYS=2

# Timestamp definition for the backupfiles (example: $(date +"%Y%m%d%H%M") = 20200124-2034)
TIMESTAMP=$(date +"%Y%m%d%H%M")

# Which Containers do you want to backup?
# Container names separated by space
#CONTAINER="mysqlcontainer 1 mysqlcontainer2 mysqlcontainer3"
# you can use "$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1)"
# for all containers which are using mysql or mariadb images
#CONTAINER=$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1)
# you can filter all containers with grep (include only) or grep -v (exclude) or a combination of both
# to do a filter for 2 or more arguments separate them with "\|"
# example: $(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1 | grep -v 'container1\|container2')
#CONTAINER=$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1 | grep -v 'container1\|container2')
CONTAINER=$(docker ps --format '{{.Names}}:{{.Image}}' | grep 'mysql\|mariadb' | cut -d":" -f1)

### Do the stuff
echo -e "Start $TIMESTAMP Backup for Databases: \n"
if [ ! -d $BACKUPDIR ]; then
        mkdir -p $BACKUPDIR
fi

for i in $CONTAINER; do
        MYSQL_DATABASE=$(docker exec $i env | grep MYSQL_DATABASE |cut -d"=" -f2)
        MYSQL_PWD=$(docker exec $i env | grep MYSQL_ROOT_PASSWORD |cut -d"=" -f2)
        echo -e " create Backup for Database on Container:\n  * $MYSQL_DATABASE DB on $i";
        docker exec -e MYSQL_DATABASE=$MYSQL_DATABASE -e MYSQL_PWD=$MYSQL_PWD \
                $i /usr/bin/mysqldump -u root $MYSQL_DATABASE \
                | gzip > $BACKUPDIR/$i-$MYSQL_DATABASE-$TIMESTAMP.sql.gz
        # dont delete last old backups!
        OLD_BACKUPS=$(ls -1 $BACKUPDIR/$i*.gz |wc -l)
        if [ $OLD_BACKUPS -gt $DAYS ]; then
                find $BACKUPDIR -name "$i*.gz" -daystart -mtime +$DAYS -delete
        fi
done
echo -e "\n$TIMESTAMP Backup for Databases completed\n"

finally set the execute right:

chmod +x /usr/local/sbin/backup-docker-mysql.sh

The script can now be executed via

backup-docker-mysql.sh

can be executed.

Set up regular backup (cron-job)

If you want to execute the script regularly, for example daily, it is sufficient to place a symlink in the cron.daily directory:

ln -s /usr/local/sbin/backup-docker-mysql.sh /etc/cron.daily/backup-docker-mysql

Restore

The restore is done manually via mysql command. The required variables (DB name and root password) are simply output with the following command:

docker exec CONTAINERNAME env

With this information you can then simply edit and execute the following command

zcat BACKUPFILE.sql.gz | docker exec -i CONTAINERNAME /usr/bin/mysql -u root --password=ROOTPASSWORD DATABASENAME

now the database should be fed with the SQL backup.