Physical Backup with mariadb-backup: Prepare, Copy-Back, Incrementals, and Encryption

Learning outcomes

ServiceHub's logical restore works, but the measured import time is too long for a multi-hundred-gigabyte production target. The team wants a faster same-platform recovery path. Copying /var/lib/mysql while MariaDB is running looks tempting, yet live InnoDB files are not a self-consistent frozen image. Pages and redo can be captured at different moments, and the server may be writing while files are copied.

mariadb-backup is MariaDB's physical backup utility. It coordinates with a running server, copies database files and records recovery metadata. A raw backup is intentionally not treated as ready-to-start data. The prepare phase applies crash-recovery logic and, for incremental chains, merges delta pages into the base. Only then should a controlled copy-back populate an empty restore data directory.

1. Physical backup is not “copy the data directory”

InnoDB uses buffer-pool pages, redo logs, undo records and background flushing. At any instant, the filesystem contains a state that may require crash recovery. A naïve recursive file copy can mix pages observed at incompatible moments and omit coordination needed for non-InnoDB files or metadata.

2. Verify versions, engines and the backup account first

mariadb -Nse "SELECT VERSION();"mariadb-backup --versionmariadb -e "SHOW ENGINES;"mariadb -e "SHOW VARIABLES LIKE 'datadir';"

If the server reports 12.3.2, use the corresponding 12.3.2 backup package/tool for this lab. A tool starting successfully against another release is not proof that its output is restorable. Storage engines matter too: mariadb-backup is designed around supported MariaDB physical formats, with engine-specific behavior and additional requirements for some engines such as MyRocks.

Create a dedicated local backup account on the disposable server:

CREATE USER IF NOT EXISTS 'mariadb_backup'@'localhost'  IDENTIFIED BY 'replace-with-lab-secret';GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR  ON *.* TO 'mariadb_backup'@'localhost';SHOW GRANTS FOR 'mariadb_backup'@'localhost';

Optional flags change privilege requirements. For example, current documentation notes additional monitoring privileges for certain replica/Galera metadata options and CONNECTION ADMIN for some long-query-kill behavior. Least privilege means matching the actual command, not memorizing one grant list forever.

3. Create independent source data for this physical-backup lesson

This lesson does not depend on Lesson 1 having been run. Create a tiny InnoDB dataset so full and incremental backup validation has a known marker.

