How to backup your Mariadb in Docker via crontab

If you have a docker environment with some Mariadb instances, you want to backup these db regularly
The Mariadb documentation provide an easy line : mariadb-dump db_name > backup-file.sql
If you don't want to specify a dbname just add --all-databases

You want to store your backup outside of the container, there's two ways :

• extract inside docker with a mounted volume
• extract outside docker
  I believe this solution is safer as a volume can be erased if incorrectly setup.

To make the process easy, I'll create a script bash.
What you need:

• the container name
• the root password to allow the extraction. The best way is to use the secret file used in your docker-compose config.
• the backup path

The script may look like this:

#!/bin/bash
DOCKERPATH=/docker/container/
BACKUPPATH=$DOCKERPATH/backup/
BACKUPNAME=backup_$(date +"%Y%m%d").sql

/usr/bin/docker exec -i  instance_name mariadb-dump --all-databases -uroot -p$(cat $DOCKERPATH/.mariadb_root_password) > $BACKUPPATH/$BACKUPNAME

Very important : the parameter for exec is -i and not -it as t will not work with crontab.
-p$ will be generated on your docker server. -p\$ will be executed on your instance. so if you use docker secret it's better to use: -p\$(cat /run/secrets/mariadb_root_password). Not tested on my side yet.

Now you can test you script manually. But it may not work with crontab. execute crontab -e to edit your crontab. set the crontab expression you desire, then the path of your script:

15 2 * * *  /docker/container/backup.sh 2>&1 | logger -t backup

2>&1 will redirect stderr to stdout. It will help debug and check for issue. logger -t backup will write everything in the linux logger, by default the journalctl. you can check your logs with journalctl --follow -t backup

To improve your script, you can add a check, on the file created:

CHECK=$(tail -n1 $BACKUPPATH/$BACKUPNAME)
echo "$CHECK"
if [[ "$CHECK" = "-- Dump completed"* ]];
   then
     echo Check OK
   else
     echo ERROR backup incorrect 
     exit 1
fi

Also you can make some automatic cleaning;

### remove +10days
find $BACKUPPATH/backup*.sql -maxdepth 1 -mtime +10 -type f -delete

Do not forget to test your restoration process.