The idea: copy while it runs, then repair the copy with the redo
A physical hot backup copies the data files of a server that keeps running and writing. It does not stop the server and it does not read rows with SQL like mariadb-dump; it copies InnoDB's 16 KB pages straight from the files. That is fast, but while it copies, the server changes pages, so the copied pages come from different moments. mariadb-backup (MariaDB's fork of Percona XtraBackup, also called mariabackup) fixes this the way InnoDB fixes a crash: it copies the redo log too, and later replays it on the copy.
The page models one table t with six accounts of 100, one per page P1–P6 of t.ibd. A transfer moves 10 from one account to another, so the total is always 600. If a restored copy does not add up to 600, it is not consistent.
Why a plain copy is wrong
Copy the pages in order with cp while transfers run. A transfer that happens after P2 is copied but before P5 is copied is half in the copy: P5 shows the money gone, P2 does not show it arriving. Demo: cp data files only → wrong total ends with a restored total of 580.
| page | copied at | value in the copy | value at the end |
|---|---|---|---|
| P2 | before transfer P5 → P2 | 100 (stale) | 110 |
| P3 | before transfer P6 → P3 | 100 (stale) | 110 |
| P5 | after transfer P5 → P2 | 90 | 90 |
| P6 | after transfer P6 → P3 | 90 | 90 |
| total | 580 ✗ | 600 ✓ | |
The redo copier and the checkpoint LSN
Every change InnoDB makes to a page is first written as a redo record, tagged with its LSN (log sequence number, a byte position in the log). Each page stores the LSN of the last change it holds: its page LSN. Before it copies any page, mariadb-backup --backup reads checkpoint_lsn from the redo log header and starts a redo copier thread there. The thread keeps reading ib_logfile0 and appends every new record to xtrabackup_logfile in the target dir. Because it starts before the first page copy, every change made during the copy is in the backup: in the page, in the copied redo, or in both. See the InnoDB redo log for LSNs and checkpoints.
--prepare is crash recovery on the copy
mariadb-backup --prepare --target-dir=/backup starts InnoDB on the backup files, as if after a crash, and applies the copied redo in LSN order. A record is applied only where page LSN < record LSN: the page was copied before that change. Where the page LSN is already at least the record's LSN, the page was copied after the change and the record is skipped. Afterwards every page is as of the same point, last_lsn, and xtrabackup_checkpoints says backup_type = full-prepared. Demo: transfers during the copy, prepare fixes them: two records are applied (to P2 and P3), two are skipped, and the total goes from 580 back to 600. The same rule makes InnoDB crash recovery safe to repeat.
The end point: BACKUP STAGE
A backup needs one moment it represents. After the data files, mariadb-backup runs the BACKUP STAGE statements. FLUSH and BLOCK_DDL let it copy the non-InnoDB files (.frm, Aria, MyISAM) while no ALTER, DROP or RENAME can run. BLOCK_COMMIT stops all commits; the copier reads the redo up to the current LSN, and the binlog position goes into xtrabackup_binlog_info, so the backup can seed a replica or start a point-in-time restore. END releases the locks. This is the behaviour of MariaDB 10.11.8, 11.4.2 and later; earlier releases ran only START and BLOCK_COMMIT, and older ones FLUSH TABLES WITH READ LOCK. Writes after the end point are not in the backup: Demo: a write after the end point is not in the backup restores P1 = 100 although the server had moved 10 away from it.
When the redo log wraps
ib_logfile0 is circular, sized by innodb_log_file_size. The server only has to keep the log after its own checkpoint; it does not wait for a backup. If it writes more than the log holds between two reads of the copier, the records the copier still needs are overwritten. The backup cannot know those changes, so it stops with Was only able to copy log from … to …, not …; try increasing innodb_log_file_size. Demo: redo overwritten before it is copied writes ten records into a log of eight. The fix is a bigger redo log, or a backup at a quieter time.
Restore with --copy-back
A prepared backup is a complete datadir. To restore: stop the server, make sure the datadir is empty (--copy-back refuses one that is not), run mariadb-backup --copy-back --target-dir=/backup (or --move-back to move the files instead of copying), chown -R mysql:mysql the datadir, and start the server. It starts as of the end point. Transactions that were still open at the end point are rolled back from their undo logs, as in any crash recovery. Demo: idle server: backup, prepare, copy back walks the whole path.
| step | command | server |
|---|---|---|
| backup | mariadb-backup --backup --target-dir=/backup | running |
| prepare | mariadb-backup --prepare --target-dir=/backup | any (works on the copy) |
| restore | mariadb-backup --copy-back --target-dir=/backup | stopped, empty datadir |
MariaDB and MySQL
MySQL uses Percona XtraBackup, which works the same way (--backup, --prepare, --copy-back), and MySQL 8.0 also has the CLONE plugin, which copies pages and redo inside the server. A backup taken by one does not restore on the other: the redo and data file formats differ, so use mariadb-backup for MariaDB. See MySQL replication for what the saved binlog position is used for.
What the page leaves out
The buffer pool (the model writes changed pages at once, so the checkpoint follows the LSN); several data copier threads (--parallel); page checksum retries; the system tablespace, undo tablespaces and DDL during the backup (ddl.log, --lock-ddl-per-table); incremental backups (--incremental-basedir, which copies only pages newer than an LSN); compression, encryption and mbstream; Galera SST; open transactions at the end point; the real record and block format of the redo log. Here there are six pages, eight log slots and 25-byte records.
See where this page fits on the MySQL / InnoDB Internals mind map.