Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

MariaDB physical backups with mariadb-backup: prepare, restore and version rules

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not a lab validation of the procedures. Check the stated versions and your own recovery requirements before making changes.

MariaDB offers two kinds of backup. A logical backup, made with mariadb-dump, exports tables as SQL statements. It supports selective recovery and migration between supported versions. A physical backup copies the server's data files directly and can reduce work for large restores, but performance depends on the workload, storage, compression and target. Measure both methods against the required recovery time.

For physical backups, MariaDB provides mariadb-backup, previously called mariabackup. It copies data files while the server keeps running. Its backups, though, are not usable as taken. They must be prepared, incremental backups must be applied in the right order, and the tool's version must match the server's.

This guide explains those stages and rules, because each one changes the backup on disk, and a mistake in any of them can leave a backup that will not restore.

Why it matters

A physical backup that has never been prepared and restored is an assumption, not a recovery plan. The MariaDB documentation is explicit that raw backup files are inconsistent, and that some problems, such as wrong encryption keys, only appear at the prepare stage. Teams that discover this during an outage lose hours they had planned to spend restoring.

During upgrades, retain the source version, the matching backup tools and a supported restore environment. A successful backup from before an upgrade should remain recoverable without relying on whatever package happens to be installed afterwards.

How it works

What the tool is

The mariadb-backup overview describes it as "an open source tool provided by MariaDB for performing physical online backups of InnoDB, Aria and MyISAM tables. For InnoDB, 'hot online' backups are possible. It was originally forked from Percona XtraBackup 2.3.8." It calls it "a production-quality, nearly non-blocking method for performing full backups on running systems."

Partial backups are possible but limited: they "require many steps and cannot be restored directly onto existing servers containing other data." For recovering a single table regularly, a logical backup alongside the physical one is often simpler.

Beyond the XtraBackup base, the overview lists support for data-at-rest encryption, InnoDB page compression, the MyRocks storage engine, Windows, and use as a Galera Cluster state transfer method.

Taking a full backup

A full backup uses --backup with --target-dir. The full backup page states: "When taking a full backup, the target directory must be empty or it must not exist." If the directory "exists and contains files, it raises an error and aborts."

On safety during the copy: "You can cancel the backup if you need to, as the backup process does not modify the database."

The account that runs the backup needs specific privileges. The overview gives the core grants as RELOAD, PROCESS, LOCK TABLES and BINLOG MONITOR, with more for some features, such as SUPER for MyRocks. If you use the backup history feature, the account needs INSERT, CREATE and ALTER on the history table. The full backup page notes a failure mode: without those, "the backup will complete the file copy process but will fail at the final step with an INSERT command denied error." Treat that exit status as a failed backup and fix the grants, rather than assuming the copied files are fine.

The overview also lists a known issue with large servers: if the tool "uses more file descriptors than the system is configured to allow", it "throws an error and aborts". The documented remedy is --open-files-limit or higher user limits.

About locks

The tool uses locking stages during a backup to capture non-InnoDB tables and binary log positions consistently (our reading of the options documentation). The options page describes --no-lock as providing "a consistent backup" only if the user ensures that "no DDL or non-transactional table updates occur during the backup", among other conditions, and notes that "the --no-lock option is not supported by MariaDB plc." It suggests that if locks fail because of replication traffic, --safe-slave-backup "may help the backup to succeed without resorting to --no-lock".

Our recommendation: do not use --no-lock to make a failing backup pass. Find out what is blocking the lock instead.

Why the backup must be prepared

The full backup page explains the core reason: "The data files that mariadb-backup creates in the target directory are not point-in-time consistent, given that the data files are copied at different times during the backup operation. If you try to restore from these files, InnoDB notices the inconsistencies and crashes to protect you from corruption".

Preparing fixes that. The options page says --prepare "readies the data so you can restore it to MariaDB Server". For a full backup, it applies the InnoDB redo log captured during the copy, so the files reflect one consistent moment.

Preparing writes to the backup directory. After it, the directory is a working data directory, not the raw backup it was. The documentation confirms: "Once a full backup is prepared, it is a fully functional MariaDB data directory." Our recommendation is to keep the raw backup unchanged and prepare a copy, so a failed or interrupted prepare can be retried from the original.

Encryption shows up late

The overview explains a consequence of the design: the tool "needs to apply InnoDB redo log records to make the data consistent when the backup is prepared", so it "does not perform many encryption or decryption operations when the backup is initially taken." As a result, "some encryption-related problems (such as using the wrong encryption keys) may not become apparent until the backup is prepared."

