percona-xtrabackup-cron

Dockerized hot MySQL backup with Percona Xtrabackup under cron.

Setup

Setup is done through a number of environment variables:

Variable name	Description	Example
CRON	Cron rules to run	Do backup at 01:00am each day: 0 1 * * * backup _name_ "--compress" >/dev/console 2>&1
TZ	The time zone	Europe/Berlin
MYSQL_HOST	MySQL host name	172.17.0.1
MYSQL_PORT	MySQL port	3306
MYSQL_USER	MySQL user	root
MYSQL_PASS	MySQL user password	[your password here]

MYSQL_ variables are used to connect to the database. You can omit these settings and mount a custom .cnf file to the /root/.my.cnf inside the container.

Example .my.cnf file

[xtrabackup]
user=user
password=password
host=host
port=port
# any other settings here if you wish

Container commands

Command	Description
xtrabackup	The Percona Xtrabackup binary
backup	xtrabackup proxy. First argument stands for the backup name (eg. rancher), second are xtrabackup additional parameters (eg. --compress)

Standalone Docker setup

Example setup to backup Rancher database periodically.

docker run -it --rm -d --restart=unless-stopped --name backup-rancher \
    # path to mysql physical files (read only for security)
    -v /var/lib/mysql:/var/lib/mysql:ro \
    # storage location
    -v /mnt/backup/rancher:/mnt/backup/rancher \
    # backup log (not required)
    -v /var/backup/backup.log:/var/backup.log \

    -e CRON='0 1 * * * backup rancher "--compress --compress-threads=4" >/dev/console 2>&1' \
    -e TZ=Europe/Warsaw \
    -e MYSQL_HOST='172.17.0.1' -e MYSQL_PORT=3306 -e MYSQL_USER=root -e MYSQL_PASS=password

Make sure the backup location directory name matches your backup name that you set up in the cron rule. This will ensure symlinks created in the container are also usable on your host machine. In this case backup name rancher matches the rancher directory inside /mnt/backup as all backups are stored in the /mnt/backup inside the container.

The --backup --target-dir=/path arguments are added automatically.

Backup log

If a physical file is mounted to the container's /var/backup.log location tool will prepend the file after a successful backup with a CSV line:

[unix time];[yyyy-mm-dd hh:mm:ss];[log name];[log size (eg. 32M)];[log path]

We then use this file to generate a Jekyll based HTML report.

Generated backup tree example

[root@host /mnt/backup/rancher]$ ls -l
total 4
drwxr-xr-x. 4 root root 4096 04-02 01:30 2018
lrwxrwxrwx. 1 root root   41 04-13 01:30 latest -> /mnt/backup/rancher/2018/04/12/1523575621
lrwxrwxrwx. 1 root root   41 04-13 01:30 latest-full -> /mnt/backup/rancher/2018/04/12/1523575621

   |-2018
   |---04
   |-----05
   |-------1522970821
   |-----06
   |-------1523057221
   |-----07
   |-------1523143621
   |-----08
   |-------1523230021
   |-----09
   |-------1523316421
   |-----10
   |-------1523402821
   |-----11
   |-------1523489221
   |-----12
   |-------1523575621