This article briefly discusses the main ways to backup MariaDB. For detailed descriptions and syntax, see the individual pages. More detail is in the process of being added.

Logical vs Physical Backups

Logical backups consist of the SQL statements necessary to restore the data, such as CREATE DATABASE, CREATE TABLE and INSERT.

Physical backups are performed by copying the individual data files or directories.

The main differences are as follows:

• logical backups are more flexible, as the data can be restored on other hardware configurations, MariaDB versions or even on another DBMS, while physical backups cannot be imported on significantly different hardware, a different DBMS, or potentially even a different MariaDB version.

• logical backups can be performed at the level of database and table, while physical databases are the level of directories and files. In the MyISAM and InnoDB storage engines, each table has an equivalent set of files.

• logical backups are larger in size than the equivalent physical backup.

• logical backups takes more time to both backup and restore than the equivalent physical backup.

• log files and configuration files are not part of a logical backup

Backup Tools

mariadb-backup

The mariadb-backup program is a fork of Percona XtraBackup with added support for compression and data-at-rest encryption.

mariadb-dump

mariadb-dump (previously mysqldump) performs a logical backup. It is the most flexible way to perform a backup and restore, and a good choice when the data size is relatively small.

For large datasets, the backup file can be large, and the restore time lengthy.

mariadb-dump dumps the data into SQL format (it can also dump into other formats, such as CSV or XML) which can then easily be imported into another database. The data can be imported into other versions of MariaDB, MySQL, or even another DBMS entirely, assuming there are no version or DBMS-specific statements in the dump.

mariadb-dump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). Procedures and functions are however also part of the system tables (for example mysql.proc).

InnoDB Logical Backups

InnoDB uses the buffer pool, which stores data and indexes from its tables in memory. This buffer is very important for performance. If InnoDB data doesn't fit the memory, it is important that the buffer contains the most frequently accessed data. However, last accessed data is candidate for insertion into the buffer pool. If not properly configured, when a table scan happens, InnoDB may copy the whole contents of a table into the buffer pool. The problem with logical backups is that they always imply full table scans.

An easy way to avoid this is by increasing the value of the innodb_old_blocks_time system variable. It represents the number of milliseconds that must pass before a recently accessed page can be put into the "new" sublist in the buffer pool. Data which is accessed only once should remain in the "old" sublist. This means that they will soon be evicted from the buffer pool. Since during the backup process the "old" sublist is likely to store data that is not useful, one could also consider resizing it by changing the value of the innodb_old_blocks_pct system variable.

It is also possible to explicitly dump the buffer pool on disk before starting a logical backup, and restore it after the process. This will undo any negative change to the buffer pool which happens during the backup. To dump the buffer pool, the innodb_buffer_pool_dump_now system variable can be set to ON. To restore it, the innodb_buffer_pool_load_now system variable can be set to ON.

mariadb-dump Examples

Backing up a single database

mariadb-dump db_name > backup-file.sql

Restoring or loading the database

mariadb db_name < backup-file.sql

See the mariadb-dump page for detailed syntax and examples.

mariadb-hotcopy

mariadb-hotcopy performs a physical backup, and works only for backing up MyISAM and ARCHIVE tables. It can only be run on the same machine as the location of the database directories.

mariadb-hotcopy Examples

mariadb-hotcopy db_name [/path/to/new_directory]
mariadb-hotcopy db_name_1 ... db_name_n /path/to/new_directory

Percona XtraBackup

Percona XtraBackup is not supported in MariaDB. mariadb-backup is the recommended backup method to use instead of Percona XtraBackup. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

Percona XtraBackup is a tool for performing fast, hot backups. It was designed specifically for XtraDB/InnoDB databases, but can be used with any storage engine (although not with encryption and compression). It is not included with MariaDB.

Filesystem Snapshots

Some filesystems, like Veritas, support snapshots. During the snapshot, the table must be locked. The proper steps to obtain a snapshot are:

• From the mariadb client, execute FLUSH TABLES WITH READ LOCK. The client must remain open.

• From a shell, execute mount vxfs snapshot

• The client can execute UNLOCK TABLES.

• Copy the snapshot files.

• From a shell, unmount the snapshot with umount snapshot.

LVM

Widely-used physical backup method, using a Perl script as a wrapper. See http://www.lenzg.net/mylvmbackup/ for more information.

Percona TokuBackup

For details, see:

• TokuDB Hot Backup – Part 1

• TokuDB Hot Backup – Part 2

• TokuDB Hot Backup Now a MySQL Plugin

dbForge Studio for MySQL

Besides the system utilities, it is possible to use third-party GUI tools to perform backup and restore operations. In this context, it is worth mentioning dbForge Studio for MySQL, a feature-rich database IDE that is fully compatible with MariaDB and delivers extensive backup functionality.

The backup and restore module of the Studio allows precise configuration and management of full and partial backups up to particular database objects. The feature of scheduling regular backups offers specific settings to handle errors and keep a log of them. Additionally, settings and configurations can be saved for later reuse.

These operations are wizard-aided allowing users to set up all tasks in a visual mode.

See Also

• Streaming MariaDB backups in the cloud (mariadb.com blog)

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

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:

mariadb-backup --move-back --target-dir=/data/backups/full

Multi-threading

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:

mariadb-backup --backup \
      --target-dir=/data/backups/full \
      --user=mariadb-backup \
      --password=mbu_passwd \
      --parallel=12

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.

mariadb-backup --backup \
      --incremental-basedir=/data/backups/inc1 \
      --target-dir=/data/backups/inc2 \
      --user=mariadb-backup \
      --password=mbu_passwd

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:

mariadb-backup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc1

mariadb-backup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc2

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

mariadb-backup --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.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

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:

mariadb-backup --backup \
      --target-dir=/data/backups/full \
      --user=mariadb-backup \
      --password=mbu_passwd \
      --ssl-ca=/etc/my.cnf.d/certs/ca.pem \
      --ssl-cert=/etc/my.cnf.d/certs/client-cert.pem \
      --ssl-key=/etc/my.cnf.d/certs/client-key.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.

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

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.

• Storage Engines and Backup Types

• Non-blocking Backups

• Understanding Recovery

• Creating the Backup User

• Full Backup and Restore

• Incremental Backup and Restore

• Partial Backup and Restore

• Point-in-Time Recoveries

Storage Engines and Backup Types

MariaDB Backup creates a file-level backup of data from the MariaDB Community 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.

Note: 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 in MariaDB Enterprise Server includes enterprise-only optimizations to backup staging, including DDL statement tracking, which reduces lock-time during backups.

• MariaDB Backup in MariaDB Community Server 10.4 and later 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.

Restore Requires Empty Data Directory

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.

For MariaDB 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 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 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 that prevent the Server from writing to a file while being read for a backup.

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

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

CREATE USER 'mariadb-backup'@'localhost'
IDENTIFIED BY 'mbu_passwd';

GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR
ON *.*
TO 'mariadb-backup'@'localhost';

CREATE USER 'mariadb-backup'@'localhost'
IDENTIFIED BY 'mbu_passwd';

GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT
ON *.*
TO 'mariadb-backup'@'localhost';

Full Backup and Restore

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

When performing a full backup, MariaDB 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 Backup writes the backup to the --target-dir path. The directory must be empty or non-existent and the operating system user account must have permission to write to that directory. A database user account is required to perform the backup.

The version of mariadb-backup or mariadb-backup should be the same version as the MariaDB Enterprise Server version. When the version does not match the server version, errors can sometimes occur, or the backup can sometimes be unusable.

To create a backup, execute mariadb-backup or mariadb-backup with the --backup option, and provide the database user account credentials using the --user and --password options:

sudo mariadb-backup --backup \
      --target-dir=/data/backups/full \
      --user=mariadb-backup \
      --password=mbu_passwd

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. The backup can be prepared any time after the backup is created and before the backup is restored. However, MariaDB recommends preparing a backup immediately after taking the backup to ensure that the backup is consistent.

The backup should be prepared with the same version of MariaDB Backup that was used to create the backup.

To prepare the backup, execute mariadb-backup or mariadb-backup with the --prepare option:

sudo mariadb-backup --prepare \
   --use-memory=34359738368 \
   --target-dir=/data/backups/full

For best performance, the --use-memory option should be set to the server's innodb_buffer_pool_size value.

Restoring from Full Backups

Once a full backup has been prepared to be point-in-time consistent, MariaDB 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:

mariadb-backup --copy-back --target-dir=/data/backups/full

MariaDB 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:

sudo systemctl start mariadb

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 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 Backup compares a previous full or incremental backup to what it finds on MariaDB Community 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:

mariadb-backup --backup \
      --incremental-basedir=/data/backups/full \
      --target-dir=/data/backups/inc1 \
      --user=mariadb-backup \
      --password=mbu_passwd

In this example, MariaDB 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:

mariadb-backup --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:

mariadb-backup --prepare \
      --target-dir=/data/backups/full \
      --incremental-dir=/data/backups/inc1

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 Community Server, Empty its data directory, and restore from the original full backup directory using the --copy-back option:

mariadb-backup --copy-back --target-dir=/data/backups/full

MariaDB 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 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:

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

mariadb-backup --backup \
      --target-dir=/data/backups/part \
      --user=mariadb-backup \
      --password=mbu_passwd \
      --database-exclude=test

Partial backups can also be incremental:

mariadb-backup --backup \
      --incremental-basedir=/data/backups/part \
      --target-dir=/data/backups/part_inc1 \
      --user=mariadb-backup \
      --password=mbu_passwd  \
      --database-exclude=test

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:

mariadb-backup --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.

mariadb-backup --prepare --export \
      --target-dir=/data/backups/part \
      --incremental-dir=/data/backups/part_inc1

Performing a Partial Restore

Unlike full and incremental backups, you cannot restore partial backups directly using MariaDB Backup. Further, as a partial backup does not contain a complete data directory, you cannot restore MariaDB Community 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 Community Server, then manually copy the files into the data directory.

The details of the restore procedure depend on the characteristics of the table:

• Partial Restore Non-partitioned Tables

• Partial Restore Partitioned Tables

• Partial Restore of Tables with Full-Text Indexes

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

Note: 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.

Partial Restore Non-partitioned Tables

To restore a non-partitioned table from a backup, first create a new table on MariaDB Community 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 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   email VARCHAR(255));

1. Modify the table to discard the tablespace:

ALTER TABLE test.address_book DISCARD 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 Community Server:

# chown mysql:mysql /var/lib/mysql/test/address_book.*

1. Lastly, import the new tablespace:

ALTER TABLE test.address_book IMPORT TABLESPACE;

MariaDB Community 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.

Partial Restore 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 Community 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 (
   id INT PRIMARY KEY AUTO_INCREMENT
   name VARCHAR(255),
   email VARCHAR(255),
   graduating_year YEAR)
PARTITION BY RANGE (graduating_year) (
   PARTITION p9 VALUES LESS THAN 2019
   PARTITION p1 VALUES LESS THAN MAXVALUE
);

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:

ALTER TABLE test.students_work DISCARD 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 Community Server:

# chown mysql:mysql /var/lib/mysql/test/students_work.*

1. Import the copied tablespace:

ALTER TABLE test.students_work IMPORT 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.

Partial Restore of Tables with Full-Text Indexes

When restoring a table with a full-text search (FTS) index, InnoDB may throw a schema mismatch error.

In this case, to restore the table, it is recommended to:

• Remove the corresponding .cfg file.

• Restore data to a table without any secondary indexes including FTS.

• Add the necessary secondary indexes to the restored table.

For example, to restore table t1 with FTS index from database db1:

1. In the MariaDB shell, drop the table you are going to restore:

DROP TABLE IF EXISTS db1.t1;

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

CREATE TABLE db1.t1(f1 CHAR(10)) ENGINE=INNODB;

1. Modify the table to discard the tablespace:

ALTER TABLE db1.t1 DISCARD TABLESPACE;

1. In the operating system shell, copy the table files from the backup to the data directory of the corresponding database:

$ sudo cp /data/backups/part/db1/t1.* /var/lib/mysql/db1

1. Remove the .cfg file from the data directory:

$ sudo rm /var/lib/mysql/db1/t1.cfg

1. Change the owner of the newly copied files to the system user running MariaDB Community Server:

$ sudo chown mysql:mysql /var/lib/mysql/db1/t1.*

1. In the MariaDB shell, import the copied tablespace:

ALTER TABLE db1.t1 IMPORT TABLESPACE;

1. Verify that the data has been successfully restored:

SELECT * FROM db1.t1;

+--------+
| f1     |
+--------+
| ABC123 |
+--------+

1. Add the necessary secondary indexes:

ALTER TABLE db1.t1 FORCE, ADD FULLTEXT INDEX f_idx(f1);

1. The table is now fully restored:

SHOW CREATE TABLE db1.t1\G

