Automating Nightly MySQL Backups with cron

2017-05-20 · mysql, backup, linux, cron

I used to run backups by hand "when I remembered", which of course meant never. These days every small or medium MySQL box I look after gets a nightly mysqldump, compressed and rotated. It's boring, it restores anywhere, and it needs no extra packages. The tricky parts are keeping the password off the command line, not locking the app out, and not silently filling the disk.

This is what I run on CentOS 7 and Ubuntu 16.04 hosts with MySQL 5.6/5.7 or MariaDB 10.1. You want InnoDB tables (for non-locking dumps) and somewhere to put a week of dumps, ideally a separate mount or NFS share.

A dedicated backup user

I don't back up as root. The backup account needs read access plus a few global privileges.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'change-this-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

RELOAD and REPLICATION CLIENT are needed for --master-data, which records the binary log position in the dump so it can be used to seed a replica or for point-in-time recovery.

Credentials go in an options file

-pPassword in a cron job leaks to ps and to anyone who can read the crontab. Use an options file.

sudo install -m 600 -o root -g root /dev/null /root/.my-backup.cnf

Then put this in /root/.my-backup.cnf:

[client]
user     = backup
password = change-this-password
socket   = /var/lib/mysql/mysql.sock

On Ubuntu the socket is /var/run/mysqld/mysqld.sock. Because mysqldump reads --defaults-extra-file before any other option, the password never appears in the process list.

The script

Save this as /usr/local/sbin/mysql-backup.sh and make it executable with chmod 700.

#!/bin/bash
set -euo pipefail

BACKUP_DIR=/var/backups/mysql
RETENTION_DAYS=7
DEFAULTS=/root/.my-backup.cnf
DATE=$(date +%F)
LOG=/var/log/mysql-backup.log

mkdir -p "$BACKUP_DIR"

DATABASES=$(mysql --defaults-extra-file="$DEFAULTS" -N -e \
  "SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema','performance_schema','sys')")

for DB in $DATABASES; do
  OUT="$BACKUP_DIR/${DB}-${DATE}.sql.gz"
  if mysqldump --defaults-extra-file="$DEFAULTS" \
       --single-transaction --quick --routines --triggers --events \
       --master-data=2 --hex-blob "$DB" | gzip -6 > "$OUT"; then
    echo "$(date '+%F %T') OK   $DB $(du -h "$OUT" | cut -f1)" >> "$LOG"
  else
    echo "$(date '+%F %T') FAIL $DB" >> "$LOG"
    rm -f "$OUT"
  fi
done

find "$BACKUP_DIR" -name '*.sql.gz' -mtime +"$RETENTION_DAYS" -delete

Why these flags: --single-transaction gives a consistent snapshot of InnoDB tables without holding locks. --quick streams rows instead of buffering whole tables in memory. --master-data=2 writes the binlog coordinates as a comment. --hex-blob keeps binary columns intact through text encoding. If you still have MyISAM tables, they will be locked for the duration of their dump; there is no way around that with mysqldump.

The mysql database is deliberately included because it holds users and grants. Note that --events requires the EVENT privilege granted with the backup user, otherwise the dump prints a warning and skips them.

Cron

sudo crontab -e, then:

MAILTO=admin@example.com
30 2 * * * /usr/local/sbin/mysql-backup.sh

Because the script writes its own log and only prints to stdout on error, cron will email you only when something goes wrong. If your server has no MTA, install mailx and configure a relay, or check the log with a monitoring agent.

Get the dumps off the box

A backup on the same disk as the data protects you from DROP TABLE, not from a dead server. I add a second cron line that pushes to another host:

0 4 * * * rsync -a --delete /var/backups/mysql/ backup@nas.internal:/volume1/backups/db1/

Set up an SSH key for the backup user with a restricted command= in authorized_keys on the receiving side.

Prove it works

Run the script by hand the first time and watch the log:

sudo /usr/local/sbin/mysql-backup.sh
tail -n 20 /var/log/mysql-backup.log
ls -lh /var/backups/mysql/

Then restore one dump into a scratch database. Everyone skips this, and I did too until a dump turned out to be empty when I needed it.

mysql -e "CREATE DATABASE restore_test"
zcat /var/backups/mysql/appdb-$(date +%F).sql.gz | mysql restore_test
mysql restore_test -e "SHOW TABLE STATUS" | head
mysql -e "DROP DATABASE restore_test"

Errors I've hit

mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'appdb' when using LOCK TABLES. Either LOCK TABLES was not granted, or you have MyISAM tables and --single-transaction alone is not enough. Grant it as shown in the user section.

mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) appears when --master-data is set but REPLICATION CLIENT is missing. Alternatively, drop --master-data=2 if binary logging is not enabled at all; in that case the error is Binlogging on server not active.

Warning: Using a password on the command line interface can be insecure means the defaults file is not being read. Check that --defaults-extra-file is the first argument; mysqldump ignores it if it comes after other options.

Disk fills up anyway. The find -mtime +7 line only removes files, so if the dumps grow, seven days may still be too many. Watch du -sh /var/backups/mysql for the first week and adjust RETENTION_DAYS.

One options file, one script, two cron lines. It won't give you point-in-time recovery on a busy database, but for most of my servers a tested nightly dump that leaves the building is enough.