DB-유

Using Percona XtraBackup for Hot MySQL Backups

· mysql, backup, xtrabackup

mysqldump is fine up to a few tens of gigabytes. After that the dump takes hours, the restore takes longer, and --single-transaction on a busy server holds a long read view that makes the undo logs grow. I moved my bigger servers to Percona XtraBackup: it copies the InnoDB files while the server runs, takes only a brief lock for non-transactional tables, and restoring is basically copying files back. It's free and works with Oracle MySQL, Percona Server and MariaDB.

This is my setup with XtraBackup 2.4 on MySQL 5.7 on CentOS 7. MySQL 8.0 needs the separate XtraBackup 8.0 series (new data dictionary and redo log format); the commands are the same. Have free space at least the size of the data directory, ideally on another volume.

Install and create a backup user

sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo yum install -y percona-xtrabackup-24
xtrabackup --version

The backup account needs RELOAD, LOCK TABLES, PROCESS and REPLICATION CLIENT (plus BACKUP_ADMIN on 8.0):

CREATE USER 'xbackup'@'localhost' IDENTIFIED BY 'backup-password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xbackup'@'localhost';
GRANT SELECT ON performance_schema.* TO 'xbackup'@'localhost';

Put the credentials in /root/.my.cnf under a dedicated group so scripts do not carry passwords:

[xtrabackup]
user=xbackup
password=backup-password

Full backup

sudo xtrabackup --backup --target-dir=/backup/mysql/full-$(date +%F)

XtraBackup copies the .ibd files while tailing the redo log; the last lines of output must read completed OK!. At the end it takes a short FLUSH TABLES WITH READ LOCK to copy MyISAM tables and record the binlog position, which lands in xtrabackup_binlog_info. On 5.7 with only InnoDB tables the lock is a fraction of a second.

For compression and streaming to another host in one go:

sudo xtrabackup --backup --stream=xbstream --compress --parallel=4 --target-dir=/tmp \
  | ssh backup01 "xbstream -x -C /backup/db01/full-$(date +%F)"

Incrementals

Incrementals copy only pages changed since the previous backup, using the LSN recorded in xtrabackup_checkpoints:

sudo xtrabackup --backup --target-dir=/backup/mysql/inc1 --incremental-basedir=/backup/mysql/full-2018-12-07
sudo xtrabackup --backup --target-dir=/backup/mysql/inc2 --incremental-basedir=/backup/mysql/inc1

A weekly full plus daily incrementals is my usual schedule. Keep the chain short; a long one makes the prepare step slow and fragile.

Prepare

A raw backup is not consistent; the redo log has to be applied. For a full backup alone:

sudo xtrabackup --prepare --target-dir=/backup/mysql/full-2018-12-07

For a chain, prepare the full with --apply-log-only, apply each incremental in order with --apply-log-only, and run the final prepare without it:

sudo xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full-2018-12-07
sudo xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full-2018-12-07 --incremental-dir=/backup/mysql/inc1
sudo xtrabackup --prepare --target-dir=/backup/mysql/full-2018-12-07 --incremental-dir=/backup/mysql/inc2

Forgetting --apply-log-only on an intermediate step rolls back uncommitted transactions and makes the later incrementals unusable. I do the prepare on the backup host right after each backup so that a failure is noticed the same day, not on restore day.

Restore

The data directory must be empty and the server stopped:

sudo systemctl stop mysqld
sudo mv /var/lib/mysql /var/lib/mysql.broken
sudo mkdir /var/lib/mysql
sudo xtrabackup --copy-back --target-dir=/backup/mysql/full-2018-12-07
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysqld

--move-back instead of --copy-back is faster when the backup is on the same filesystem and you do not need to keep it. For point-in-time recovery, read the position in xtrabackup_binlog_info and replay binlogs from there with mysqlbinlog --start-position.

Trust, but check

After every prepare:

cat /backup/mysql/full-2018-12-07/xtrabackup_checkpoints
# backup_type = full-prepared

After a restore, run a quick consistency pass and compare a few counts against production:

CHECK TABLE shop.orders QUICK;
SELECT COUNT(*) FROM shop.orders;

And test-restore to a staging server regularly; a backup that has never been restored is an assumption, not a backup.

Errors

  • xtrabackup: error: log block numbers mismatch or The log was not applied to the intended LSN – Wrong incremental order or a missing --apply-log-only. Start the prepare chain again from a fresh copy of the full backup.
  • Error: failed to execute query FLUSH TABLES WITH READ LOCK: Access denied; you need (at least one of) the RELOAD privilege(s) – The backup account lacks RELOAD.
  • InnoDB: Operating system error number 13 in a file operation during --copy-back – Ownership; run chown -R mysql:mysql /var/lib/mysql and check SELinux with restorecon -Rv /var/lib/mysql.
  • xtrabackup: Error: Please set parameter 'datadir' – XtraBackup reads datadir from my.cnf; if the server uses a non-standard config, pass --defaults-file=/etc/my.cnf.
  • Backup runs for hours and completed OK! never appears, with log scanned up to lines repeating – A long-running write transaction is preventing the checkpoint; check SHOW ENGINE INNODB STATUS and, if needed, --lock-ddl-per-table on 5.7 with heavy DDL.

Pair it with binlog archiving and you get point-in-time recovery. That's what I want at three in the morning.