*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` char(10) DEFAULT NULL,
  FULLTEXT KEY `f_idx` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

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:

mariadb-backup --prepare --target-dir=/data/backups/full

1. When MariaDB Backup runs on a MariaDB Community 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.

[mysqld]
datadir=/var/lib/mysql_new

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

mariadb-backup --copy-back --target-dir=/data/backups/full

1. Then change the owner to the MariaDB Community Server system user:

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

1. Start MariaDB Community Server:

sudo systemctl start mariadb

1. 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

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

mariadb-backup is 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 is available on Linux and Windows.

This tool provides a production-quality, nearly non-blocking method for performing full backups on running systems. While partial backups with mariadb-backup are technically possible, they require many steps and cannot be restored directly onto existing servers containing other data.

Supported Features

mariadb-backup supports all of the main features of Percona XtraBackup 2.3.8, plus:

• Backup/Restore of tables using Data-at-Rest Encryption.

• Backup/Restore of tables using InnoDB Page Compression.

• mariadb-backup SST method with Galera Cluster.

• Microsoft Windows support.

• Backup/Restore of tables using the MyRocks storage engine. See Files Backed up by mariadb-backup: MyRocks Data Files for more information.

Supported Features in MariaDB Enterprise Backup

Differences Compared to Percona XtraBackup

• Percona XtraBackup requires more locks to run than MariaDB. In addition, any running ALTER TABLE will block Percona XtraBackup until it completes.

• Percona XtraBackup copies its InnoDB redo log files to the file xtrabackup_logfile, while mariadb-backup uses the file ib_logfile0.

• Percona XtraBackup's libgcrypt-based encryption of backups is not supported by mariadb-backup.

• There is no symbolic link from mariadb-backup to innobackupex, as there is for xtrabackup. Instead, mariadb-backup has the --innobackupex command-line option to enable innobackupex-compatible options.

• The --compact and --rebuild_indexes options are not supported.

• Support for --stream=tar was removed from mariadb-backup in MariaDB 10.1.24.

• The xbstream utility has been renamed to mbstream. However, to select this output format when creating a backup, mariadb-backup's --stream option still expects the xbstream value.

• mariadb-backup does not support lockless binlog.

Difference in Versioning Schemes

Each Percona XtraBackup release has two version numbers--the Percona XtraBackup version number and the version number of the MySQL Server release that it is based on. For example:

xtrabackup version 2.2.8 based on MySQL server 5.6.22

Each mariadb-backup release only has one version number, and it is the same as the version number of the MariaDB Server release that it is based on. For example:

mariadb-backup based on MariaDB server 10.2.15-MariaDB Linux (x86_64)

See Compatibility of mariadb-backup Releases with MariaDB Server Releases for more information on mariadb-backup versions.

Installing mariadb-backup

Installing on Linux

The mariadb-backup executable is included in binary tarballs on Linux.

Installing with a Package Manager

mariadb-backup can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from one of the MariaDB repositories.

You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the MariaDB Package Repository setup script.

You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the MariaDB Repository Configuration Tool.

Installing with yum/dnf

On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using yum or dnf. Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf. For example:

sudo yum install MariaDB-backup

Installing with apt-get

On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant DEB package from MariaDB's repository using apt-get. For example:

sudo apt-get install mariadb-backup

Installing with zypper

On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using zypper. For example:

sudo zypper install MariaDB-backup

Installing on Windows

The mariadb-backup executable is included in MSI and ZIP packages on Windows.

When using the Windows MSI installer, mariadb-backup can be installed by selecting Backup utilities:

Using mariadb-backup

The command to use mariadb-backup and the general syntax is:

mariadb-backup <options>

For in-depth explanations on how to use mariadb-backup, see:

• Full Backup and Restore with mariadb-backup

• Incremental Backup and Restore with mariadb-backup

• Partial Backup and Restore with mariadb-backup

• Restoring Individual Tables and Partitions with mariadb-backup

• Setting up a Replica with mariadb-backup

• Using Encryption and Compression Tools With mariadb-backup

Options

Options supported by mariadb-backup can be found on the mariadb-backup Options page.

Option Files

In addition to reading options from the command-line, mariadb-backup can also read options from option files.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

Server Option Groups

mariadb-backup reads server options from the following option groups from option files:

Client Option Groups

mariadb-backup reads client options from the following option groups from option files:

Authentication and Privileges

mariadb-backup needs to authenticate with the database server when it performs a backup operation (i.e. when the --backup option is specified). For most use cases, the user account that performs the backup needs to have the following global privileges on the database server.

CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';

CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariadb-backup'@'localhost';

If your database server is also using the MyRocks storage engine, then the user account that performs the backup will also need the SUPER global privilege. This is because mariadb-backup creates a checkpoint of this data by setting the rocksdb_create_checkpoint system variable, which requires this privilege. See MDEV-20577 for more information.

CONNECTION ADMIN is also required where --kill-long-queries-timeout is greater than 0, and --no-lock isn't applied in order to KILL queries. Prior to 10.5 a SUPER privilege is required instead of CONNECTION ADMIN.

REPLICA MONITOR (or alias SLAVE MONITOR) is also required where --galera-info or --slave-info is specified.

To use the --history option, the backup user also needs to have the following privileges granted:

GRANT CREATE, INSERT ON mysql.mariadb_backup_history TO 'mariadb-backup'@'localhost';

Prior to MariaDB 10.11, the necessary permissions to use --history were:

GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariadb-backup'@'localhost';

If you're upgrading from an older version and you want to use the new default table without losing your backup history, you can move and rename the current table in this way:

RENAME TABLE PERCONA_SCHEMA.xtrabackup_history TO mysql.mariadb_backup_history;

The user account information can be specified with the --user and --password command-line options. For example:

mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

The user account information can also be specified in a supported client option group in an option file. For example:

[mariadb-backup]
user=mariadb-backup
password=mypassword

mariadb-backup does not need to authenticate with the database server when preparing or restoring a backup.

File System Permissions

mariadb-backup has to read MariaDB's files from the file system. Therefore, when you run mariadb-backup as a specific operating system user, you should ensure that user account has sufficient permissions to read those files.

If you are using Linux and if you installed MariaDB with a package manager, then MariaDB's files will probably be owned by the mysql user and the mysql group.

Using mariadb-backup with Data-at-Rest Encryption

mariadb-backup supports Data-at-Rest Encryption.

mariadb-backup will query the server to determine which key management and encryption plugin is being used, and then it will load that plugin itself, which means that mariadb-backup needs to be able to load the key management and encryption plugin's shared library.

mariadb-backup will also query the server to determine which encryption keys it needs to use.

In other words, mariadb-backup is able to figure out a lot of encryption-related information on its own, so normally one doesn't need to provide any extra options to backup or restore encrypted tables.

mariadb-backup backs up encrypted and unencrypted tables as they are on the original server. If a table is encrypted, then the table will remain encrypted in the backup. Similarly, if a table is unencrypted, then the table will remain unencrypted in the backup.

The primary reason that mariadb-backup needs to be able to encrypt and decrypt data is that it needs to apply InnoDB redo log records to make the data consistent when the backup is prepared. As a consequence, mariadb-backup does not perform many encryption or decryption operations when the backup is initially taken. MariaDB performs more encryption and decryption operations when the backup is prepared. This means that some encryption-related problems (such as using the wrong encryption keys) may not become apparent until the backup is prepared.

Using mariadb-backup for Galera SSTs

The mariadb-backup SST method uses the mariadb-backup utility for performing SSTs. See mariadb-backup SST method for more information.

Files Backed up by mariadb-backup

mariadb-backup backs up many different files in order to perform its backup operation. See Files Backed up by mariadb-backup for a list of these files.

Files Created by mariadb-backup

mariadb-backup creates several different types of files during the backup and prepare phases. See Files Created by mariadb-backup for a list of these files.

Binary logs

mariadb-backup can store the binary log position in the backup. See --binlog-info. This can be used for point-in-time recovery and to use the backup to setup a slave with the correct binlog position.

Known Issues

Unsupported Server Option Groups

Prior to MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13, mariadb-backup doesn't read server options from all option groups supported by the server. In those versions, it only looks for server options in the following server option groups:

Those versions do not read server options from the following option groups supported by the server:

See MDEV-18347 for more information.

No Default Datadir

Prior to MariaDB 10.1.36, MariaDB 10.2.18, and MariaDB 10.3.10, if you were performing a --copy-back operation, and if you did not explicitly specify a value for the --datadir option either on the command line or one of the supported server option groups in an option file, then mariadb-backup would not default to the server's default datadir. Instead, mariadb-backup would fail with an error. For example:

Error: datadir must be specified.

The solution is to explicitly specify a value for the --datadir option either on the command line or in one of the supported server option groups in an option file. For example:

[mysqld]
datadir=/var/lib/mysql

In MariaDB 10.1.36, MariaDB 10.2.18, and MariaDB 10.3.10 and later, mariadb-backup will default to the server's default datadir value.

See MDEV-12956 for more information.

Concurrent DDL and Backup Issues

Prior to MariaDB 10.2.19 and MariaDB 10.3.10, if concurrent DDL was executed while the backup was taken, then that could cause various kinds of problems to occur.

One example is that if DDL caused any tablespace IDs to change (such as TRUNCATE TABLE or RENAME TABLE), then that could cause the effected tables to be inconsistent in the backup. In this scenario, you might see errors about mismatched tablespace IDs when the backup is prepared.

For example, the errors might look like this:

2018-12-07 07:49:32 7f51b3184820  InnoDB: Error: table 'DB1/TAB_TEMP'
InnoDB: in InnoDB data dictionary has tablespace id 1355633,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name DB1/TAB_TEMP and id 1354713, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

Or they might look like this:

2018-07-12 21:24:14 139666981324672 [Note] InnoDB: Ignoring data file 'db1/tab1.ibd' with space ID 200485, since the redo log references db1/tab1.ibd with space ID 200484.

Some of the problems related to concurrent DDL are described below.

Problems solved by setting --lock-ddl-per-table (mariadb-backup command-line option added in MariaDB 10.2.9):

• If a table is dropped during the backup, then it might still exists after the backup is prepared.

• If a table exists when the backup starts, but it is dropped before the backup copies it, then the tablespace file can't be copied, so the backup would fail.

Problems solved by setting --innodb_log_optimize_ddl=OFF (MariaDB Server system variable added in MariaDB 10.2.17 and removed in 10.6.0):

• If the backup noticed concurrent DDL, then it might fail with

  Copy

  ALTER TABLE or OPTIMIZE TABLE was executed during backup

Problems solved by --innodb_safe_truncate=ON (MariaDB Server system variable in MariaDB 10.2.19 and removed in 10.3.0):

• If a table is created during the backup, then it might not exist in the backup after prepare.

• If a table is renamed during the backup after the tablespace file was copied, then the table may not exist after the backup is prepared.

• If a table is dropped and created under the same name during the backup after the tablespace file was copied, then the table will have the wrong tablespace ID when the backup is prepared.

Note that, with the removal of innodb_log_optimize_ddl and innodb_safe_truncate, the above problems were definitely solved.

Problems solved by other bug fixes:

• If --lock-ddl-per-table is used and if a table is concurrently being dropped or renamed, then mariadb-backup can fail to acquire the MDL lock.

These problems are only fixed in MariaDB 10.2 and later, so it is not recommended to execute concurrent DDL when using mariadb-backup with MariaDB 10.1.

See MDEV-13563, MDEV-13564, MDEV-16809, and MDEV-16791 for more information.

Manual Restore with Pre-existing InnoDB Redo Log files

Prior to MariaDB 10.2.10, mariadb-backup users could run into issues if they restored a backup by manually copying the files from the backup into the datadir while the directory still contained pre-existing InnoDB redo log files. The backup itself did not contain InnoDB redo log files with the traditional ib_logfileN file names, so the pre-existing log files would remain in the datadir. If the server were started with these pre-existing log files, then it could perform crash recovery with them, which could cause the database to become inconsistent or corrupt.

In these MariaDB versions, this problem could be avoided by not restoring the backup by manually copying the files and instead restoring the backup by using mariadb-backup and providing the --copy-back option, since mariadb-backup deletes pre-existing InnoDB redo log files from the datadir during the restore process.

In MariaDB 10.2.10 and later, mariadb-backup prevents this issue by creating an empty InnoDB redo log file called ib_logfile0 as part of the --prepare stage. That way, if the backup is manually restored, any pre-existing InnoDB redo log files would get overwritten by the empty one.

See MDEV-13311 for more information.

Too Many Open Files

If mariadb-backup uses more file descriptors than the system is configured to allow, then users can see errors like the following:

2019-02-12 09:48:38 7ffff7fdb820  InnoDB: Operating system error number 23 in a file operation.
InnoDB: Error number 23 means 'Too many open files in system'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
InnoDB: Error: could not open single-table tablespace file ./db1/tab1.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

Prior to MariaDB 10.1.39, MariaDB 10.2.24, and MariaDB 10.3.14, mariadb-backup would actually ignore the error and continue the backup. In some of those cases, mariadb-backup would even report a successful completion of the backup to the user. In later versions, mariadb-backup will properly throw an error and abort when this error is encountered. See MDEV-19060 for more information.

When this error is encountered, one solution is to explicitly specify a value for the --open-files-limit option either on the command line or in one of the supported server option groups in an option file. For example:

[mariadb-backup]
open_files_limit=65535

An alternative solution is to set the soft and hard limits for the user account that runs mariadb-backup by adding new limits to /etc/security/limits.conf. For example, if mariadb-backup is run by the mysql user, then you could add lines like the following:

mysql soft nofile 65535
mysql hard nofile 65535

After the system is rebooted, the above configuration should set new open file limits for the mysql user, and the user's ulimit output should look like the following:

ulimit -Sn
65535
ulimit -Hn
65535

Versions

See Also

• mariadb-dump/mysqldump

• How to backup with MariaDB (video)

• MariaDB point-in-time recovery (video)

• mariadb-backup and Restic (video)

• Percona Xtrabackup 2.3 documentation

_{This page is licensed: CC BY-SA / Gnu FDL}

List of mariadb-backup Options

--apply-log

Prepares an existing backup to restore to the MariaDB Server. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

Files that mariadb-backup generates during --backup operations in the target directory are not ready for use on the Server. Before you can restore the data to MariaDB, you first need to prepare the backup.

In the case of full backups, the files are not point in time consistent, since they were taken at different times. If you try to restore the database without first preparing the data, InnoDB rejects the new data as corrupt. Running mariadb-backup with the --prepare command readies the data so you can restore it to MariaDB Server. When working with incremental backups, you need to use the --prepare command and the --incremental-dir option to update the base backup with the deltas from an incremental backup.

mariadb-backup --innobackupex --apply-log

Once the backup is ready, you can use the --copy-back or the --move-back commands to restore the backup to the server.

--apply-log-only

If this option is used when preparing a backup, then only the redo log apply stage will be performed, and other stages of crash recovery will be ignored. This option is used with incremental backups.

--backup

Backs up your databases.

Using this command option, mariadb-backup performs a backup operation on your database or databases. The backups are written to the target directory, as set by the --target-dir option.

mariadb-backup --backup 
      --target-dir /path/to/backup \
      --user user_name --password user_passwd

mariadb-backup can perform full and incremental backups. A full backup creates a snapshot of the database in the target directory. An incremental backup checks the database against a previously taken full backup, (defined by the --incremental-basedir option) and creates delta files for these changes.

In order to restore from a backup, you first need to run mariadb-backup with the --prepare command option, to make a full backup point-in-time consistent or to apply incremental backup deltas to base. Then you can run mariadb-backup again with either the --copy-back or --move-back commands to restore the database.

For more information, see Full Backup and Restore and Incremental Backup and Restore.

--binlog-info

Defines how mariadb-backup retrieves the binary log coordinates from the server.

--binlog-info[=OFF | ON | LOCKLESS | AUTO]

The --binlog-info option supports the following retrieval methods. When no retrieval method is provided, it defaults to AUTO.

Using this option, you can control how mariadb-backup retrieves the server's binary log coordinates corresponding to the backup.

When enabled, whether using ON or AUTO, mariadb-backup retrieves information from the binlog during the backup process. When disabled with OFF, mariadb-backup runs without attempting to retrieve binary log information. You may find this useful when you need to copy data without metadata like the binlog or replication coordinates.

mariadb-backup --binlog-info --backup

Currently, the LOCKLESS option depends on features unsupported by MariaDB Server. See the description of the xtrabackup_binlog_pos_innodb file for more information. If you attempt to run mariadb-backup with this option, then it causes the utility to exit with an error.

--close-files

Defines whether you want to close file handles.

Using this option, you can tell mariadb-backup that you want to close file handles. Without this option, mariadb-backup keeps files open in order to manage DDL operations. When working with particularly large tablespaces, closing the file can make the backup more manageable. However, it can also lead to inconsistent backups. Use at your own risk.

mariadb-backup --close-files --prepare

--compress

Defines the compression algorithm for backup files.

--compress[=compression_algorithm]

The --compress option only supports the now deprecated quicklz algorithm.

mariadb-backup --compress --backup

If a backup is compressed using this option, then mariadb-backup will record that detail in the xtrabackup_info file.

--compress-chunk-size

Defines the working buffer size for compression threads.

--compress-chunk-size=#

mariadb-backup can perform compression operations on the backup files before writing them to disk. It can also use multiple threads for parallel data compression during this process. Using this option, you can set the chunk size each thread uses during compression. It defaults to 64K.

mariadb-backup --backup --compress \
     --compress-threads=12 --compress-chunk-size=5M

To further configure backup compression, see the --compress and --compress-threads options.

--compress-threads

Defines the number of threads to use in compression.

--compress-threads=#

mariadb-backup can perform compression operations on the backup files before writing them to disk. Using this option, you can define the number of threads you want to use for this operation. You may find this useful in speeding up the compression of particularly large databases. It defaults to single-threaded.

mariadb-backup --compress --compress-threads=12 --backup

To further configure backup compression, see the --compress and --compress-chunk-size options.

--copy-back

Restores the backup to the data directory.

Using this command, mariadb-backup copies the backup from the target directory to the data directory, as defined by the --datadir option. You must stop the MariaDB Server before running this command. The data directory must be empty. If you want to overwrite the data directory with the backup, use the --force-non-empty-directories option.

Bear in mind, before you can restore a backup, you first need to run mariadb-backup with the --prepare option. In the case of full backups, this makes the files point-in-time consistent. With incremental backups, this applies the deltas to the base backup. Once the backup is prepared, you can run --copy-back to apply it to MariaDB Server.

mariadb-backup --copy-back --force-non-empty-directories

Running the --copy-back command copies the backup files to the data directory. Use this command if you want to save the backup for later. If you don't want to save the backup for later, use the --move-back command.

--core-file

Defines whether to write a core file.

Using this option, you can configure mariadb-backup to dump its core to file in the event that it encounters fatal signals. You may find this useful for review and debugging purposes.

mariadb-backup --core-file --backup

--databases

Defines the databases and tables you want to back up.

--databases="database[.table][ database[.table] ...]"

Using this option, you can define the specific database or databases you want to back up. In cases where you have a particularly large database or otherwise only want to back up a portion of it, you can optionally also define the tables on the database.

mariadb-backup --backup \
      --databases="example.table1 example.table2"

In cases where you want to back up most databases on a server or tables on a database, but not all, you can set the specific databases or tables you don't want to back up using the --databases-exclude option.

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

In innobackupex mode, which can be enabled with the --innobackupex option, the --databases option can be used as described above, or it can be used to refer to a file, just as the --databases-file option can in the normal mode.

--databases-exclude

Defines the databases you don't want to back up.

--databases-exclude="database[.table][ database[.table] ...]"

Using this option, you can define the specific database or databases you want to exclude from the backup process. You may find it useful when you want to back up most databases on the server or tables on a database, but would like to exclude a few from the process.

mariadb-backup --backup \
      --databases="example" \
      --databases-exclude="example.table1 example.table2"

To include databases in the backup, see the --databases option option

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--databases-file

Defines the path to a file listing databases and/or tables you want to back up.

--databases-file="/path/to/database-file"

Format the databases file to list one element per line, with the following syntax:

database[.table]

In cases where you need to back up a number of databases or specific tables in a database, you may find the syntax for the --databases and --databases-exclude options a little cumbersome. Using this option you can set the path to a file listing the databases or databases and tables you want to back up.

For instance, imagine you list the databases and tables for a backup in a file called main-backup.

cat main-backup

example1
example2.table1
example2.table2

mariadb-backup --backup --databases-file=main-backup

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

-h, --datadir

Defines the path to the database root.

--datadir=PATH

Using this option, you can define the path to the source directory. This is the directory that mariadb-backup reads for the data it backs up. It should be the same as the MariaDB Server datadir system variable.

mariadb-backup --backup -h /var/lib64/mysql

--debug-sleep-before-unlock

This is a debug-only option used by the Xtrabackup test suite.

--decompress

Deprecated, for details see the --compress option.

This option requires that you have the qpress utility installed on your system.

Defines whether you want to decompress previously compressed backup files.

When you run mariadb-backup with the --compress option, it compresses the subsequent backup files, using the QuickLZ algorithm. Using this option, mariadb-backup decompresses the compressed files from a previous backup.

For instance, run a backup with compression,

mariadb-backup --compress --backup

Then decompress the backup,

mariadb-backup --decompress

You can enable the decryption of multiple files at a time using the --parallel option. By default, mariadb-backup does not remove the compressed files from the target directory. If you want to delete these files, use the --remove-original option.

--debug-sync

Defines the debug sync point. This option is only used by the mariadb-backup test suite.

--defaults-extra-file

Defines the path to an extra default option file.

--defaults-extra-file=/path/to/config

Using this option, you can define an extra default option file for mariadb-backup. Unlike --defaults-file, this file is read after the default option files are read, allowing you to only overwrite the existing defaults.

mariadb-backup --backup \
      --defaults-file-extra=addition-config.cnf \
      --defaults-file=config.cnf

--defaults-file

Defines the path to the default option file.

--defaults-file=/path/to/config

Using this option, you can define a default option file for mariadb-backup. Unlike the --defaults-extra-file option, when this option is provided, it completely replaces all default option files.

mariadb-backup --backup \
     --defaults-file="config.cnf

--defaults-group

Defines the option group to read in the option file.

--defaults-group="name"

In situations where you find yourself using certain mariadb-backup options consistently every time you call it, you can set the options in an option file. The --defaults-group option defines what option group mariadb-backup reads for its options.

Options you define from the command-line can be set in the configuration file using minor formatting changes. For instance, if you find yourself perform compression operations frequently, you might set --compress-threads and --compress-chunk-size options in this way:

[mariadb-backup]
compress_threads = 12
compress_chunk_size = 64K

Now whenever you run a backup with the --compress option, it always performs the compression using 12 threads and 64K chunks.

mariadb-backup --compress --backup

See mariadb-backup Overview: Server Option Groups and mariadb-backup Overview: Client Option Groups for a list of the option groups read by mariadb-backup by default.

--encrypted-backup

When this option is used with --backup, if mariadb-backup encounters a page that has a non-zero key_version value, then mariadb-backup assumes that the page is encrypted.

Use --skip-encrypted-backup instead to allow mariadb-backup to copy unencrypted tables that were originally created before MySQL 5.1.48.

--export

If this option is provided during the --prepare stage, then it tells mariadb-backup to create .cfg files for each InnoDB file-per-table tablespace. These .cfg files are used to import transportable tablespaces in the process of restoring partial backups and restoring individual tables and partitions.

The --export option could require rolling back incomplete transactions that had modified the table. This will likely create a "new branch of history" that does not correspond to the server that had been backed up, which makes it impossible to apply another incremental backup on top of such additional changes. The option should only be applied when doing a --prepare of the last incremental.

mariadb-backup --prepare --export

mariadb-backup did not support the --export option. See MDEV-13466 about that. In earlier versions of MariaDB, this means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

--extra-lsndir

Saves an extra copy of the xtrabackup_checkpoints and xtrabackup_info files into the given directory.

--extra-lsndir=PATH

When using the --backup command option, mariadb-backup produces a number of backup files in the target directory. Using this option, you can have mariadb-backup produce additional copies of the xtrabackup_checkpoints and xtrabackup_info files in the given directory.

mariadb-backup --extra-lsndir=extras/ --backup

This is especially usefull when using --stream for streaming output, e.g. for compression and/or encryption using external tools in combination with incremental backups, as the xtrabackup_checkpoints file necessary to determine the LSN to continue the incremental backup from is still accessible without uncompressing / decrypting the backup file first. Simply pass in the --extra-lsndir of the previous backup as--incremental-basedir

--force-non-empty-directories

Allows --copy-back or --move-back command options to use non-empty target directories.

When using mariadb-backup with the --copy-back or --move-back command options, they normally require a non-empty target directory to avoid conflicts. Using this option with either of command allows mariadb-backup to use a non-empty directory.

mariadb-backup --force-non-empty-directories --copy-back

Bear in mind that this option does not enable overwrites. When copying or moving files into the target directory, if mariadb-backup finds that the target file already exists, it fails with an error.

--ftwrl-wait-query-type

Defines the type of query allowed to complete before mariadb-backup issues the global lock.

--ftwrl-wait-query-type=[ALL | UPDATE | SELECT]

The --ftwrl-wait-query-type option supports the following query types. The default value is ALL.

When mariadb-backup runs, it issues a global lock to prevent data from changing during the backup process. When it encounters a statement in the process of executing, it waits until the statement is finished before issuing the global lock. Using this option, you can modify this default behavior to ensure that it waits only for certain query types, such as for SELECT and UPDATE statements.

mariadb-backup --backup  \
      --ftwrl-wait-query-type=UPDATE

--ftwrl-wait-threshold

Defines the minimum threshold for identifying long-running queries for FTWRL.

--ftwrl-wait-threshold=#

When mariadb-backup runs, it issues a global lock to prevent data from changing during the backup process and ensure a consistent record. If it encounters statements still in the process of executing, it waits until they complete before setting the lock. Using this option, you can set the threshold at which mariadb-backup engages FTWRL. When it --ftwrl-wait-timeout is not 0 and a statement has run for at least the amount of time given this argument, mariadb-backup waits until the statement completes or until the --ftwrl-wait-timeout expires before setting the global lock and starting the backup.

mariadb-backup --backup \
     --ftwrl-wait-timeout=90 \
     --ftwrl-wait-threshold=30

--ftwrl-wait-timeout

Defines the timeout to wait for queries before trying to acquire the global lock. The global lock refers to BACKUP STAGE BLOCK_COMMIT. The global lock refers to FLUSH TABLES WITH READ LOCK (FTWRL).

--ftwrl-wait-timeout=#

When mariadb-backup runs, it acquires a global lock to prevent data from changing during the backup process and ensure a consistent record. If it encounters statements still in the process of executing, it can be configured to wait until the statements complete before trying to acquire the global lock.

If the --ftwrl-wait-timeout is set to 0, then mariadb-backup tries to acquire the global lock immediately without waiting. This is the default value.

If the --ftwrl-wait-timeout is set to a non-zero value, then mariadb-backup waits for the configured number of seconds until trying to acquire the global lock.

Starting in MariaDB 10.5.3, mariadb-backup will exit if it can't acquire the global lock after waiting for the configured number of seconds. In earlier versions, it could wait for the global lock indefinitely, even if --ftwrl-wait-timeout was set to a non-zero value.

mariadb-backup --backup \
      --ftwrl-wait-query-type=UPDATE \
      --ftwrl-wait-timeout=5

--galera-info

Defines whether you want to back up information about a Galera Cluster node's state.

When this option is used, mariadb-backup creates an additional file called xtrabackup_galera_info, which records information about a Galera Cluster node's state. It records the values of the wsrep_local_state_uuid and wsrep_last_committed status variables.

You should only use this option when backing up a Galera Cluster node. If the server is not a Galera Cluster node, then this option has no effect.

This option, when enabled and used with GTID replication, will rotate the binary logs at backup time.

mariadb-backup --backup --galera-info

--history

Defines whether you want to track backup history in the PERCONA_SCHEMA.xtrabackup_history table.

--history[=name]

When using this option, mariadb-backup records its operation in a table on the MariaDB Server. Passing a name to this option allows you group backups under arbitrary terms for later processing and analysis.

mariadb-backup --backup --history=backup_all

Currently, the table it uses by default is named mysql.mariadb_backup_history. Prior to MariaDB 10.11, the default table was PERCONA_SCHEMA.xtrabackup_history.

mariadb-backup will also record this in the xtrabackup_info file.

-H, --host

Defines the host for the MariaDB Server you want to backup.

--host=name

Using this option, you can define the host to use when connecting to a MariaDB Server over TCP/IP. By default, mariadb-backup attempts to connect to the local host.

mariadb-backup --backup \
      --host="example.com"

--include

This option is a regular expression to be matched against table names in databasename.tablename format. It is equivalent to the --tables option. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

--incremental

Defines whether you want to take an increment backup, based on another backup. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

mariadb-backup --innobackupex --incremental

Using this option with the --backup command option makes the operation incremental rather than a complete overwrite. When this option is specified, either the --incremental-lsn or[--incremental-basedir](mariadb-backup-options.md#-incremental-basedir)options can also be given. If neither option is given, option[--incremental-basedir](mariadb-backup-options.md#-incremental-basedir)is used by default, set to the first timestamped backup directory in the backup base directory.

mariadb-backup --innobackupex --backup --incremental \
     --incremental-basedir=/data/backups \
     --target-dir=/data/backups

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--incremental-basedir

Defines whether you want to take an incremental backup, based on another backup.

--incremental-basedir=PATH

Using this option with the --backup command option makes the operation incremental rather than a complete overwrite. mariadb-backup will only copy pages from .ibd files if they are newer than the backup in the specified directory.

mariadb-backup --backup \
     --incremental-basedir=/data/backups \
     --target-dir=/data/backups

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--incremental-dir

Defines whether you want to take an incremental backup, based on another backup.

--increment-dir=PATH

Using this option with --prepare command option makes the operation incremental rather than a complete overwrite. mariadb-backup will apply .delta files and log files into the target directory.

mariadb-backup --prepare \
      --increment-dir=backups/

If a backup is a incremental backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--incremental-force-scan

Defines whether you want to force a full scan for incremental backups.

When using mariadb-backup to perform an incremental backup, this option forces it to also perform a full scan of the data pages being backed up, even when there's bitmap data on the changes. MariaDB does not support changed page bitmaps, so this option is useless in those versions. See MDEV-18985 for more information.

mariadb-backup --backup \
     --incremental-basedir=/path/to/target \
     --incremental-force-scan

--incremental-history-name

Defines a logical name for the backup.

--incremental-history-name=name

mariadb-backup can store data about its operations on the MariaDB Server. Using this option, you can define the logical name it uses in identifying the backup.

mariadb-backup --backup \
     --incremental-history-name=morning_backup

Currently, the table it uses by default is named mysql.mariadb_backup_history. Prior to MariaDB 10.11, the default table was PERCONA_SCHEMA.xtrabackup_history.

mariadb-backup will also record this in the xtrabackup_info file.

--incremental-history-uuid

Defines a UUID for the backup.

--incremental-history-uuid=name

mariadb-backup can store data about its operations on the MariaDB Server. Using this option, you can define the UUID it uses in identifying a previous backup to increment from. It checks --incremental-history-name, --incremental-basedir, and --incremental-lsn. If mariadb-backup fails to find a valid lsn, it generates an error.

mariadb-backup --backup \
      --incremental-history-uuid=main-backup012345678

Currently, the table it uses is named PERCONA_SCHEMA.xtrabackup_history, but expect that name to change in future releases. See MDEV-19246 for more information.

mariadb-backup will also record this in the xtrabackup_info file.

--incremental-lsn

Defines the sequence number for incremental backups.

--incremental-lsn=name

Using this option, you can define the sequence number (LSN) value for --backup operations. During backups, mariadb-backup only copies .ibd pages newer than the specified values.

WARNING: Incorrect LSN values can make the backup unusable. It is impossible to diagnose this issue.

--innobackupex

Deprecated

Enables innobackupex mode, which is a compatibility mode.

mariadb-backup --innobackupex

In innobackupex mode, mariadb-backup has the following differences:

• To prepare a backup, the --apply-log option is used instead of the --prepare option.

• To create an incremental backup, the --incremental option is supported.

• The --no-timestamp option is supported.

• To create a partial backup, the --include option is used instead of the --tables option.

• To create a partial backup, the --databases option can still be used, but it's behavior changes slightly.

• The --target-dir option is not used to specify the backup directory. The backup directory should instead be specified as a standalone argument.

The primary purpose of innobackupex mode is to allow scripts and tools to more easily migrate to mariadb-backup if they were originally designed to use the innobackupex utility that is included with Percona XtraBackup. It is not recommended to use this mode in new scripts, since it is not guaranteed to be supported forever. See MDEV-20552 for more information.

--innodb

This option has no effect. Set only for MySQL option compatibility.

--innodb-adaptive-hash-index

Enables InnoDB Adaptive Hash Index.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can explicitly enable the InnoDB Adaptive Hash Index. This feature is enabled by default for mariadb-backup. If you want to disable it, use --skip-innodb-adaptive-hash-index.

mariadb-backup --backup \
      --innodb-adaptive-hash-index

--innodb-autoextend-increment

Defines the increment in megabytes for auto-extending the size of tablespace file.

--innodb-autoextend-increment=36

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the increment in megabytes for automatically extending the size of tablespace data file in InnoDB.

mariadb-backup --backup \
     --innodb-autoextend-increment=35

--innodb-buffer-pool-filename

Using this option has no effect. It is available to provide compatibility with the MariaDB Server.

--innodb-buffer-pool-size

Defines the memory buffer size InnoDB uses the cache data and indexes of the table.

--innodb-buffer-pool-size=124M

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can configure the buffer pool for InnoDB operations.

mariadb-backup --backup \
      --innodb-buffer-pool-size=124M

--innodb-checksum-algorithm

innodb_checksum_algorithm was deprecated in MariaDB 10.5.10 and removed in MariaDB 10.6.

In earlier versions, it is used to define the checksum algorithm.

--innodb-checksum-algorithm=crc32
                           | strict_crc32
                           | innodb
                           | strict_innodb
                           | none
                           | strict_none

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can specify the algorithm mariadb-backup uses when checksumming on InnoDB tables. Currently, MariaDB supports the following algorithms CRC32, STRICT_CRC32, INNODB, STRICT_INNODB, NONE, STRICT_NONE.

mariadb-backup --backup \
      ---innodb-checksum-algorithm=strict_innodb

--innodb-data-file-path

Defines the path to individual data files.

--innodb-data-file-path=/path/to/file

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can define the path to InnoDB data files. Each path is appended to the --innodb-data-home-dir option.

mariadb-backup --backup \
     --innodb-data-file-path=ibdata1:13M:autoextend \
     --innodb-data-home-dir=/var/dbs/mysql/data

--innodb-data-home-dir

Defines the home directory for InnoDB data files.

--innodb-data-home-dir=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can define the path to the directory containing InnoDB data files. You can specific the files using the --innodb-data-file-path option.

mariadb-backup --backup \
     --innodb-data-file-path=ibdata1:13M:autoextend \
     --innodb-data-home-dir=/var/dbs/mysql/data

--innodb-doublewrite

Enables doublewrites for InnoDB tables.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. When using this option, mariadb-backup improves fault tolerance on InnoDB tables with a doublewrite buffer. By default, this feature is enabled. Use this option to explicitly enable it. To disable doublewrites, use the --skip-innodb-doublewrite option.

mariadb-backup --backup \
     --innodb-doublewrite

--innodb-encrypt-log

Defines whether you want to encrypt InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can tell mariadb-backup that you want to encrypt logs from its InnoDB activity.

--innodb-file-io-threads

Defines the number of file I/O threads in InnoDB.

--innodb-file-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the number of file I/O threads mariadb-backup uses on InnoDB tables.

mariadb-backup --backup \
     --innodb-file-io-threads=5

--innodb-file-per-table

Defines whether you want to store each InnoDB table as an .ibd file.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option causes mariadb-backup to store each InnoDB table as an .ibd file in the target directory.

--innodb-flush-method

Defines the data flush method. Ignored from MariaDB 11.0.

--innodb-flush-method=fdatasync 
                     | O_DSYNC 
                     | O_DIRECT 
                     | O_DIRECT_NO_FSYNC 
                     | ALL_O_DIRECT

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the data flush method mariadb-backup uses with InnoDB tables.

mariadb-backup --backup \
      --innodb-flush-method==_DIRECT_NO_FSYNC

--innodb-io-capacity

Defines the number of IOP's the utility can perform.

--innodb-io-capacity=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can limit the I/O activity for InnoDB background tasks. It should be set around the number of I/O operations per second that the system can handle, based on drive or drives being used.

mariadb-backup --backup \
     --innodb-io-capacity=200

--innodb-log-checksums

Defines whether to include checksums in the InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can explicitly set mariadb-backup to include checksums in the InnoDB logs. The feature is enabled by default. To disable it, use the --skip-innodb-log-checksums option.

mariadb-backup --backup \
      --innodb-log-checksums

--innodb-log-buffer-size

This option has no functionality in mariadb-backup. It exists for MariaDB Server compatibility.

--innodb-log-files-in-group

This option has no functionality in mariadb-backup. It exists for MariaDB Server compatibility.

--innodb-log-group-home-dir

Defines the path to InnoDB log files.

--innodb-log-group-home-dir=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the path to InnoDB log files.

mariadb-backup --backup \
     --innodb-log-group-home-dir=/path/to/logs

--innodb-max-dirty-pages-pct

Defines the percentage of dirty pages allowed in the InnoDB buffer pool.

--innodb-max-dirty-pages-pct=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the maximum percentage of dirty, (that is, unwritten) pages that mariadb-backup allows in the InnoDB buffer pool.

mariadb-backup --backup \
     --innodb-max-dirty-pages-pct=80

--innodb-open-files

Defines the number of files kept open at a time.

--innodb-open-files=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the maximum number of files InnoDB keeps open at a given time during backups.

mariadb-backup --backup \
      --innodb-open-files=10

--innodb-page-size

Defines the universal page size.

--innodb-page-size=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the universal page size in bytes for mariadb-backup.

mariadb-backup --backup \
     --innodb-page-size=16k

--innodb-read-io-threads

Defines the number of background read I/O threads in InnoDB.

--innodb-read-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the number of I/O threads MariaDB uses when reading from InnoDB.

mariadb-backup --backup \
      --innodb-read-io-threads=4

--innodb-undo-directory

Defines the directory for the undo tablespace files.

--innodb-undo-directory=PATH

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the path to the directory where you want MariaDB to store the undo tablespace on InnoDB tables. The path can be absolute.

mariadb-backup --backup \
     --innodb-undo-directory=/path/to/innodb_undo

--innodb-undo-tablespaces

Defines the number of undo tablespaces to use.

--innodb-undo-tablespaces=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can define the number of undo tablespaces you want to use during the backup.

mariadb-backup --backup \
      --innodb-undo-tablespaces=10

--innodb-use-native-aio

Defines whether you want to use native AI/O.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can enable the use of the native asynchronous I/O subsystem. It is only available on Linux operating systems.

mariadb-backup --backup \
      --innodb-use-native-aio

--innodb-write-io-threads

Defines the number of background write I/O threads in InnoDB.

--innodb-write-io-threads=#

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set the number of background write I/O threads mariadb-backup uses.

mariadb-backup --backup \
     --innodb-write-io-threads=4

--kill-long-queries-timeout

Defines the timeout for blocking queries.

--kill-long-queries-timeout=#

When mariadb-backup runs, it issues a FLUSH TABLES WITH READ LOCK statement. It then identifies blocking queries. Using this option you can set a timeout in seconds for these blocking queries. When the time runs out, mariadb-backup kills the queries.

The default value is 0, which causes mariadb-backup to not attempt killing any queries.

mariadb-backup --backup \
      --kill-long-queries-timeout=10

--kill-long-query-type

Defines the query type the utility can kill to unblock the global lock.

--kill-long-query-type=ALL | UPDATE | SELECT

When mariadb-backup encounters a query that sets a global lock, it can kill the query in order to free up MariaDB Server for the backup. Using this option, you can choose the types of query it kills: SELECT, UPDATE, or both set with ALL. The default is ALL.

mariadb-backup --backup \
      --kill-long-query-type=UPDATE

--lock-ddl-per-table

Prevents DDL for each table to be backed up by acquiring MDL lock on that. NOTE: Unless --no-lock option was also specified, conflicting DDL queries , will be killed at the end of backup This is done avoid deadlock between "FLUSH TABLE WITH READ LOCK", user's DDL query (ALTER, RENAME), and MDL lock on table.

--log

This option has no functionality. It is set to ensure compatibility with MySQL.

--log-bin

Defines the base name for the log sequence.

--log-bin[=name]

Using this option you, you can set the base name for mariadb-backup to use in log sequences.

--log-copy-interval

Defines the copy interval between checks done by the log copying thread.

--log-copy-interval=#

Using this option, you can define the copy interval mariadb-backup uses between checks done by the log copying thread. The given value is in milliseconds.

mariadb-backup --backup \
      --log-copy-interval=50

--log-innodb-page-corruption

Continue backup if InnoDB corrupted pages are found. The pages are logged in innodb_corrupted_pages and backup is finished with error. --prepare will try to fix corrupted pages. If innodb_corrupted_pages exists after --prepare in base backup directory, backup still contains corrupted pages and can not be considered as consistent.

Added in MariaDB 10.5.9

--move-back

Restores the backup to the data directory.

Using this command, mariadb-backup moves the backup from the target directory to the data directory, as defined by the --datadir option. You must stop the MariaDB Server before running this command. The data directory must be empty. If you want to overwrite the data directory with the backup, use the --force-non-empty-directories option.

Bear in mind, before you can restore a backup, you first need to run mariadb-backup with the --prepare option. In the case of full backups, this makes the files point-in-time consistent. With incremental backups, this applies the deltas to the base backup. Once the backup is prepared, you can run --move-back to apply it to MariaDB Server.

mariadb-backup --move-back \
      --datadir=/var/mysql

Running the --move-back command moves the backup files to the data directory. Use this command if you don't want to save the backup for later. If you do want to save the backup for later, use the --copy-back command.

--mysqld

Used internally to prepare a backup.

--no-backup-locks

mariadb-backup locks the database by default when it runs. This option disables support for Percona Server's backup locks.

When backing up Percona Server, mariadb-backup would use backup locks by default. To be specific, backup locks refers to the LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP statements. This option can be used to disable support for Percona Server's backup locks. This option has no effect when the server does not support Percona's backup locks.

Deprecated and has no effect from MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5 and MariaDB 11.2.4 as MariaDB will now always use backup locks for better performance. See MDEV-32932.

mariadb-backup --backup --no-backup-locks

--no-lock

Disables table locks with the FLUSH TABLE WITH READ LOCK statement.

Using this option causes mariadb-backup to disable table locks with the FLUSH TABLE WITH READ LOCK statement. Only use this option if:

• You are not executing DML statements on non-InnoDB tables during the backup. This includes the mysql database system tables (which are MyISAM).

• You are not executing any DDL statements during the backup.

• You are not using the file "xtrabackup_binlog_info", which is not consistent with the data when --no-lock is used. Use the file "xtrabackup_binlog_pos_innodb" [link] instead.

• All tables you're backing up use the InnoDB storage engine.

mariadb-backup --backup --no-lock

If you're considering --no-lock due to backups failing to acquire locks, this may be due to incoming replication events preventing the lock. Consider using the --safe-slave-backup option to momentarily stop the replica thread. This alternative may help the backup to succeed without resorting to --no-lock.

The --no-lock option only provides a consistent backup if the user ensures that no DDL or non-transactional table updates occur during the backup. The --no-lock option is not supported by MariaDB plc.

--no-timestamp

This option prevents creation of a time-stamped subdirectory of the BACKUP-ROOT-DIR given on the command line. When it is specified, the backup is done in BACKUP-ROOT-DIR instead. This is only valid in innobackupex mode, which can be enabled with the --innobackupex option.

--no-version-check

Disables version check.

Using this option, you can disable mariadb-backup version check.

mariadb-backup --backup --no-version-check

--open-files-limit

Defines the maximum number of file descriptors.

--open-files-limit=#

Using this option, you can define the maximum number of file descriptors mariadb-backup reserves with setrlimit().

mariadb-backup --backup \
      --open-files-limit=

--parallel

Defines the number of threads to use for parallel data file transfer.

--parallel=#

Using this option, you can set the number of threads mariadb-backup uses for parallel data file transfers. By default, it is set to 1.

-p, --password

Defines the password to use to connect to MariaDB Server.

--password=passwd

When you run mariadb-backup, it connects to MariaDB Server in order to access and back up the databases and tables. Using this option, you can set the password mariadb-backup uses to access the server. To set the user, use the --user option.

mariadb-backup --backup \
      --user=root \
      --password=root_password

--plugin-dir

Defines the directory for server plugins.

--plugin-dir=PATH

Using this option, you can define the path mariadb-backup reads for MariaDB Server plugins. It only uses it during the --prepare phase to load the encryption plugin. It defaults to the plugin_dir server system variable.

mariadb-backup --backup \
      --plugin-dir=/var/mysql/lib/plugin

--plugin-load

Defines the encryption plugins to load.

--plugin-load=name

Using this option, you can define the encryption plugin you want to load. It is only used during the --prepare phase to load the encryption plugin. It defaults to the server --plugin-load option.

The option was removed.

-P, --port

Defines the server port to connect to.

--port=#

When you run mariadb-backup, it connects to MariaDB Server in order to access and back up your databases and tables. Using this option, you can set the port the utility uses to access the server over TCP/IP. To set the host, see the --host option. Use mysql --help for more details.

mariadb-backup --backup \
      --host=192.168.11.1 \
      --port=3306

--prepare

Prepares an existing backup to restore to the MariaDB Server.

Files that mariadb-backup generates during --backup operations in the target directory are not ready for use on the Server. Before you can restore the data to MariaDB, you first need to prepare the backup.

In the case of full backups, the files are not point in time consistent, since they were taken at different times. If you try to restore the database without first preparing the data, InnoDB rejects the new data as corrupt. Running mariadb-backup with the --prepare command readies the data so you can restore it to MariaDB Server. When working with incremental backups, you need to use the --prepare command and the --incremental-dir option to update the base backup with the deltas from an incremental backup.

mariadb-backup --prepare

Once the backup is ready, you can use the --copy-back or the --move-back commands to restore the backup to the server.

--print-defaults

Prints the utility argument list, then exits.

Using this argument, MariaDB prints the argument list to stdout and then exits. You may find this useful in debugging to see how the options are set for the utility.

mariadb-backup --print-defaults

--print-param

Prints the MariaDB Server options needed for copyback.

Using this option, mariadb-backup prints to stdout the MariaDB Server options that the utility requires to run the --copy-back command option.

mariadb-backup --print-param

--rollback-xa

By default, mariadb-backup will not commit or rollback uncommitted XA transactions, and when the backup is restored, any uncommitted XA transactions must be manually committed using XA COMMIT or manually rolled back using XA ROLLBACK.

MariaDB starting with 10.5

mariadb-backup's --rollback-xa option is not present because the server has more robust ways of handling uncommitted XA transactions.

This is an experimental option. Do not use this option in older versions. Older implementation can cause corruption of InnoDB data.

--rsync

Defines whether to use rsync.

During normal operation, mariadb-backup transfers local non-InnoDB files using a separate call to cp for each file. Using this option, you can optimize this process by performing this transfer with rsync, instead.

mariadb-backup --backup --rsync

This option is not compatible with the --stream option.

Deprecated and has no effect from MariaDB 10.11.8, MariaDB 11.0.6, MariaDB 11.1.5 and MariaDB 11.2.4 as rsync will not work on tables that are in use. See MDEV-32932.

--safe-slave-backup

Stops replica SQL threads for backups.

When running mariadb-backup on a server that uses replication, you may occasionally encounter locks that block backups. Using this option, it stops replica SQL threads and waits until the Slave_open_temp_tables in the SHOW STATUS statement is zero. If there are no open temporary tables, the backup runs, otherwise the SQL thread starts and stops until there are no open temporary tables.

mariadb-backup --backup \
      --safe-slave-backup \
      --safe-slave-backup-timeout=500

The backup fails if the Slave_open_temp_tables doesn't reach zero after the timeout period set by the --safe-slave-backup-timeout option.

--safe-slave-backup-timeout

Defines the timeout for replica backups.

--safe-slave-backup-timeout=#

When running mariadb-backup on a server that uses replication, you may occasionally encounter locks that block backups. With the --safe-slave-backup option, it waits until the Slave_open_temp_tables in the SHOW STATUS statement reaches zero. Using this option, you set how long it waits. It defaults to 300.

mariadb-backup --backup \
      --safe-slave-backup \
      --safe-slave-backup-timeout=500

--secure-auth

Refuses client connections to servers using the older protocol.

Using this option, you can set it explicitly to refuse client connections to the server when using the older protocol, from before 4.1.1. This feature is enabled by default. Use the --skip-secure-auth option to disable it.

mariadb-backup --backup --secure-auth

--skip-innodb-adaptive-hash-index

Disables InnoDB Adaptive Hash Index.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option you can explicitly disable the InnoDB Adaptive Hash Index. This feature is enabled by default for mariadb-backup. If you want to explicitly enable it, use --innodb-adaptive-hash-index.

mariadb-backup --backup \
      --skip-innodb-adaptive-hash-index

--skip-innodb-doublewrite

Disables doublewrites for InnoDB tables.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. When doublewrites are enabled, InnoDB improves fault tolerance with a doublewrite buffer. By default this feature is turned on. Using this option you can disable it for mariadb-backup. To explicitly enable doublewrites, use the --innodb-doublewrite option.

mariadb-backup --backup \
     --skip-innodb-doublewrite

--skip-innodb-log-checksums

Defines whether to exclude checksums in the InnoDB logs.

mariadb-backup initializes its own embedded instance of InnoDB using the same configuration as defined in the configuration file. Using this option, you can set mariadb-backup to exclude checksums in the InnoDB logs. The feature is enabled by default. To explicitly enable it, use the --innodb-log-checksums option.

--skip-secure-auth

Refuses client connections to servers using the older protocol.

Using this option, you can set it accept client connections to the server when using the older protocol, from before 4.1.1. By default, it refuses these connections. Use the --secure-auth option to explicitly enable it.

mariadb-backup --backup --skip-secure-auth

--slave-info

Prints the binary log position and the name of the primary server.

If the server is a replica, then this option causes mariadb-backup to print the hostname of the replica's replication primary and the binary log file and position of the replica's SQL thread to stdout.

This option also causes mariadb-backup to record this information as a CHANGE MASTER command that can be used to set up a new server as a replica of the original server's primary after the backup has been restored. This information will be written to the xtrabackup_slave_info file.

mariadb-backup does not check if GTIDs are being used in replication. It takes a shortcut and assumes that if the gtid_slave_pos system variable is non-empty, then it writes the CHANGE MASTER command with the MASTER_USE_GTID option set to slave_pos. Otherwise, it writes the CHANGE MASTER command with the MASTER_LOG_FILE and MASTER_LOG_POS options using the primary's binary log file and position. See MDEV-19264 for more information.

mariadb-backup --slave-info

-S, --socket

Defines the socket for connecting to local database.

--socket=name

Using this option, you can define the UNIX domain socket you want to use when connecting to a local database server. The option accepts a string argument. For more information, see the mysql --help command.

mariadb-backup --backup \
      --socket=/var/mysql/mysql.sock

--ssl

Enables TLS. By using this option, you can explicitly configure mariadb-backup to encrypt its connection with TLS when communicating with the server. You may find this useful when performing backups in environments where security is extra important or when operating over an insecure network.

TLS is also enabled even without setting this option when certain other TLS options are set. For example, see the descriptions of the following options:

• --ssl-ca

• --ssl-capath

• --ssl-cert

• --ssl-cipher

• --ssl-key

--ssl-ca

Defines a path to a PEM file that should contain one or more X509 certificates for trusted Certificate Authorities (CAs) to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

See Secure Connections Overview: Certificate Authorities (CAs) for more information.

This option implies the --ssl option.

--ssl-capath

Defines a path to a directory that contains one or more PEM files that should each contain one X509 certificate for a trusted Certificate Authority (CA) to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-capath=/etc/my.cnf.d/certificates/ca/

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-capath=/etc/my.cnf.d/certificates/ca/

The directory specified by this option needs to be run through the openssl rehash command.

See Secure Connections Overview: Certificate Authorities (CAs) for more information

This option implies the --ssl option.

--ssl-cert

Defines a path to the X509 certificate file to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option implies the --ssl option.

--ssl-cipher

Defines the list of permitted ciphers or cipher suites to use for TLS. For example:

--ssl-cipher=name

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem
   --ssl-cipher=TLSv1.2

To determine if the server restricts clients to specific ciphers, check the ssl_cipher system variable.

This option implies the --ssl option.

--ssl-crl

Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-crl=/etc/my.cnf.d/certificates/crl.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-crl=/etc/my.cnf.d/certificates/crl.pem

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.

This option is only supported if mariadb-backup was built with OpenSSL. If mariadb-backup was built with yaSSL, then this option is not supported. See TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

--ssl-crlpath

Defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-crlpath=/etc/my.cnf.d/certificates/crl/

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-crlpath=/etc/my.cnf.d/certificates/crl/

The directory specified by this option needs to be run through the openssl rehash command.

See Secure Connections Overview: Certificate Revocation Lists (CRLs) for more information.

This option is only supported if mariadb-backup was built with OpenSSL. If mariadb-backup was built with yaSSL, then this option is not supported. See TLS and Cryptography Libraries Used by MariaDB for more information about which libraries are used on which platforms.

--ssl-key

Defines a path to a private key file to use for TLS. This option requires that you use the absolute path, not a relative path. For example:

--ssl-key=/etc/my.cnf.d/certificates/client-key.pem

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem

This option implies the --ssl option.

--ssl-verify-server-cert

Enables server certificate verification. This option is disabled by default.

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --ssl-verify-server-cert

--stream

Streams backup files to stdout.

--stream=xbstream

Using this command option, you can set mariadb-backup to stream the backup files to stdout in the given format. Currently, the supported format is xbstream.

mariadb-backup --stream=xbstream > backup.xb

To extract all files from the xbstream archive into a directory use the mbstream utility

mbstream  -x < backup.xb

If a backup is streamed, then mariadb-backup will record the format in the xtrabackup_info file.

--tables

Defines the tables you want to include in the backup.

--tables=REGEX

Using this option, you can define what tables you want mariadb-backup to back up from the database. The table values are defined using Regular Expressions. To define the tables you want to exclude from the backup, see the --tables-exclude option.

mariadb-backup --backup \
     --databases=example
     --tables=nodes_* \
     --tables-exclude=nodes_tmp

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--tables-exclude

Defines the tables you want to exclude from the backup.

--tables-exclude=REGEX

Using this option, you can define what tables you want mariadb-backup to exclude from the backup. The table values are defined using Regular Expressions. To define the tables you want to include from the backup, see the --tables option.

mariadb-backup --backup \
     --databases=example
     --tables=nodes_* \
     --tables-exclude=nodes_tmp

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--tables-file

Defines path to file with tables for backups.

--tables-file=/path/to/file

Using this option, you can set a path to a file listing the tables you want to back up. mariadb-backup iterates over each line in the file. The format is database.table.

mariadb-backup --backup \
     --databases=example \
     --tables-file=/etc/mysql/backup-file

If a backup is a partial backup, then mariadb-backup will record that detail in the xtrabackup_info file.

--target-dir

Defines the destination directory.

--target-dir=/path/to/target

Using this option you can define the destination directory for the backup. mariadb-backup writes all backup files to this directory. mariadb-backup will create the directory, if it does not exist (but it will not create the full path recursively, i.e. at least parent directory if the --target-dir must exist=

mariadb-backup --backup \
       --target-dir=/data/backups

--throttle

Defines the limit for I/O operations per second in IOS values.

--throttle=#

Using this option, you can set a limit on the I/O operations mariadb-backup performs per second in IOS values. It is only used during the --backup command option.

--tls-version

This option accepts a comma-separated list of TLS protocol versions. A TLS protocol version will only be enabled if it is present in this list. All other TLS protocol versions will not be permitted. For example:

--tls-version="TLSv1.2,TLSv1.3"

This option is usually used with other TLS options. For example:

mariadb-backup --backup \
   --ssl-cert=/etc/my.cnf.d/certificates/client-cert.pem \
   --ssl-key=/etc/my.cnf.d/certificates/client-key.pem \
   --ssl-ca=/etc/my.cnf.d/certificates/ca.pem \
   --tls-version="TLSv1.2,TLSv1.3"

See Secure Connections Overview: TLS Protocol Versions for more information.

-t, --tmpdir

Defines path for temporary files.

--tmpdir=/path/tmp[;/path/tmp...]

Using this option, you can define the path to a directory mariadb-backup uses in writing temporary files. If you want to use more than one, separate the values by a semicolon (that is, ;). When passing multiple temporary directories, it cycles through them using round-robin.

mariadb-backup --backup \
     --tmpdir=/data/tmp;/tmp

--use-memory

Defines the buffer pool size that is used during the prepare stage.

--use-memory=124M

Using this option, you can define the buffer pool size for mariadb-backup. Use it instead of buffer_pool_size.

mariadb-backup --prepare \
      --use-memory=124M

--user

Defines the username for connecting to the MariaDB Server.

--user=name
-u name

When mariadb-backup runs, it connects to the specified MariaDB Server to get its backups. Using this option, you can define the database user used for authentication. Starting from MariaDB 10.5.24, MariaDB 10.6.17, MariaDB 10.11.7, MariaDB 11.0.5, MariaDB 11.1.4, MariaDB 11.2.3, MariaDB 11.3.2, MariaDB 11.4.1, if the --user option is ommited, the user name is detected from the OS.

mariadb-backup --backup \
      --user=root \
      --password=root_passwd

--verbose

Displays verbose output

mariadb-backup --verbose

--version

Prints version information.

Using this option, you can print the mariadb-backup version information to stdout.

mariadb-backup --version

_{This page is licensed: CC BY-SA / Gnu FDL}

When using mariadb-backup, you have the option of performing a full or an incremental backup. Full backups create a complete backup of the database server in an empty directory while incremental backups update a previous backup with whatever changes to the data have occurred since the backup. This page documents how to perform full backups.

Backing up the Database Server

In order to back up the database, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the backup files. When taking a full backup, the target directory must be empty or it must not exist.

To take a backup, run the following command:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

The time the backup takes depends on the size of the databases or tables you're backing up. You can cancel the backup if you need to, as the backup process does not modify the database.

Mariadb-backup writes the backup files the target directory. If the target directory doesn't exist, then it creates it. If the target directory exists and contains files, then it raises an error and aborts.

Here is an example backup directory:

$ ls /var/mariadb/backup/

aria_log.0000001  mysql                   xtrabackup_checkpoints
aria_log_control  performance_schema      xtrabackup_info
backup-my.cnf     test                    xtrabackup_logfile
ibdata1           xtrabackup_binlog_info

Preparing the Backup for Restoration

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

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare option.

$ mariadb-backup --prepare \
   --target-dir=/var/mariadb/backup/

Backup Preparation Steps

1. Run mariadb-backup --backup. 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.4 to 10.5, you must use the 10.4 version of mariadb-backup, Another example: When upgrading from MariaDB 10.6 to 10.11, you must use the 10.6 version of mariadb-backup.

2. Run mariadb-backup --prepare, again using a compatible version of mariadb-backup, as described in the previous step.

Restoring the Backup

Once the backup is complete and you have prepared the backup for restoration (previous step), you can restore the backup using either the --copy-back or the --move-back options. 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.

• First, stop the MariaDB Server process.

• Then, ensure that the datadir is empty.

• Then, run mariadb-backup with one of the options mentioned above:

$ mariadb-backup --copy-back \
   --target-dir=/var/mariadb/backup/

• Then, you may need to fix the file permissions.

When mariadb-backup restores a database, it preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database. As such, 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. For example, to recursively change ownership of the files to the mysql user and group, you could execute:

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

• Finally, start the MariaDB Server process.

Restoring with Other Tools

Once a full backup is prepared, it is a fully functional MariaDB data directory. Therefore, as long as the MariaDB Server process is stopped on the target server, you can technically restore the backup using any file copying tool, such as cp or rysnc. For example, you could also execute the following to restore the backup:

$ rsync -avrP /var/mariadb/backup /var/lib/mysql/
$ chown -R mysql:mysql /var/lib/mysql/

_{This page is licensed: CC BY-SA / Gnu FDL}

When using mariadb-backup, you have the option of performing a full or incremental backup. Full backups create a complete copy in an empty directory while incremental backups update a previous backup with new data. This page documents incremental backups.

InnoDB pages contain log sequence numbers, or LSN's. Whenever you modify a row on any InnoDB table on the database, the storage engine increments this number. When performing an incremental backup, mariadb-backup 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.

Backing up the Database Server

In order to take an incremental backup, you first need to take a full backup. In order to back up the database, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the backup files. When taking a full backup, the target directory must be empty or it must not exist.

To take a backup, run the following command:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

This backs up all databases into the target directory /var/mariadb/backup. If you look in that directory at the xtrabackup_checkpoints file, you can see the LSN data provided by InnoDB.

For example:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1635102
last_lsn = 1635102
recover_binlog_info = 0

Backing up the Incremental Changes

Once you have created a full backup on your system, you can also back up the incremental changes as often as you would like.

In order to perform an incremental backup, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the incremental changes. The target directory must be empty. You also need to run it with the --incremental-basedir option to tell it the path to the full backup taken above. For example:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/inc1/ \
   --incremental-basedir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

This command creates a series of delta files that store the incremental changes in /var/mariadb/inc1. You can find a similar xtrabackup_checkpoints file in this directory, with the updated LSN values.

For example:

backup_type = incremental
from_lsn = 1635102
to_lsn = 1635114
last_lsn = 1635114
recover_binlog_info = 0

To perform additional incremental backups, you can then use the target directory of the previous incremental backup as the incremental base directory of the next incremental backup. For example:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/inc2/ \
   --incremental-basedir=/var/mariadb/inc1/ \
   --user=mariadb-backup --password=mypassword

Combining with --stream output

When using --stream, e.g for compression or encryption using external tools, the xtrabackup_checkpoints file containing the information where to continue from on the next incremental backup will also be part of the compressed/encrypted backup file, and so not directly accessible by default.

A directory containing an extra copy of the file can be created using the --extra-lsndir=... option though, and this directory can then be passed to the next incremental backup --incremental-basedir=..., for example:

# initial full backup
$ mariadb-backup --backup --stream=mbstream \
  --user=mariadb-backup --password=mypassword \
  --extra-lsndir=backup_base | gzip > backup_base.gz

# incremental backup
$ mariadb-backup --backup --stream=mbstream \
  --incremental-basedir=backup_base \
  --user=mariadb-backup --password=mypassword \
  --extra-lsndir=backup_inc1 | gzip > backup-inc1.gz

Preparing the Backup

Following the above steps, you have three backups in /var/mariadb: The first is a full backup, the others are increments on this first backup. In order to restore a backup to the database, you first need to apply the incremental backups to the base full backup. This is done using the --prepare command option. In MariaDB 10.1, you would also have to use the --apply-log-only option.

In MariaDB 10.2 and later, perform the following process:

First, prepare the base backup:

$ mariadb-backup --prepare \
   --target-dir=/var/mariadb/backup

Running this command brings the base full backup, that is, /var/mariadb/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:

$ mariadb-backup --prepare \
   --target-dir=/var/mariadb/backup \
   --incremental-dir=/var/mariadb/inc1

Running this command brings the base full backup, that is, /var/mariadb/backup, into sync with the changes contained in the first incremental backup.

For each remaining incremental backup, repeat the last step to bring the base full backup into sync with the changes contained in that incremental backup.

Restoring the Backup

Once you've applied all incremental backups to the base, you can restore the backup using either the --copy-back or the --move-back options. 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.

• First, stop the MariaDB Server process.

• Then, ensure that the datadir is empty.

• Then, run mariadb-backup with one of the options mentioned above:

$ mariadb-backup --copy-back \
   --target-dir=/var/mariadb/backup/

• Then, you may need to fix the file permissions.

When mariadb-backup restores a database, it preserves the file and directory privileges of the backup. However, it writes the files to disk as the user and group restoring the database. As such, 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. For example, to recursively change ownership of the files to the mysql user and group, you could execute:

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

• Finally, start the MariaDB Server process.

_{This page is licensed: CC BY-SA / Gnu FDL}

When using mariadb-backup, you have the option of performing partial backups. Partial backups allow you to choose which databases or tables to backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace.This page documents how to perform partial backups.

Backing up the Database Server

Just like with full backups, in order to back up the database, you need to run mariadb-backup with the --backup option to tell it to perform a backup and with the --target-dir option to tell it where to place the backup files. The target directory must be empty or not exist.

For a partial backup, there are a few other arguments that you can provide as well:

• To tell it which databases to backup, you can provide the --databases option.

• To tell it which databases to exclude from the backup, you can provide the --databases-exclude option.

• To tell it to check a file for the databases to backup, you can provide the --databases-file option.

• To tell it which tables to backup, you can use the --tables option.

• To tell it which tables to exclude from the backup, you can provide the --tables-exclude option.

• To tell it to check a file for specific tables to backup, you can provide the --tables-file option.

The non-file partial backup options support regex in the database and table names.

For example, to take a backup of any database that starts with the string app1_ and any table in those databases that start with the string tab_, run the following command:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --databases='app1_*' --tables='tab_*' \
   --user=mariadb-backup --password=mypassword

mariadb-backup cannot currently backup a subset of partitions from a partitioned table. Backing up a partitioned table is currently an all-or-nothing selection. See MDEV-17132 about that. If you need to backup a subset of partitions, then one possibility is that instead of using mariadb-backup, you can export the file-per-table tablespaces of the partitions.

The time the backup takes depends on the size of the databases or tables you're backing up. You can cancel the backup if you need to, as the backup process does not modify the database.

mariadb-backup writes the backup files to the target directory. If the target directory doesn't exist, then it creates it. If the target directory exists and contains files, then it raises an error and aborts.

Preparing the Backup

Just like with full backups, 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. In fact, for partial backups, the backup is not even a completely functional MariaDB data directory, so InnoDB would raise more errors than it would for full backups. This point will also be very important to keep in mind during the restore process.

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare command option.

Partial backups rely on InnoDB's transportable tablespaces. For MariaDB to import tablespaces like these, InnoDB looks for a file with a .cfg extension. For mariadb-backup to create these files, you also need to add the --export option during the prepare step.

For example, you might execute the following command:

$ mariadb-backup --prepare --export \
   --target-dir=/var/mariadb/backup/

If this operation completes without error, then the backup is ready to be restored.

mariadb-backup did not support the --export option. See MDEV-13466 about that. This means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

Restoring the Backup

The restore process for partial backups is quite different than the process for full backups. A partial backup is not a completely functional data directory. The data dictionary in the InnoDB system tablespace will still contain entries for the databases and tables that were not included in the backup.

Rather than using the --copy-back or the --move-back, each individual InnoDB file-per-table tablespace file will have to be manually imported into the target server. The process that is used to import the file will depend on whether partitioning is involved.

Restoring Individual Non-Partitioned Tables

To restore individual non-partitioned tables from a backup, find the .ibd and .cfg files for the table in the backup, and then import them using the Importing Transportable Tablespaces for Non-partitioned Tables process.

Restoring Individual Partitions and Partitioned Tables

To restore individual partitions or partitioned tables from a backup, find the .ibd and .cfg files for the partition(s) in the backup, and then import them using the Importing Transportable Tablespaces for Partitioned Tables process.

_{This page is licensed: CC BY-SA / Gnu FDL}

When using mariadb-backup, you don't necessarily need to restore every table and/or partition that was backed up. Even if you're starting from a full backup, it is certainly possible to restore only certain tables and/or partitions from the backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace. This page documents how to restore individual tables and partitions.

Preparing the Backup

Before you can restore from a backup, you first need to prepare it to make the data files consistent. You can do so with the --prepare command option.

The ability to restore individual tables and partitions relies on InnoDB's transportable tablespaces. For MariaDB to import tablespaces like these, InnoDB looks for a file with a .cfg extension. For mariadb-backup to create these files, you also need to add the --export option during the prepare step.

For example, you might execute the following command:

$ mariadb-backup --prepare --export \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

If this operation completes without error, then the backup is ready to be restored.

Note

mariadb-backup did not support the --export option to begin with. See MDEV-13466 about that. In earlier versions of MariaDB, this means that mariadb-backup could not create .cfg files for InnoDB file-per-table tablespaces during the --prepare stage. You can still import file-per-table tablespaces without the .cfg files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the .ibd files. If you have a full backup and you need to create .cfg files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the --export option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

Restoring the Backup

The restore process for restoring individual tables and/or partitions is quite different than the process for full backups.

Rather than using the --copy-back or the --move-back, each individual InnoDB file-per-table tablespace file will have to be manually imported into the target server. The process that is used to restore the backup will depend on whether partitioning is involved.

Restoring Individual Non-Partitioned Tables

To restore individual non-partitioned tables from a backup, find the .ibd and .cfg files for the table in the backup, and then import them using the Importing Transportable Tablespaces for Non-partitioned Tables process.

Restoring Individual Partitions and Partitioned Tables

To restore individual partitions or partitioned tables from a backup, find the .ibd and .cfg files for the partition(s) in the backup, and then import them using the Importing Transportable Tablespaces for Partitioned Tables process.

_{This page is licensed: CC BY-SA / Gnu FDL}

mariadb-backup makes it very easy to set up a replica using a full backup. This page documents how to set up a replica from a backup.

If you are using MariaDB Galera Cluster, then you may want to try one of the following pages instead:

• Configuring MariaDB Replication between MariaDB Galera Cluster and MariaDB Server

• Configuring MariaDB Replication between Two MariaDB Galera Clusters

Backup the Database and Prepare It

The first step is to simply take and prepare a fresh full backup of a database server in the replication topology. If the source database server is the desired replication primary, then we do not need to add any additional options when taking the full backup. For example:

$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

If the source database server is a replica of the desired primary, then we should add the --slave-info option, and possibly the --safe-slave-backup option. For example:

$ mariadb-backup --backup \
   --slave-info --safe-slave-backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariadb-backup --password=mypassword

And then we would prepare the backup as you normally would. For example:

$ mariadb-backup --prepare \
   --target-dir=/var/mariadb/backup/

Copy the Backup to the New Replica

Once the backup is done and prepared, we can copy it to the new replica. For example:

$ rsync -avP /var/mariadb/backup dbserver2:/var/mariadb/backup

Restore the Backup on the New Replica

At this point, we can restore the backup to the datadir, as you normally would. For example:

$ mariadb-backup --copy-back \
   --target-dir=/var/mariadb/backup/

And adjusting file permissions, if necessary:

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

Create a Replication User on the Primary

Before the new replica can begin replicating from the primary, we need to create a user account on the primary that the replica can use to connect, and we need to grant the user account the REPLICATION SLAVE privilege. For example:

CREATE USER 'repl'@'dbserver2' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'dbserver2';

Configure the New Replica

Before we start the server on the new replica, we need to configure it. At the very least, we need to ensure that it has a unique server_id value. We also need to make sure other replication settings are what we want them to be, such as the various GTID system variables, if those apply in the specific environment.

Once configuration is done, we can start the MariaDB Server process on the new replica.

Start Replication on the New Replica

At this point, we need to get the replication coordinates of the primary from the original backup directory.

If we took the backup on the primary, then the coordinates will be in the xtrabackup_binlog_info file. If we took the backup on another replica and if we provided the --slave-info option, then the coordinates will be in the file xtrabackup_slave_info file.

mariadb-backup dumps replication coordinates in two forms: GTID coordinates and binary log file and position coordinates, like the ones you would normally see from SHOW MASTER STATUS output. We can choose which set of coordinates we would like to use to set up replication.

For example:

mariadb-bin.000096 568 0-1-2

Regardless of the coordinates we use, we will have to set up the primary connection using CHANGE MASTER TO and then start the replication threads with START SLAVE.

GTIDs

If we want to use GTIDs, then we will have to first set gtid_slave_pos to the GTID coordinates that we pulled from either the xtrabackup_binlog_info file or the xtrabackup_slave_info file in the backup directory. For example:

$ cat xtrabackup_binlog_info
mariadb-bin.000096 568 0-1-2

And then we would set MASTER_USE_GTID=slave_pos in the CHANGE MASTER TO command. For example:

SET GLOBAL gtid_slave_pos = "0-1-2";
CHANGE MASTER TO 
   MASTER_HOST="dbserver1", 
   MASTER_PORT=3306, 
   MASTER_USER="repl",  
   MASTER_PASSWORD="password", 
   MASTER_USE_GTID=slave_pos;
START SLAVE;

File and Position

If we want to use the binary log file and position coordinates, then we would set MASTER_LOG_FILE and MASTER_LOG_POS in the CHANGE MASTER TO command to the file and position coordinates that we pulled; either the xtrabackup_binlog_info file or the xtrabackup_slave_info file in the backup directory, depending on whether the backup was taken from the primary or from a replica of the primary. For example:

CHANGE MASTER TO 
   MASTER_HOST="dbserver1", 
   MASTER_PORT=3306, 
   MASTER_USER="repl",  
   MASTER_PASSWORD="password", 
   MASTER_LOG_FILE='mariadb-bin.000096',
   MASTER_LOG_POS=568;
START SLAVE;

Check the Status of the New Replica

We should be done setting up the replica now, so we should check its status with SHOW SLAVE STATUS. For example:

SHOW SLAVE STATUS\G

_{This page is licensed: CC BY-SA / Gnu FDL}

Files Included in Backup

mariadb-backup backs up the files listed below.

InnoDB Data Files

mariadb-backup backs up the following InnoDB data files:

• InnoDB system tablespace

• InnoDB file-per-table tablespaces

MyRocks Data Files

Other Data Files

mariadb-backup also backs up files with the following extensions:

• frm

• isl

• MYD

• MYI

• MAD

• MAI

• MRG

• TRG

• TRN

• ARM

• ARZ

• CSM

• CSV

• opt

• par

Files Excluded From Backup

mariadb-backup does not back up the files listed below.

• InnoDB Temporary Tablespaces

• Binary logs

• Relay logs

_{This page is licensed: CC BY-SA / Gnu FDL}

mariadb-backup creates the following files:

backup-my.cnf

During the backup, any server options relevant to mariadb-backup are written to the backup-my.cnf option file, so that they can be re-read later during the --prepare stage.

ib_logfile0

In MariaDB 10.2.10 and later, mariadb-backup creates an empty InnoDB redo log file called ib_logfile0 as part of the --prepare stage. This file has 3 roles:

1. In the source server, ib_logfile0 is the first (and possibly the only) InnoDB redo log file.

2. In the non-prepared backup, ib_logfile0 contains all of the InnoDB redo log copied during the backup. Previous versions of mariadb-backup would use a file called xtrabackup_logfile for this.

3. During the --prepare stage, ib_logfile0 would previously be deleted. Now during the --prepare stage, ib_logfile0 is initialized as an empty InnoDB redo log file. That way, if the backup is manually restored, any pre-existing InnoDB redo log files would get overwritten by the empty one. This helps to prevent certain kinds of known issues. For example, see mariadb-backup Overview: Manual Restore with Pre-existing InnoDB Redo Log files.

xtrabackup_logfile

In MariaDB 10.2.9 and before, mariadb-backup creates xtrabackup_logfile to store the InnoDB redo log, In later versions, ib_logfile0 is created instead.

xtrabackup_binlog_info

This file stores the binary log file name and position that corresponds to the backup.

This file also stores the value of the gtid_current_pos system variable that correspond to the backup.

For example:

mariadb-bin.000096 568 0-1-2

The values in this file are only guaranteed to be consistent with the backup if the --no-lock option was not provided when the backup was taken.

xtrabackup_binlog_pos_innodb

This file is created by mariadb-backup to provide the binary log file name and position when the --no-lock option is used. It can be used instead of the file "xtrabackup_binlog_info" to obtain transactionally consistent binlog coordinates from the backup of a master server with the --no-lock option to minimize the impact on a running server.

Whenever a transaction is committed inside InnoDB when the binary log is enabled, the corresponding binlog coordinates are written to the InnoDB redo log along with the transaction commit. This allows one to restore the binlog coordinates corresponding to the last commit done by InnoDB along with a backup.

The limitation of using "xtrabackup_binlog_pos_innodb" with the "--no-lock" option is that no DDL or modification of non-transactional tables should be done during the backup. If the last event in the binlog is a DDL/non-transactional update, the coordinates in the file "xtrabackup_binlog_pos_innodb" will be too old. But as long as only InnoDB updates are done during the backup, the coordinates will be correct.

xtrabackup_checkpoints

The xtrabackup_checkpoints file contains metadata about the backup.

For example:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1635102
last_lsn = 1635102
recover_binlog_info = 0

See below for a description of the fields.

If the --extra-lsndir option is provided, then an extra copy of this file will be saved in that directory.

backup_type

If the backup is a non-prepared full backup or a non-prepared partial backup, then backup_type is set to full-backuped.

If the backup is a non-prepared incremental backup, then backup_type is set to incremental.

If the backup has already been prepared, then backup_type is set to log-applied.

from_lsn

If backup_type is full-backuped, then from_lsn has the value of 0.

If backup_type is incremental, then from_lsn has the value of the log sequence number (LSN) at which the backup started reading from the InnoDB redo log. This is internally used by mariadb-backup when preparing incremental backups.

This value can be manually set during an incremental backup with the --incremental-lsn option. However, it is generally better to let mariadb-backup figure out the from_lsn automatically by specifying a parent backup with the --incremental-basedir option.

to_lsn

to_lsn has the value of the log sequence number (LSN) of the last checkpoint in the InnoDB redo log. This is internally used by mariadb-backup when preparing incremental backups.

last_lsn

last_lsn has the value of the last log sequence number (LSN) read from the InnoDB redo log. This is internally used by mariadb-backup when preparing incremental backups.

xtrabackup_info

The xtrabackup_info file contains information about the backup. The fields in this file are listed below.

If the --extra-lsndir option is provided, then an extra copy of this file will be saved in that directory.

uuid

If a UUID was provided by the --incremental-history-uuid option, then it will be saved here. Otherwise, this will be the empty string.

name

If a name was provided by the --history or the ---incremental-history-name options, then it will be saved here. Otherwise, this will be the empty string.

tool_name

The name of the mariadb-backup executable that performed the backup. This is generally mariadb-backup.

tool_command

The arguments that were provided to mariadb-backup when it performed the backup.

tool_version

The version of mariadb-backup that performed the backup.

ibbackup_version

The version of mariadb-backup that performed the backup.

server_version

The version of MariaDB Server that was backed up.

start_time

The time that the backup started.

end_time

The time that the backup ended.

lock_time

The amount of time that mariadb-backup held its locks.

binlog_pos

This field stores the binary log file name and position that corresponds to the backup.

This field also stores the value of the gtid_current_pos system variable that correspond to the backup.

The values in this field are only guaranteed to be consistent with the backup if the --no-lock option was not provided when the backup was taken.

innodb_from_lsn

This is identical to from_lsn in xtrabackup_checkpoints.

If the backup is a full backup, then innodb_from_lsn has the value of 0.

If the backup is an incremental backup, then innodb_from_lsn has the value of the log sequence number (LSN) at which the backup started reading from the InnoDB redo log.

innodb_to_lsn

This is identical to to_lsn in xtrabackup_checkpoints.

innodb_to_lsn has the value of the log sequence number (LSN) of the last checkpoint in the InnoDB redo log.

partial

If the backup is a partial backup, then this value will be Y.

Otherwise, this value will be N.

incremental

If the backup is an incremental backup, then this value will be Y.

Otherwise, this value will be N.

format

This field's value is the format of the backup.

If the --stream option was set to xbstream, then this value will be xbstream.

If the --stream option was not provided, then this value will be file.

compressed

If the --compress option was provided, then this value will be compressed.

Otherwise, this value will be N.

xtrabackup_slave_info

If the --slave-info option is provided, then this file contains the CHANGE MASTER command that can be used to set up a new server as a slave of the original server's master after the backup has been restored.

mariadb-backup does not check if GTIDs are being used in replication. It takes a shortcut and assumes that if the gtid_slave_pos system variable is non-empty, then it writes the CHANGE MASTER command with the MASTER_USE_GTID option set to slave_pos. Otherwise, it writes the CHANGE MASTER command with the MASTER_LOG_FILE and MASTER_LOG_POS options using the master's binary log file and position. See MDEV-19264 for more information.

xtrabackup_galera_info

If the --galera-info option is provided, then this file contains information about a Galera Cluster node's state.

The file contains the values of the wsrep_local_state_uuid and wsrep_last_committed status variables.

The values are written in the following format:

wsrep_local_state_uuid:wsrep_last_committed

For example:

d38587ce-246c-11e5-bcce-6bbd0831cc0f:1352215

<table>.delta

If the backup is an incremental backup, then this file contains changed pages for the table.

<table>.delta.meta

If the backup is an incremental backup, then this file contains metadata about <table>.delta files. The fields in this file are listed below.

page_size

This field contains either the value of innodb_page_size or the value of the KEY_BLOCK_SIZE table option for the table if the ROW_FORMAT table option for the table is set to COMPRESSED.

zip_size

If the ROW_FORMAT table option for this table is set to COMPRESSED, then this field contains the value of the compressed page size.

space_id

This field contains the value of the table's space_id.

_{This page is licensed: CC BY-SA / Gnu FDL}

mariadb-backup supports streaming to stdout with the --stream=xbstream option. This option allows easy integration with popular encryption and compression tools. Below are several examples.

Encrypting and Decrypting Backup With openssl

The following example creates an AES-encrypted backup, protected with the password "mypass" and stores it in a file "backup.xb.enc":

mariadb-backup --user=root --backup --stream=xbstream  | openssl  enc -aes-256-cbc -k mypass > backup.xb.enc

To decrypt and unpack this backup into the current directory, the following command can be used:

openssl  enc -d -aes-256-cbc -k mypass -in backup.xb.enc | mbstream -x

Compressing and Decompressing Backup With gzip

This example compresses the backup without encrypting:

mariadb-backup --user=root --backup --stream=xbstream | gzip > backupstream.gz

We can decompress and unpack the backup as follows:

gunzip -c backupstream.gz | mbstream -x

Compressing and Encrypting Backup, Using gzip and openssl

This example adds a compression step before the encryption, otherwise looks almost identical to the previous example:

mariadb-backup --user=root --backup --stream=xbstream | gzip | openssl  enc -aes-256-cbc -k mypass > backup.xb.gz.enc

We can decrypt, decompress and unpack the backup as follow (note gzip -d in the pipeline):

openssl  enc -d -aes-256-cbc -k mypass -in backup.xb.gz.enc |gzip -d| mbstream -x

Compressing and Encrypting with 7Zip

7zip archiver is a popular utility (especially on Windows) that supports reading from standard output, with the --si option, and writing to stdout with the -so option, and can thus be used together with mariadb-backup.

Compressing backup with the 7z command line utility works as follows:

mariadb-backup --user=root --backup --stream=xbstream | 7z a -si backup.xb.7z

Uncompress and unpack the archive with

7z e backup.xb.7z -so |mbstream -x

7z also has builtin AES-256 encryption. To encrypt the backup from the previous example using password SECRET, add -pSECRET to the 7z command line.

Compressing with zstd

Compress

mariadb-backup --user=root --backup --stream=xbstream  | zstd - -o backup.xb.zst -f -1

Decompress , unpack

zstd -d backup.xbstream.zst -c | mbstream -x

Encrypting With GPG

Encryption

mariadb-backup --user=root --backup --stream=xbstream | gpg -c --passphrase SECRET --batch --yes -o backup.xb.gpg

Decrypt, unpack

gpg --decrypt --passphrase SECRET --batch --yes  backup.xb.gpg | mbstream -x

Interactive Input for Passphrases

Most of the described tools also provide a way to enter a passphrase interactively (although 7zip does not seem to work well when reading input from stdin). Please consult documentation of the tools for more info.

Writing extra status files

By default files like xtrabackup_checkpoints are also written to the output stream only, and so would not be available for taking further incremental backups without prior extraction from the compressed or encrypted stream output file.

To avoid this these files can additionally be written to a directory that can then be used as input for further incremental backups using the --extra-lsndir=... option.

See also e.g: Combining incremental backups with streaming output

_{This page is licensed: CC BY-SA / Gnu FDL}

This is a description of the different stages in mariadb-backup, what they do and why they are needed.

Note that a few items are marked with TODO; these are things we are working on and will be in next version of mariadb-backup.

Execution Stages

Initialization Phase

• Connect to mysqld instance, find out important variables (datadir ,InnoDB pagesize, encryption keys, encryption plugin etc)

• Scan the database directory, datadir, looking for InnoDB tablespaces, load the tablespaces (basically, it is an “open” in InnoDB sense)

• If --lock-ddl-per-table is used:

  • Do MDL locks, for InnoDB tablespaces that we want to copy. This is to ensure that there are no ALTER, RENAME , TRUNCATE or DROP TABLE on any of the tables that we want to copy.

  • This is implemented with:

BEGIN
FOR EACH affected TABLE
SELECT 1 FROM <TABLE> LIMIT 0

• If lock-ddl-per-table is not done, then mariadb-backup would have to know all tables that were created or altered during the backup. See MDEV-16791.

Redo Log Handling

Start a dedicated thread in mariadb-backup to copy InnoDB redo log (ib_logfile*).

• This is needed to record all changes done while the backup is running. (The redo log logically is a single circular file, split into innodb_log_files_in_group files.)

• The log is also used to see detect if any truncate or online alter tables are used.

• The assumption is that the copy thread will be able to keep up with server. It should always be able keep up, if the redo log is big enough.

Copy-phase for InnoDB Tablespaces

• Copy all selected tablespaces, file by file, in dedicated threads in mariadb-backup without involving the mysqld server.

• This is special “careful” copy, it looks for page-level consistency by checking the checksum.

• The files are not point-in-time consistent as data may change during copy.

• The idea is that InnoDB recovery would make it point-in-time consistent.

• Copy Aria log files (TODO)

Create a Consistent Backup Point

• Execute FLUSH TABLE WITH READ LOCK. This is default, but may be omitted with the -–no-lock parameter. The reason why FLUSH is needed is to ensure that all tables are in a consistent state at the exact same point in time, independent of storage engine.

• If --lock-ddl-per-table is used and there is a user query waiting for MDL, the user query will be killed to resolve a deadlock. Note that these are only queries of type ALTER, DROP, TRUNCATE or RENAME TABLE. (MDEV-15636)

Last Copy Phase

• Copy .frm, MyISAM, Aria and other storage engine files

• If MyRocks is used, create rocksdb checkpoint via "set rocksdb_create_checkpoint=$rocksdb_data_dir/mariadb-backup_rocksdb_checkpoint " command. The result of it is a directory with hardlinks to MyRocks files. Copy the checkpoint directory to the backup (or create hardlinks in backup directory is on the same partition as data directory). Remove the checkpoint directory.

• Copy tables that were created while the backup was running and do rename files that were changed during backup (since MDEV-16791)

• Copy the rest of InnoDB redo log, stop redo-log-copy thread

• Copy changes to Aria log files (They are append only, so this is easy to do) (TODO)

• Write some metadata info (binlog position)

Release Locks

• If FLUSH TABLE WITH READ LOCK was done:

  • execute: UNLOCK TABLES

• If --lock-ddl-per-table was done:

  • execute COMMIT

Handle Log Tables (TODO)

• If log tables exists:

  • Take MDL lock for log tables

  • Copy part of log tables that wasn't copied before

  • Unlock log tables

Notes

• If FLUSH TABLE WITH READ LOCK is not used, then only InnoDB tables will be consistent (not the privilege tables in the mysql database or the binary log). The backup point depends on the content of the redo log within the backup itself.

_{This page is licensed: CC BY-SA / Gnu FDL}

The BACKUP STAGE commands are a set of commands to make it possible to make an efficient external backup tool. How mariadb-backup uses these commands depends on whether you are using the version that is bundled with MariaDB Community Server or the version that is bundled with MariaDB Enterprise Server.

mariadb-backup and BACKUP STAGE Commands in MariaDB Community Server

The BACKUP STAGE commands are supported. However, the version of mariadb-backup that is bundled with MariaDB Community Server does not yet use the BACKUP STAGE commands in the most efficient way. mariadb-backup simply executes the following BACKUP STAGE commands to lock the database:

BACKUP STAGE START;
BACKUP STAGE BLOCK_COMMIT;

When the backup is complete, it executes the following BACKUP STAGE command to unlock the database:

BACKUP STAGE END;

If you would like to use a version of mariadb-backup that uses the BACKUP STAGE commands in the most efficient way, then your best option is to use MariaDB Backup that is bundled with MariaDB Enterprise Server.

Tasks Performed Prior to BACKUP STAGE in MariaDB Community Server

• Copy some transactional tables.

  • InnoDB (i.e. ibdataN and file extensions .ibd and .isl)

• Copy the tail of some transaction logs.

  • The tail of the InnoDB redo log (i.e. ib_logfileN files) will be copied for InnoDB tables.

BACKUP STAGE START in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not currently perform any tasks in the START stage.

BACKUP STAGE FLUSH in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not currently perform any tasks in the FLUSH stage.

BACKUP STAGE BLOCK_DDL in MariaDB Community Server