MariaDB Enterprise Backup

Regular and reliable backups are essential to successful recovery of mission critical applications. MariaDB Enterprise Server backup and restore operations are performed using MariaDB Enterprise Backup, an enterprise-build of MariaDB Backup.

MariaDB Enterprise Backup is compatible with MariaDB Enterprise Server 10.2, 10.3, and 10.4.

Forming a Backup Strategy

The strategy applied when implementing data backups depends on business needs.

Data backup strategy depends on business needs. Business needs can be evaluated by performing a data inventory, determining data recovery objectives, considering the replication environment, and considering encryption requirements. Also critical is a backup storage strategy and testing backup and recovery procedures.

Data Inventory

Backup strategy requirements flow from the understanding you build by performing a data inventory. A data inventory is established by asking questions such as:

  1. What data is housed in the databases?

  2. What business purpose does this data serve?

  3. How long does the data needed to be retained in order to meet this business purpose?

  4. Are there any legal or regulatory requirements, which would limit the length of data retention?

Recovery Objectives

Data recovery requirements are often defined in terms of Recovery Point Objective (RPO) and Recovery Time Objective (RTO). RTO and RPO are considered in the context of the data identified in the data inventory.

Recovery Point Objective (RPO) defines the maximum amount of data a business is willing to lose. For example, a business can define a RPO of 24 hours.

Recovery Time Objective (RTO) defines how quickly a business needs to restore service in the event of a fault. For example, a business can define a RTO of 8 hours.

Backup strategy plays a substantial role in achieving RPO and RTO.

Achieving RPO

RPO depends on completion of backups, which provide a viable recovery point. Since RPO is measured at backup completion, not backup initiation, backup jobs must be scheduled at an interval smaller than the RPO.

Techniques for achieving RPO include:

  • Frequent incremental backups and less frequent full backups.

  • Performing backups in conjunction with replication and clustering to eliminate impact on production workloads, allowing a higher backup frequency.

  • Automated monitoring of backup status.

  • Automated testing of backups.

Achieving RTO

The RTO window typically commences at the point when a decision is made by the business to recover from backups, not at the start of an incident.

Techniques for achieving RTO include:

  • Leveraging information produced during incident response, which can reduce the set of data to restore from backups, or identify specific data validation requirements dependent on the nature of the incident.

  • Having fast access to backup data. Performance requirements of backup infrastructure should be understood for both backup and restoration workloads.

  • Using delayed replication, either within the same data center or to a different data center, can provide shorter path to recovery. This is particularly true when coupled with robust application monitoring, which allows intervention before the window of delay elapses.

  • Applying written and tested recovery procedures, which designate the systems and commands to be used during recovery.

  • Performing drills and exercises that periodically test recovery procedures to confirm readiness.

Replication Considerations

MariaDB Enterprise Server supports several implementations of replication, which accurately duplicates data from one Server to one or more other Servers. The use of a dedicated replica as a source for backups can minimize workload impact.

MariaDB Enterprise Cluster implements virtually synchronous replication, where each Server instance contains a replica of all of the data for the Cluster. Backups can be performed from any node in the Cluster.

Encryption Considerations

MariaDB Enterprise Server supports encryption on disk (data-at-rest encryption) and on the network (data-in-transit encryption).

MariaDB Enterprise Backup copies tablespaces from disk. When data-at-rest encryption is enabled, backups contain encrypted data.

MariaDB Enterprise Backup supports TLS encryption for communications with MariaDB Enterprise Server. To enable TLS encryption, set TLS options from the command-line or in the configuration file:

# mariabackup --backup \
      --target-dir=/data/backups/full \
      --user=mariabackup \
      --password=mbu_passwd \
      --ssl-ca=/etc/my.cnf.d/certs/ca.pem \
      --ssl-cert=/etc/my.cnf.d/certs/client-cert.pem \

Backup Storage Considerations

How backups are stored can impact backup viability. Backup storage also presents separate risks. These risks need to be carefully considered:

  • Backup data should always be stored separately from the system being backed up, and separate from the system used for recovery.

  • Backup data should be subject to equal or more controls than data in production databases. For example, backup data should generally be encrypted even where a decision has bee made that a production database will not use data-at-rest encryption.

  • Business requirements may define a need for offsite storage of backups as a means of guaranteeing delivery on RPO. In these cases you should also consider onsite storage of backups as a means of guaranteeing delivery on RTO.

  • Retention requirements and the run-rate of new data production can aid in capacity planning.