If your servers use data-at-rest encryption, a backup that has not been prepared with the real key configuration has not been tested. Preparing a copy of each backup soon after it is taken turns that late failure into an early one.

Incremental backups

The incremental backup page explains how they work: "InnoDB pages contain log sequence numbers, or LSN's." The tool "checks the most recent LSN for the backup against the LSN's contained in the database. It then updates any of the backup files that have fallen behind."

An incremental backup uses --incremental-basedir to point at the previous backup, and its target directory "must be empty". Each backup records its LSN range in a checkpoints file: mariadb_backup_checkpoints, which "in MariaDB 11.0 and earlier" was named xtrabackup_checkpoints.

When backups are streamed through compression or encryption tools, that checkpoints file ends up inside the stream and is "not directly accessible by default". The documented answer is --extra-lsndir, which writes an extra copy that the next incremental can reference. Without a separately retained checkpoints file, the next incremental needs another supported way to obtain the correct base LSN, such as extracting the metadata from the stream. The options reference also documents an explicit incremental LSN. Never guess that value or substitute an unrelated backup's metadata.

Preparing an incremental chain

Restoring from incrementals means building one prepared backup from the chain. The page describes the order: "First, prepare the base backup", which "brings the base full backup... into sync with the changes contained in the InnoDB redo log collected while the backup was taken." Then "apply the incremental changes to the base full backup", and "for each remaining incremental backup, repeat the last step".

Three practical points follow:

  • Order is fixed. Each incremental is applied in the order it was taken, oldest first.
  • The base changes in place. Each step updates the base backup directory. Work on a copy of the base so the original chain stays usable.
  • Every link is needed. A missing or damaged incremental in the middle of the chain stops the chain at that point.

Restoring

The full backup page gives two ways to put the prepared files in place: "The --copy-back option allows you to keep the original backup files. The --move-back option actually moves the backup files to the datadir, so the original backup files are lost." In both cases, the procedure says: "First, stop the MariaDB Server process. Then, ensure that datadir is empty."

Then comes a step that is easy to miss. The tool "preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database." So "after restoring a backup, you may need to adjust the owner of the data directory to match the user and group for the MariaDB Server, typically mysql for both."

The page adds that, once prepared, the backup can be restored "using any file copying tool, such as cp or rsync", as long as the server is stopped on the target.

Our recommendations: prefer --copy-back, so the prepared backup survives if the restore has to be repeated. Restore to a new server or a new data directory where possible, and never empty a production data directory before confirming the prepared backup is complete and you have a separate copy of what was there.

Version rules

The full backup page states the rule plainly, in the context of upgrades: "You must use a version of mariadb-backup that is compatible with the server version you are planning to upgrade from. For instance, when upgrading from MariaDB 10.6 to 10.11, you must use the 10.6 version of mariadb-backup." It continues: "When upgrading from MariaDB 10.11 to 11.4, you must use the 10.11 version". The prepare step uses "again using a compatible version of mariadb-backup".

In practice: the backup is taken and prepared with the tool that matches the source server's version, and restored onto a server of that version. Upgrading is a separate step after the restore. Keep the matching tool package available alongside the backups, because after an upgrade it may no longer be installed anywhere. (our reading and recommendation)

What to choose and why

These are our recommendations, based on the documentation above:

  • Use mariadb-backup for large databases where logical dumps take too long, and keep a logical backup for table-level recovery and portability.
  • Treat a backup as incomplete until it has been prepared on a copy. For encrypted servers, prepare with the production key configuration.
  • Check every exit status, including the history table insert.
  • For incrementals, keep chains short, use --extra-lsndir with streaming, record each chain's base, and apply increments to a copy in order.
  • Record the server version with each backup and keep the matching tool available.
  • Record binary log coordinates if you plan to roll forward after restore. The documentation's example backup directory includes an xtrabackup_binlog_info file, and the options page describes a different InnoDB coordinate file for certain no-lock cases. That is a limited documented mode, not a recommendation to bypass consistency locks.
  • Test restores to a separate server on a schedule, including ownership fixes and a server start.

Risks and limits

  • Preparing changes the backup. Prepare and apply increments to copies.
  • --move-back consumes the backup. Prefer --copy-back.
  • Emptying the data directory is destructive. Use an isolated restore destination and preserve the original data. Selecting and emptying a live data directory is not part of this article's planning guidance.
  • Version mismatches between tool and server can make backups unusable.
  • --no-lock can produce inconsistent backups unless its conditions are met.
  • Enterprise Backup differs from the community tool in some features; this article covers the community documentation.
  • Nothing here was run. Options are quoted from the documentation for planning.

Sources

This article was originally published on .