DROP DATABASE IF EXISTS servicehub_physical_lab;CREATE DATABASE servicehub_physical_lab;CREATE TABLE servicehub_physical_lab.tickets (  ticket_id BIGINT PRIMARY KEY,  status VARCHAR(20) NOT NULL,  note VARCHAR(120) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_physical_lab.tickets VALUES (5001,'open','full-backup marker'), (5002,'assigned','full-backup marker');SELECT * FROM servicehub_physical_lab.tickets ORDER BY ticket_id;

4. Take a full backup and inspect what was produced

On Linux/package installations, the OS user running mariadb-backup needs filesystem permission to read the MariaDB files and write the target directory. In a disposable lab, using an administrative OS account is acceptable; production should narrow this boundary.

sudo mkdir -p /var/mariadb/backups/full-001sudo mariadb-backup --backup \  --target-dir=/var/mariadb/backups/full-001 \  --user=mariadb_backup \  --passwordsudo ls -lah /var/mariadb/backups/full-001sudo cat /var/mariadb/backups/full-001/xtrabackup_checkpointssudo cat /var/mariadb/backups/full-001/xtrabackup_info | head

If your build does not prompt safely with --password, use a protected option file rather than a command-line value. The exact metadata files vary by version/features, but xtrabackup_checkpoints exposes log sequence number (LSN) boundaries and xtrabackup_info records provenance. When binary-log metadata is captured, files such as xtrabackup_binlog_info can bridge the physical backup to PITR.

5. Prepare the full backup, then prove it can start

sudo mariadb-backup --prepare \  --target-dir=/var/mariadb/backups/full-001sudo cat /var/mariadb/backups/full-001/xtrabackup_checkpoints

The prepare phase performs recovery work against the backup copy. Run it with the same backup-tool version used to capture the backup. Preparation success is necessary but still not sufficient: the restore data directory, OS ownership, server startup and data-level validation remain untested.

For a destructive copy-back experiment, use a disposable second MariaDB instance or volume. Stop the target, preserve/remove its old data directory intentionally, make sure the target directory is empty, then copy back:

sudo systemctl stop mariadb-restore# Verify this is the disposable target datadir before removing anything.sudo find /var/lib/mysql-restore -mindepth 1 -maxdepth 1 -print# After independent verification/snapshot, empty the disposable target.sudo rm -rf /var/lib/mysql-restore/*sudo mariadb-backup --copy-back \  --target-dir=/var/mariadb/backups/full-001 \  --datadir=/var/lib/mysql-restoresudo chown -R mysql:mysql /var/lib/mysql-restoresudo systemctl start mariadb-restore

Service names and data-directory flags vary by package/platform. Do not paste these commands into a production host. On Windows, use a separate MariaDB service/data directory and its service account ACLs rather than Unix chown. In containers, use separate named volumes; MariaDB's official container documentation demonstrates backup/prepare/copy-back with isolated volumes.

6. Build an incremental chain and follow the LSNs

An InnoDB log sequence number (LSN) increases as redo is generated. Incremental backup compares page LSNs with the previous backup boundary and stores changed pages/deltas. The chain is ordered: base → inc1 → inc2. Losing or corrupting the middle increment can invalidate every later increment that depends on it.

sudo mariadb-backup --backup \  --target-dir=/var/mariadb/backups/inc-001 \  --incremental-basedir=/var/mariadb/backups/full-001 \  --user=mariadb_backup --password# Generate a small, known lab change before the second increment.mariadb -e "UPDATE servicehub_physical_lab.tickets SET status='closed', note='incremental marker' WHERE ticket_id=5001;"sudo mariadb-backup --backup \  --target-dir=/var/mariadb/backups/inc-002 \  --incremental-basedir=/var/mariadb/backups/inc-001 \  --user=mariadb_backup --passwordfor d in full-001 inc-001 inc-002; do  echo "=== $d ==="  sudo cat /var/mariadb/backups/$d/xtrabackup_checkpointsdone

Before applying deltas, make a separate working copy of the base if you need to preserve the original artifact. Then merge each increment in order:

sudo mariadb-backup --prepare \  --target-dir=/var/mariadb/backups/full-001sudo mariadb-backup --prepare \  --target-dir=/var/mariadb/backups/full-001 \  --incremental-dir=/var/mariadb/backups/inc-001sudo mariadb-backup --prepare \  --target-dir=/var/mariadb/backups/full-001 \  --incremental-dir=/var/mariadb/backups/inc-002

Current documentation notes that older --apply-log-only patterns are no longer needed/supported in current releases. This is exactly why a course should teach the mechanism and verify current syntax rather than perpetuating historical recipes.

7. Compression and encryption add dependencies to the restore path

Physical backups are large and highly sensitive. mariadb-backup supports streaming workflows and version-specific encryption options; external compression/encryption can also wrap streams. Every additional transform creates a recovery dependency: decompressor, decryptor, key, plugin and metadata must still exist during a disaster.

mkdir -p /var/mariadb/backups/meta-full-002sudo mariadb-backup --backup \  --stream=mbstream \  --extra-lsndir=/var/mariadb/backups/meta-full-002 \  --user=mariadb_backup --password \  | gzip > /var/mariadb/backups/full-002.mbstream.gz

For encryption, first inspect mariadb-backup --help on the exact build. If you use built-in encryption, preserve the required key-management material and plugin/library version. If you use an external free tool such as GnuPG or age, capture its version and key identifier. A backup whose decryption key is stored only on the failed database host is operationally unrecoverable.

8. Failure diagnosis: “backup completed” but restore fails

Typical causes include version mismatch, incomplete incremental chain, missing encryption key/plugin, unprepared files, nonempty target datadir, wrong ownership, insufficient disk space, or a backup that already contains physical corruption. Diagnose from evidence rather than retrying arbitrary flags.

mariadb-backup --versionmariadbd --versioncat /var/mariadb/backups/full-001/xtrabackup_checkpointscat /var/mariadb/backups/full-001/xtrabackup_info | head -40find /var/lib/mysql-restore -maxdepth 1 -ls# Also inspect the target MariaDB error log for startup/recovery messages.

Do not use --force-non-empty-directories as a reflex to bypass a safety failure. Understand why the directory is nonempty and whether those files belong to a previous server. Recovery tools are intentionally conservative around data-directory replacement.

9. Production judgment, verification and cleanup

Physical backup is attractive when same-platform restore speed matters and the storage engines/tool versions are supported. It is less portable than logical SQL and can preserve physical corruption. Pair it with logical/object-level recovery paths, integrity checks and periodic clean-server restore drills.

After restoring the prepared full+incremental chain into the disposable target, verify servicehub_physical_lab.tickets: ticket 5001 should carry the incremental marker. That database-level observation proves the increment reached the restored state; it still does not prove every application invariant, so add domain checks in production. Drop the disposable schema/account only after the restore drill is complete.

Lesson 3 adds the second half of recoverability: binary logs. A prepared full backup restores the past; retained transaction logs determine how far forward you can safely recover.