Backup Testing

Testing has been identified as a Critical Success Factor for the successful operation of data systems.

Backups should be tested. Recovery using backups and recovery procedures should be tested.

Storage Engines and Backup Types

MariaDB Enterprise Backup creates a file-level backup of data from the MariaDB Enterprise Server data directory. This backup includes temporal data, and the encrypted and unencrypted tablespaces of supported storage engines (e.g., InnoDB, MyRocks, Aria).

MariaDB Enterprise Server implements:

  • Full backups, which contain all data in the database.

  • Incremental backups, which contain modifications since the last backup.

  • Partial backups, which contain a subset of the tables in the database.

Backup support is specific to storage engines. All supported storage engines enable full backup. The InnoDB storage engine additionally supports incremental backup.


MariaDB Enterprise Backup does not support backups of MariaDB ColumnStore. Backup of MariaDB ColumnStore can be performed using MariaDB ColumnStore Tools. Backup of data ingested to MariaDB ColumnStore can also occur pre-ingestion, such as in the case of HTAP where backup could occur of transactional data in MariaDB Enterprise Server, and restore of data to MariaDB ColumnStore would then occur through reprocessing.

Non-blocking Backups

A feature of MariaDB Enterprise Backup and MariaDB Enterprise Server, non-blocking backups minimize workload impact during backups. When MariaDB Enterprise Backup connects to MariaDB Enterprise Server, staging operations are initiated to protect data during read.

Non-blocking backup functionality differs from historical backup functionality in the following ways:

  • MariaDB Enterprise Backup and MariaDB Enterprise Server include enterprise-only optimizations to backup staging, including DDL statement tracking, which reduces lock-time during backups.

  • MariaDB Community Server 10.4 backup staging will block writes, log tables, and statistics.

  • Older MariaDB Community Server releases used FLUSH TABLES WITH READ LOCK, which closed open tables and only allowed tables to be reopened with a read lock during the duration of backups.

Understanding Recovery

MariaDB Enterprise Backup creates complete or incremental backups of MariaDB Enterprise Server data, and is also used to restore data from backups produced using MariaDB Enterprise Backup.

Preparing Backups for Recovery

Full backups produced using MariaDB Enterprise Server are not initially point-in-time consistent, and an attempt to restore from a raw full backup will cause InnoDB to crash to protect the data.

Incremental backups produced using MariaDB Enterprise Backup contain only the changes since the last backup and cannot be used standalone to perform a restore.

To restore from a backup, you first need to prepare the backup for point-in-time consistency using the --prepare command:

  • Running the --prepare command on a full backup synchronizes the tablespaces, ensuring that they are point-in-time consistent and ready for use in recovery.

  • Running the --prepare command on an incremental backup synchronizes the tablespaces and also applies the updated data into the previous full backup, making it a complete backup ready for use in recovery.

  • Running the --prepare command on data that is to be used for a partial restore (when restoring only one or more selected tables) requires that you also use the --export option to create the necessary .cfg files to use in recovery.

Restoring from Backups Overview

When MariaDB Enterprise Backup restores from a backup, it copies or moves the backup files into the MariaDB Enterprise Server data directory, as defined by the datadir system variable.

Empty or Move Data Directory

For MariaDB Enterprise Backup to safely restore data from full and incremental backups, the data directory must be empty. One way to achieve this is to move the data directory aside to a unique directory name:

  1. Make sure that the Server is stopped.

  2. Move the data directory to a unique name (e.g., /var/lib/mysql-2020-01-01) OR remove the old data directory (depending on how much space you have available).

  3. Create a new (empty) data directory (e.g., mkdir /var/lib/mysql).

  4. Run MariaDB Enterprise Backup to restore the databases into that directory.

  5. Change the ownership of all the restored files to the correct system user (e.g., chown -R mysql:mysql /var/lib/mysql).

  6. Start MariaDB Enterprise Server, which now uses the restored data directory.

  7. When ready, and if you have not already done so, delete the old data directory to free disk space.

Creating the Backup User

