Simple bash script to make mysql backup via docker

README.md

Attention

This wrapper use hardcoded data, you should avoid this. This doesn't meet best practices writing bash scripts but I leave this as it is because I use other methods to secure it.

My first recommendation is add separate mysql (read only) user for this purpose. It dramatically reduce possibly attack surface.

You can do this easily log in as root in your mysql instance and run:

Locally:

mysql -u root -p

Remotely:

mysql -P 1234 -h srv1.mysql.com -u root -p

Let's say you want backup all tables in your database (wordpress_db), you can do this by:

GRANT LOCK TABLES, SELECT ON wordpress_db.* TO 'backuper'@'%' IDENTIFIED BY 'testpass';

As you can see above we defacto create new user with password 'testpass' and give him minimal rights, we'll use this in a second. Statement wordpress_db.* means that you allow for "select" and "lock tables" for all tables in that database.

In my case this script is run as cron job that make database backup on my Synology NAS (to increase security I use WORM protected share). Because docker require "sudo" prefix I run this job as root what makes another problem - root account on Synology is not tty - this is why we have "docker -i" instead more common "docker -it". During dump process mysqldump complains about using password in plaintext as command argument, I shut him up by redirecting std error to /dev/null at the end.

Bash script is simple, so I think it doesn't require more explanation so I just paste it here

backup.sh

#!/bin/bash

TIME=$(date +"%d%m%Y")
BACKUP_DIR="/volume1/Backup_NET"
BACKUP_NAME="backup_${TIME}"

MYSQL_HOST="srv1.mysql.com"
MYSQL_PORT=3306
MYSQL_USER="backuper"
MYSQL_PASS="testpass"
MYSQL_DB="wordpress_db"

docker run -i --rm mysql:latest /usr/bin/mysqldump -P $MYSQL_PORT -h $MYSQL_HOST -u $MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB > "${BACKUP_DIR}/${BACKUP_NAME}.sql" 2>/dev/null