Mastering Percona XtraBackup: Principles, Commands, and Best Practices

XtraBackup Overview

XtraBackup, released by Percona, is an open‑source hot‑backup tool for MySQL that can perform fast full and incremental backups with minimal impact on online reads and writes. It supports InnoDB, XtraDB, and MyISAM engines.

1. Operation Principles

Permissions

XtraBackup must connect to the MySQL server and operate on the datadir directory.

Two types of users are required with appropriate privileges:

grant reload, lock tables, replication client, super on *.* to 'backup'@'localhost';

2. Terminology

redo – applies committed transactions to data files.

rollback – undoes uncommitted transactions to ensure consistency.

prepare – the process of applying redo and rollback to a backup created by innobackupex, bringing it to a consistent state.

3. Backup Principles

XtraBackup leverages InnoDB’s crash‑recovery feature, copying data files page by page without locking tables; the copied data may be inconsistent until crash‑recovery (redo/rollback) is applied.

InnoDB maintains a redo (transaction) log. XtraBackup copies data pages while a separate thread monitors the log and copies changed log pages to keep up with the circular log buffer.

During restoration, InnoDB checks data files and the transaction log, then performs crash recovery (redo and rollback). This can be triggered automatically on startup or manually via the --apply‑log (prepare) option.

Incremental backup workflow:

Because the logfile records every data modification, even if data files change during backup, consistency can be restored by parsing the copied logfile.

2. Program Description

XtraBackup consists of two main programs:

innobackupex – a Perl wrapper that calls xtrabackup and other utilities, providing a convenient command‑line interface for full, incremental, and remote backups. It supports MyISAM, InnoDB, and XtraDB.

xtrabackup – the core C program that performs the actual data copying.

Typical usage employs innobackupex: innobackupex [options] BACKUP-DIR Common options include: --host=, --port=, --user=, --password= – connection parameters. --apply-log – prepares a backup by applying the transaction log. --ibbackup= – selects the specific xtrabackup binary (e.g., xtrabackup_51, xtrabackup_55). --safe-slave-backup – pauses the slave until temporary tables are flushed. --no-lock – skips FLUSH TABLES WITH READ LOCK when no DDL or MyISAM updates are expected. --no-timestamp – writes backup directly to the specified directory without a timestamp subdirectory. --redo-only – performs only redo during prepare, useful for merging full and incremental backups. --slave-info – records master connection info for slave backups. --copy-back and --move-back – restore files to the original datadir. --databases=LIST – limit backup to specific databases/tables. --include=REGEXP – include tables matching a regular expression. --incremental, --incremental-basedir=, --incremental-dir=, --incremental-lsn= – control incremental backup behavior. --use-memory=2GB – memory limit for prepare phase. --rsync – use rsync for remote file transfer (cannot be combined with --stream). --stream=STREAMNAME – stream backup (tar or xbstream). --tmpdir=DIRECTORY – temporary directory for streamed logs. --parallel=NUMBER-OF-THREADS – number of copy threads. --compress and --compress-threads=N – compress data files. --defaults-file=MY.CNF – specify MySQL option file. --throttle=IOS – limit I/O per second. --export – enable export/import of tablespaces (Percona Server only). --extra-lsndir=DIRECTORY – extra directory for checkpoint files.

3. Backup Script Example

For a complete backup script, refer to the original knowledge‑base article.