When MariaDB Enterprise Backup performs a backup operation, it not only copies files from the data directory but also connects to the running MariaDB Enterprise Server.

This connection to MariaDB Enterprise Server is used to manage locks and backup staging that prevent the Server from writing to a file while being read for a backup.

MariaDB Enterprise Backup establishes this connection based on the user credentials specified with the --user and --password options when performing a backup.

MariaDB Enterprise Backup requires this user to have the RELOAD, PROCESS, LOCK TABLES, and REPLICATION CLIENT privileges.

It is recommended that a dedicated user be created and authorized to perform backups:

CREATE USER 'mariabackup'@'localhost'
IDENTIFIED BY 'mbu_passwd';

ON *.*
TO 'mariabackup'@'localhost';

In the above example, MariaDB Enterprise Backup would run on the local system that runs MariaDB Enterprise Server. Where backups may be run against a remote server, the user authentication and authorization should be adjusted.

While MariaDB Enterprise Backup requires a user for backup operations, no user is required for restore operations since restores occur while MariaDB Enterprise Server is not running.

Full Backup and Restore

Full backups performed with MariaDB Enterprise Backup contain all table data present in the database.

When performing a full backup, MariaDB Enterprise Backup makes a file-level copy of the MariaDB Enterprise Server data directory. This backup omits log data such as the binary logs (binlog), error logs, general query logs, and slow query logs.

Performing Full Backups

When you perform a full backup, MariaDB Enterprise Backup writes the backup to the --target-dir path. The directory must be empty or non-existent and the system user must have permission to write to that directory. The --user and --password options correspond to a user on MariaDB Enterprise Server, allowing MariaDB Enterprise Backup to initiate staging, locking, and unlocking:

# mariabackup --backup \
      --target-dir=/data/backups/full \
      --user=mariabackup \

Subsequent to the above example, the backup is now available in the designated --target-dir path.

Preparing a Full Backup for Recovery

A raw full backup is not point-in-time consistent and must be prepared before it can be used for a restore. This preparation can occur at any point after the backup is created and before the backup is used for a restore.

To prepare the backup data, run MariaDB Enterprise Backup again with the --prepare command option:

# mariabackup --prepare --target-dir=/data/backups/full

Restoring from Full Backups

Once a full backup has been prepared to be point-in-time consistent, MariaDB Enterprise Backup is used to copy backup data to the MariaDB Enterprise Server data directory.

To restore from a full backup:

  1. Stop the MariaDB Enterprise Server

  2. Empty the data directory

  3. Restore from the "full" directory using the --copy-back option:

# mariabackup --copy-back --target-dir=/data/backups/full

MariaDB Enterprise Backup writes to the data directory as the current user, which can be changed using sudo. To confirm that restored files are properly owned by the user that runs MariaDB Enterprise Server, run a command like this (adapted for the correct user/group):

# chown -R mysql:mysql /var/lib/mysql

Once this is done, start MariaDB Enterprise Server. When the Server starts, it works from the restored data directory.

Incremental Backup and Restore

Full backups of large data-sets can be time-consuming and resource-intensive. MariaDB Enterprise Backup supports the use of incremental backups to minimize this impact.

While full backups are resource-intensive at time of backup, the resource burden around incremental backups occurs when preparing for restore. First, the full backup is prepared for restore, then each incremental backup is applied.

Performing Incremental Backups

When you perform an incremental backup, MariaDB Enterprise Backup compares a previous full or incremental backup to what it finds on MariaDB Enterprise Server. It then creates a new backup containing the incremental changes.

Incremental backup is supported for InnoDB tables. Tables using other storage engines receive full backups even during incremental backup operations.

To increment a full backup, use the --incremental-basedir option to indicate the path to the full backup and the --target-dir option to indicate where you want to write the incremental backup:

# mariabackup --backup \
      --incremental-basedir=/data/backups/full \
      --target-dir=/data/backups/inc1 \
      --user=mariabackup \

In this example, MariaDB Enterprise Backup reads the /data/backups/full directory, and MariaDB Enterprise Server then creates an incremental backup in the /data/backups/inc1 directory.

Preparing an Incremental Backup

An incremental backup must be applied to a prepared full backup before it can be used in a restore operation. If you have multiple full backups to choose from, pick the nearest full backup prior to the incremental backup that you want to restore. You may also want to back up your full-backup directory, as it will be modified by the updates in the incremental data.

