Scheduling Full and Incremental Backups with XtraBackup
My mysqldump nightly stopped being practical once one of our databases got past about 50 GB. The dump took hours, the restore took longer, and a long logical backup on a busy InnoDB server holds a read view open and bloats the undo log. Percona XtraBackup copies the InnoDB files physically while the server runs, and its incremental mode copies only pages that changed. A weekly full plus nightly incrementals gives me fast backups, fast restores and a small footprint.
This is the schedule I use with XtraBackup 2.4 on MySQL 5.7 and CentOS 7. The same commands work on Ubuntu, Percona Server 5.7 and MariaDB 10.1/10.2 (for MariaDB 10.3 use Mariabackup, since the InnoDB file format diverged). Everything should be in InnoDB (MyISAM gets copied too, but under a lock), and the destination needs room for one full plus a week of incrementals, roughly 1.5 times the data directory.
Install from the Percona repository:
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install -y percona-xtrabackup-24 qpress
A backup user
XtraBackup needs to read the data files as the OS user (run it as root or the mysql user) and a MySQL account to coordinate with the server:
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'xb-strong-password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.* TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;
Store the credentials in /root/.xtrabackup.cnf with mode 600:
[client]
user = xtrabackup
password = xb-strong-password
socket = /var/lib/mysql/mysql.sock
First full backup, by hand
sudo mkdir -p /backup/mysql
sudo xtrabackup --defaults-extra-file=/root/.xtrabackup.cnf \
--backup --target-dir=/backup/mysql/full-$(date +%F) \
--compress --compress-threads=4 --parallel=4
The last lines of output should read completed OK!. The target directory contains xtrabackup_checkpoints, which records the LSN range covered:
cat /backup/mysql/full-2018-12-27/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8837129441
last_lsn = 8837129441
to_lsn is what the next incremental will start from.
The script
Save as /usr/local/sbin/xb-backup.sh, chmod 700. It takes one argument, full or inc, and always bases an incremental on the most recent backup of either kind.
#!/bin/bash
set -euo pipefail
BASE=/backup/mysql
CNF=/root/.xtrabackup.cnf
TODAY=$(date +%F)
MODE=${1:-inc}
if [ "$MODE" = "full" ]; then
TARGET="$BASE/full-$TODAY"
xtrabackup --defaults-extra-file=$CNF --backup --target-dir="$TARGET" \
--compress --compress-threads=4 --parallel=4
# keep only the two most recent full chains
ls -1d $BASE/full-* | sort | head -n -2 | while read old; do
rm -rf "$old" "$BASE"/inc-*-of-"${old##*/}"
done
else
LAST=$(ls -1d $BASE/full-* $BASE/inc-* 2>/dev/null | sort -t- -k2,4 | tail -1)
FULL=$(ls -1d $BASE/full-* | sort | tail -1)
TARGET="$BASE/inc-$TODAY-of-${FULL##*/}"
xtrabackup --defaults-extra-file=$CNF --backup --target-dir="$TARGET" \
--incremental-basedir="$LAST" --compress --compress-threads=4 --parallel=4
fi
echo "$(date '+%F %T') $MODE OK $TARGET" >> /var/log/xb-backup.log
Naming incrementals after their full base keeps a chain together and makes cleanup predictable. I got this wrong the first time and ended up with orphaned incrementals eating disk.
Cron
sudo crontab -e
MAILTO=dba@example.com
0 1 * * 0 /usr/local/sbin/xb-backup.sh full
0 1 * * 1-6 /usr/local/sbin/xb-backup.sh inc
Full on Sunday at 01:00, incrementals Monday through Saturday. Add an rsync job an hour later to copy /backup/mysql off the host.
The restore procedure (learn it before you need it)
Restore is where XtraBackup incrementals differ from a dump. You must prepare the chain in order, applying redo logs but leaving the base open for more increments until the last one:
cd /backup/mysql
for d in full-2018-12-23 inc-*-of-full-2018-12-23; do
xtrabackup --decompress --remove-original --target-dir=$d
done
xtrabackup --prepare --apply-log-only --target-dir=full-2018-12-23
for d in inc-2018-12-24-of-full-2018-12-23 inc-2018-12-25-of-full-2018-12-23; do
xtrabackup --prepare --apply-log-only --target-dir=full-2018-12-23 --incremental-dir=$d
done
xtrabackup --prepare --target-dir=full-2018-12-23 # final prepare, without --apply-log-only
Then stop MySQL, empty the data directory, copy back and fix ownership:
sudo systemctl stop mysqld
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --copy-back --target-dir=/backup/mysql/full-2018-12-23
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysqld
Checking the chain
Look at the log and the checkpoints of the newest incremental. from_lsn must equal the previous backup's to_lsn:
tail -3 /var/log/xb-backup.log
grep lsn /backup/mysql/inc-*/xtrabackup_checkpoints
Once a month, run the full restore procedure into a scratch VM or a second data directory on a different port (--datadir=/var/lib/mysql-restore --port=3307) and run mysqlcheck --all-databases against it. A backup that has never been restored is a hypothesis.
Problems
Error: failed to open the target directory or xtrabackup: error: cannot mkdir. The script ran as a user that cannot write to /backup/mysql; run from root's crontab, not a user's.
InnoDB: The log sequence number ... in the ibdata files does not match after a restore. You skipped --apply-log-only on an intermediate step or applied incrementals out of order. Start the prepare sequence again from a fresh copy of the full.
xtrabackup: Error: incremental backup base LSN ... is greater than the current LSN means the base directory you pointed at is from a different server or a later backup. Check the sort in the script picked the right LAST.
Backup runs for hours on a write-heavy server with log scanned up to messages far behind. Redo is being generated faster than XtraBackup copies it. Raise --parallel, or add --ftwrl-wait-timeout=120 --ftwrl-wait-threshold=60 so the final lock waits for long queries instead of blocking them.
Put the restore rehearsal on the calendar. The backups are the easy part.