MariaDB Backup and Restore

A backup that has never been restored is not a backup. This skill covers the two production-grade backup paths in MariaDB (logical via mariadb-dump, physical via mariadb-backup), incremental chains, point-in-time recovery via binlog replay, single-table restore via the transportable-tablespace flow, encrypted streaming backups, verification routines, and the RPO/RTO trade-offs that drive the strategy choice. Read this before designing any backup plan for a non-trivial database.

Quick Reference

• ALWAYS use mariadb-backup (renamed from mariabackup in 10.5+) for production : it is the hot, InnoDB-aware physical backup tool. Reserve mariadb-dump for small databases, test fixtures, or cross-version migrations.
• ALWAYS run mariadb-backup --prepare on a physical backup BEFORE restoring. The raw --backup output is in an inconsistent crash-recovery state ; --prepare applies pending redo and rolls back uncommitted transactions.
• ALWAYS pass --single-transaction to mariadb-dump on InnoDB workloads. Without it the default behaviour is LOCK TABLES per database, which blocks all writes for the dump duration. --single-transaction is ignored for MyISAM and Aria tables (they are NOT transactional), which still get locked.
• ALWAYS pair --routines, --triggers, and --events with mariadb-dump. Defaults DO NOT include them ; restored databases silently lose stored procedures, triggers, and scheduled events.
• ALWAYS keep continuous binlog archival if RPO must be tighter than your backup interval. A full backup at 02:00 with no binlogs gives 24-hour worst-case RPO ; the same backup plus archived binlogs gives sub-minute RPO.
• ALWAYS use --apply-log-only on the base and all intermediate incremental prepares, and the plain --prepare (no --apply-log-only) on the final increment. Wrong order or wrong flag corrupts the chain.
• ALWAYS verify every backup type at least monthly by restoring to a sandbox host and running mariadb-check --all-databases plus a row-count sanity query against canonical tables.
• NEVER run mariadb-dump on a multi-GB production database without --single-transaction : table locks will cascade into application timeouts.
• NEVER restore an incremental chain in the wrong order. Order is : prepare base with --apply-log-only, prepare each increment in order with --apply-log-only, prepare the final increment WITHOUT --apply-log-only, then --copy-back.
• NEVER use the deprecated --compress option in mariadb-backup : it relies on the unmaintained QuickLZ algorithm. Use --stream=xbstream | zstd -T0 instead.
• NEVER take a Galera SST-donor backup on a node that is currently donating an SST to a joiner : it can corrupt the backup mid-transfer. Run mariadb-backup on a non-donor node, prefer a dedicated backup node (wsrep_node_name=backup-1 + pc.weight=0 so it never wins elections).
• NEVER store backups on the same physical host as the database : a disk-loss event takes both with it. Always replicate or rsync to a remote target before considering the backup retained.
• The mysqldump, mysqlbinlog, and mariabackup binaries are 10.5+ compatibility symlinks for mariadb-dump, mariadb-binlog, and mariadb-backup. Old names work but scripts must update before MariaDB 11.0+ packaging changes drop the symlinks in some distributions.