If your full backup directory is not yet prepared, run this to make it consistent:

# mariabackup --prepare --target-dir=/data/backups/full

Then, using the prepared full backup, apply the first incremental backup's data to the full backup in an incremental preparation step:

# mariabackup --prepare \
      --target-dir=/data/backups/full \

Once the incremental backup has been applied to the full backup, the full backup directory contains the changes from the incremental backup (that is, the inc1/ directory). Feel free to remove inc1/ to save disk space.

Restoring from Incremental Backups

Once you have prepared the full backup directory with all the incremental changes you need (as described above), stop the MariaDB Enterprise Server, empty its data directory, and restore from the original full backup directory using the --copy-back option:

# mariabackup --copy-back --target-dir=/data/backups/full

MariaDB Enterprise Backup writes files into the data directory using either the current user or root (in the case of a sudo operation), which may be different from the system user that runs the database. Run the following to recursively update the ownership of the restored files and directories:

# chown -R mysql:mysql /var/lib/mysql

Then, start MariaDB Enterprise Server. When the Server starts, it works from the restored data directory.

Partial Backup and Restore

In a partial backup, MariaDB Enterprise Backup copies a specified subset of tablespaces from the MariaDB Enterprise Server data directory. Partial backups are useful in establishing a higher frequency of backups on specific data, at the expense of increased recovery complexity. In selecting tablespaces for a partial backup, please consider referential integrity.

Performing a Partial Backup

Command-line options can be used to narrow the set of databases or tables to be included within a backup:




List of databases to include


List of databases to omit from the backup


Path to file listing the databases to include


List of tables to include


List of tables to exclude


Path to file listing the tables to include

For example, you may wish to produce a partial backup, which excludes a specific database:

# mariabackup --backup \
      --target-dir=/data/backups/part \
      --user=mariabackup \
      --password=mbu_passwd \

Partial backups can also be incremental:

# mariabackup --backup \
      --incremental-basedir=/data/backups/part \
      --target-dir=/data/backups/part_inc1 \
      --user=mariabackup \
      --password=mbu_passwd  \

Preparing a Backup Before a Partial Restore

As with full and incremental backups, partial backups are not point-in-time consistent. A partial backup must be prepared before it can be used for recovery.

A partial restore can be performed from a full backup or partial backup.

The preparation step for either partial or full backup restoration requires the use of transportable tablespaces for InnoDB. As such, each prepare operation requires the --export option:

# mariabackup --prepare --export --target-dir=/data/backups/part

When using a partial incremental backup for restore, the incremental data must be applied to its prior partial backup data before its data is complete. If performing partial incremental backups, run the prepare statement again to apply the incremental changes onto the partial backup that served as the base.

# mariabackup --prepare --export \
      --target-dir=/data/backups/part \

Performing a Partial Restore

Unlike full and incremental backups, you cannot restore partial backups directly using MariaDB Enterprise Backup. Further, as a partial backup does not contain a complete data directory, you cannot restore MariaDB Enterprise Server to a startable state solely with a partial backup.

To restore from a partial backup, you need to prepare a table on the MariaDB Enterprise Server, then manually copy the files into the data directory. The particular method varies depending on whether you are restoring a partitioned or non-partitioned table.

As partial restores are performed while the server is running, not stopped, care should be taken to prevent production workloads during restore activity.


You can also use data from a full backup in a partial restore operation if you have prepared the data using the --export option as described above.

Restoring Non-partitioned Tables

To restore a non-partitioned table from a backup, first create a new table on MariaDB Enterprise Server to receive the restored data. It should match the specifications of the table you're restoring.

Be extra careful if the backup data is from a server with a different version than the restore server, as some differences (such as a differing ROW_FORMAT) can cause an unexpected result.

  1. Create an empty table for the data being restored:

CREATE TABLE test.address_book (
   name VARCHAR(255),
   email VARCHAR(255));
  1. Modify the table to discard the tablespace:

  1. You can copy (or move) the files for the table from the backup to the data directory:

# cp /data/backups/part_inc1/test/address_book.* /var/lib/mysql/test
  1. Use a wildcard to include both the .ibd and .cfg files. Then, change the owner to the system user running MariaDB Enterprise Server:

# chown mysql:mysql /var/lib/mysql/test/address_book.*
  1. Lastly, import the new tablespace:


MariaDB Enterprise Server looks in the data directory for the tablespace you copied in, then imports it for use. If the table is encrypted, it also looks for the encryption key with the relevant key ID that the table data specifies.

  1. Repeat this step for every table you wish to restore.

Restoring Partitioned Tables

Restoring a partitioned table from a backup requires a few extra steps compared to restoring a non-partitioned table.

To restore a partitioned table from a backup, first create a new table on MariaDB Enterprise Server to receive the restored data. It should match the specifications of the table you're restoring, including the partition specification.

Be extra careful if the backup data is from a server with a different version than the restore server, as some differences (such as a differing ROW_FORMAT) can cause an unexpected result.

  1. Create an empty table for the data being restored:

CREATE TABLE test.students (
   name VARCHAR(255),
   email VARCHAR(255),
   graduating_year YEAR)
PARTITION BY RANGE (graduating_year) (
  1. Then create a second empty table matching the column specification, but without partitions. This will be your working table:

CREATE TABLE test.students_work AS
SELECT * FROM test.students WHERE NULL;
  1. For each partition you want to restore, discard the working table's tablespace:

  1. Then, copy the table files from the backup, using the new name:

# cp /data/backups/part_inc1/test/students.ibd /var/lib/mysql/test/students_work.ibd
# cp /data/backups/part_inc1/test/students.cfg /var/lib/mysql/test/students_work.cfg
  1. Change the owner to that of the user running MariaDB Enterprise Server:

# chown mysql:mysql /var/lib/mysql/test/students_work.*
  1. Import the copied tablespace:

  1. Lastly, exchange the partition, copying the tablespace from the working table into the partition file for the target table:

ALTER TABLE test.students EXCHANGE PARTITION p0 WITH TABLE test.students_work;
  1. Repeat the above process for each partition until you have them all exchanged into the target table. Then delete the working table, as it's no longer necessary:

DROP TABLE test.students_work;

This restores a partitioned table.

Point-in-Time Recoveries

Recovering from a backup restores the data directory at a specific point-in-time, but it does not restore the binary log. In a point-in-time recovery, you begin by restoring the data directory from a full or incremental backup, then use the mysqlbinlog utility to recover the binary log data to a specific point in time.

  1. First, prepare the backup as you normally would for a full or incremental backup:

# mariabackup --prepare --target-dir=/data/backups/full
  1. When MariaDB Enterprise Backup runs on a MariaDB Enterprise Server where binary logs is enabled, it stores binary log information in the xtrabackup_binlog_info file. Consult this file to find the name of the binary log position to use. In the following example, the log position is 321.

# cat /data/backups/full/xtraback_binlog_info

mariadb-node4.00001     321
  1. Update the configuration file to use a new data directory.

  1. Using MariaDB Enterprise Backup, restore from the backup to the new data directory:

# mariabackup --copy-back --target-dir=/data/backups/full
  1. Then change the owner to the MariaDB Enterprise Server system user:

# chown -R mysql:mysql /var/lib/mysql_new
  1. Start MariaDB Enterprise Server.

  2. Using the binary log file in the old data directory, the start position in the xtrabackup_binlog_info file, the date and time you want to restore to, and the mysqlbinlog utility to create an SQL file with the binary log changes:

$ mysqlbinlog --start-position=321 \
      --stop-datetime="2019-06-28 12:00:00" \
      /var/lib/mysql/mariadb-node4.00001 \
      > mariadb-binlog.sql
  1. Lastly, run the binary log SQL to restore the databases:

$ mysql -u root -p < mariadb-binlog.sql

Backup Optimizations

Backup and restore implementations can help overcome specific technical challenges that would otherwise pose a barrier to meeting business requirements.

Each of these practices represents a trade-off. Understand risks before implementing any of these practices.

Scheduling of Restore Preparation

Technical challenge: restore time

Trade-off: increased ongoing overhead for backup processing

Backup data can be prepared for restore any time after it is produced and before it is used for restore. To expedite recovery, incremental backups can be pre-applied to the prior full backup to enable faster recovery. This may be done at the expense of recovery points, or at the expense of storage by maintaining copies of unmerged full and incremental backup directories.

Moving Restored Data

Technical challenge: disk space limitations

Trade-off: modification of backup directory contents

Suggested method for moving restored data is to use --copy-back as this method provides added safety. Where you might instead optimize for disk space savings, system resources, and time you may choose to instead use MariaDB Enterprise Backup's --move-back option. Speed benefits are only present when backup files are on the same disk partition as the destination data directory.

The --move-back option will result in the removal of all data files from the backup directory, so it is best to use this option only when you have an additional copy of your backup data in another location.

To restore from a backup by moving files, use the --move-back option:

# mariabackup --move-back --target-dir=/data/backups/full


Technical challenge: CPU bottlenecks

Trade-off: Increased workload during backups

MariaDB Enterprise Backup is a multi-threaded application that by default runs on a single thread. In cases where you have a host with multiple cores available, you can specify the number of threads you want it to use for parallel data file transfers using the --parallel option:

# mariabackup --backup \
      --target-dir=/data/backups/full \
      --user=mariabackup \
      --password=mbu_passwd \

Incrementing an Incremental Backup

Technical challenge: Backup resource overhead, backup duration

Trade-off: Increased restore complexity, restore process duration

Under normal operation an incremental backup is taken against an existing full backup. This allows you to further shorten the amount of time MariaDB Enterprise Backup locks MariaDB Enterprise Server while copying tablespaces. You can then apply the changes in the increment to the full backup with a --prepare operation at leisure, without disrupting database operations.

MariaDB Enterprise Backup also supports incrementing from an incremental backup. In this operation, the --incremental-basedir option points not to the full backup directory but rather to the previous incremental backup.

# mariabackup --backup \
      --incremental-basedir=/data/backups/inc1 \
      --target-dir=/data/backups/inc2 \
      --user=mariabackup \

In preparing a backup to restore the data directory, apply the chain of incremental backups to the full backup in order. That is, first inc1/, then inc2/, and so on:

# mariabackup --prepare \
      --target-dir=/data/backups/full \

# mariabackup --prepare \
      --target-dir=/data/backups/full \

Continue to apply all the incremental changes until you have applied all available to the backup. Then restore as usual:

# mariabackup --copy-back --target-dir=/data/backups/full
# chown -R mysql:mysql /var/lib/mysql

Start MariaDB Enterprise Server on the restored data directory.

Storage Snapshots

Technical challenge: Backup resource overhead, backup duration

Trade-off: Limited to platforms with volume-level snapshots, may require crash recovery

While MariaDB Enterprise Backups produces file-level backups, users on storage solutions may prefer to instead perform volume-level snapshots to minimize resource impact. This storage capability exists with some SAN, NAS, and volume manager platforms.

Snapshots occur point-in-time, so no preparation step is needed to ensure data is internally consistent. Snapshots occur while tablespaces are open, and a restored snapshot may need to undergo crash recovery.

Just as traditional full, incremental, and partial backups should be tested, so too should recovery from snapshots be tested on an ongoing basis.

Snapshotting with MariaDB Enterprise Server

MariaDB Enterprise Server includes advanced backup functionality to reduce the impact of backup operations:

  1. Connect with a client and issue a BACKUP STAGE START statement and then a BACKUP STAGE BLOCK_COMMIT statement.

  2. Take the snapshot.

  3. Issue a BACKUP STAGE END statement.

  4. Once the backup has been completed, remove all files which begin with the #sql prefix. These files are generated when ALTER TABLE occurs during a staged backup.

  5. Retrieve, copy, or store the snapshot as is typical for your storage platform and as per business requirements to make the backup durable. This may require mounting the snapshot in some manner.

Snapshotting with MariaDB Community Server

It is recommended to briefly prevent writes while snapshotting. Specific commands vary depending on storage platform, business requirements, and setup, but a general approach is to:

  1. Connect with a client and issue a FLUSH TABLES WITH READ LOCK statement, leaving the client connected.

  2. Take the snapshot.

  3. Issue an UNLOCK TABLES statement, to remove the read lock.

  4. Retrieve, copy, or store the snapshot as is typical for your storage platform and as per business requirements to make the backup durable. This may require mounting the snapshot in some manner.