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 and storage engines, each table has an equivalent set of files.

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

Backup Tools

mariadb-backup

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

Storage Engines and Backup Types

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

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

Non-Blocking Backups

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

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

• MariaDB Backup includes 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 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

Full backups produced using MariaDB 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 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 --prepare on a full backup synchronizes the tablespaces, ensuring they are point-in-time consistent.

• Running --prepare on an incremental backup synchronizes the tablespaces and applies the updated data into the previous full backup.

• Running --prepare

Restore Requires Empty Data Directory

For MariaDB Backup to safely restore data from full and incremental backups, the data directory must be empty. When MariaDB Backup restores from a backup using --copy-back or --move-back, it copies or moves the backup files into the MariaDB Server data directory.

Creating the Backup User

When MariaDB Backup performs a backup operation, it connects to the running MariaDB Server to manage locks and backup staging that prevent the Server from writing to a file while being read. It is recommended that a dedicated user be created and authorized to perform backups:

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, , , and are not, and need extra parameters to be recreated explicitly (for example, --routines and --events). and are however also part of the system tables (for example ).

InnoDB Logical Backups

InnoDB uses the , 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 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 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 system variable can be set to ON. To restore it, the system variable can be set to ON.

mariadb-dump Examples

Backing up a single database

Restoring or loading the database

See the page for detailed syntax and examples.

mariadb-hotcopy

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

mariadb-hotcopy Examples

Percona XtraBackup

Percona XtraBackup is not supported in MariaDB. is the recommended backup method to use instead of Percona XtraBackup. See for more information.

is a tool for performing fast, hot backups. It was designed specifically for databases, but can be used with any storage engine (although not with and ). 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 client, execute . The client must remain open.

• From a shell, execute mount vxfs snapshot

• The client can execute .

LVM

Widely-used physical backup method, using a Perl script as a wrapper. See for more information.

Percona TokuBackup

For details, see:

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

• (mariadb.com blog)

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

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 .

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.

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.

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:

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.

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

Files Included in Backup

mariadb-backup backs up the files listed below.

InnoDB Data Files

mariadb-backup backs up the following InnoDB data files:

MyRocks Data Files

Other Data Files

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

• frm

• isl

• MYD

Files Excluded From Backup

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

_{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 . This page documents how to restore individual tables and partitions.

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.

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

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.

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.

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:

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:

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:

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:

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:

Using the Backup History Table

Alternatively, you can use the backup history table to manage your backup chain. This allows you to reference the previous backup by a logical name instead of a directory path.

1. Create the Base Backup: Take a full backup using the --history option.

2. Create the Incremental Backup: Use --incremental-history-name to specify the base backup's name. It is recommended to use --history again to record this new incremental backup.

Combining With --stream Output

When using --stream, for instance 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:

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.

Perform the following process:

First, prepare the base 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:

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

• Then, ensure that the datadir is empty.

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

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

• Finally, .

_{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.

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

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

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

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 are killed to resolve a deadlock. Note that these are only queries of type ALTER, DROP

Last Copy Phase

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

• If MyRocks is used, create rocksdb checkpoint via the 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.

Release Locks

• If FLUSH TABLE WITH READ LOCK was done:

  • execute: UNLOCK TABLES

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

Handle Log Tables (TODO)

• If log tables exists:

  • Take MDL lock for log tables

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

Notes

• If FLUSH TABLE WITH READ LOCK is not used, only InnoDB tables are 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}

This method is to solve a flaw with mariadb-backup; it cannot do single database restores from a full backup easily. There is a , but it's a manual process which is fine for a few tables but if you have hundreds or even thousands of tables then it would be impossible to do quickly.

We can't just move the data files to the datadir as the tables are not registered in the engines, so the database will error. Currently, the only effective method is to a do full restore in a test database and then dump the database that requires restoring or running a partial backup.

This has only been tested with InnoDB. Also, if you have stored procedures or triggers then these will need to be deleted and recreated.

The BACKUP STAGE statements make it possible to make an efficient external backup tool. How mariadb-backup uses these statements 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.

BACKUP STAGE in MariaDB Community Server

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

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

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.

BACKUP STAGE START in MariaDB Community Server

mariadb-backup from MariaDB Community Server does not 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

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

BACKUP STAGE BLOCK_COMMIT in MariaDB Community Server

mariadb-backup from MariaDB Community Server performs the following tasks in the BLOCK_COMMIT stage:

• Copy other files.

  • i.e. file extensions .frm, .isl, .TRG, .TRN, .opt, .par

BACKUP STAGE END in MariaDB Community Server

mariadb-backup from MariaDB Community Server performs the following tasks in the END stage:

• Copy the MyRocks checkpoint into the backup.

BACKUP STAGE in MariaDB Enterprise Server

The following sections describe how the MariaDB Backup version of mariadb-backup that is bundled with MariaDB Enterprise Server uses each statement in an efficient way.

BACKUP STAGE START in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the START stage:

• Copy all transactional tables.

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

  • Aria (i.e.

BACKUP STAGE FLUSH in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the FLUSH stage:

• Copy all non-transactional tables that are not in use. This list of used tables is found with SHOW OPEN TABLES.

  • MyISAM (i.e. file extensions .MYD and .MYI)

BACKUP STAGE BLOCK_DDL in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the BLOCK_DDL stage:

• Copy other files.

  • i.e. file extensions .frm, .isl, .TRG, .TRN, .opt, .par

BACKUP STAGE BLOCK_COMMIT in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the BLOCK_COMMIT stage:

• Create a MyRocks checkpoint using the rocksdb_create_checkpoint system variable.

• Copy changes to system log tables.

  • mysql.general_log

BACKUP STAGE END in MariaDB Enterprise Server

mariadb-backup from MariaDB Enterprise Server performs the following tasks in the END stage:

• Copy the MyRocks checkpoint into the backup.

_{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

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:

Using --history with Partial Backups

You can use the --history option with a partial backup to log the operation in the history table for auditing purposes.

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:

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

mariadb-backup did not support the --export option. See 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 partitions in the backup, and then import them using the process.

Restoring Individual 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 Command-Line Client, drop the table you are going to restore:

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

3. Modify the table to discard the tablespace:

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

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

mariadb-backup creates the following files:

Replication can be used to support the backup strategy.

Replication is most commonly used to support backups as follows:

• A primary server replicates to a replica

• Backups are then run off the replica without any impact on the primary.

Backups can have a significant effect on a server, and a high-availability primary may not be able to be stopped, locked or simply handle the extra load of a backup. Running the backup from a replica has the advantage of being able to shutdown or lock the replica and perform a backup without any impact on the primary server.

Note that when backing up off a replica server, it is important to ensure that the servers keep the data in sync. See for example for a situation when identical statements can result in different data on a replica and a primary.

Setting Up a Dedicated Backup Replica

To set up a replica specifically for backup purposes, you will need to configure outbound replication from your primary server to the replica. Here is a step-by-step guide to establishing this connection securely:

1. Create a User for Replication

On the primary server, create a dedicated user for replication and grant it the necessary privileges:

(Optional: Confirm the grants by executing SHOW GRANTS FOR 'backup_replica'@'replica_ip_or_hostname';)

2. Obtain the GTID Position

On the primary server, obtain the current GTID position from which the replica should start replicating. If you want to start from the most recent transaction, query the gtid_current_pos:

3. Configure the GTID Position on the Replica

On the replica server, configure the starting GTID position using the value obtained in the previous step:

SQL

4. Configure Replication

On the replica server, configure the connection to the primary server using the CHANGE MASTER TO statement:

(Note: Adjust the MASTER_PORT, MASTER_SSL, and add MASTER_SSL_CA parameters as necessary depending on your network and security configuration).

5. Start Replication

On the replica server, start the replication process:

6. Check Replication Status

On the replica server, verify that replication is running smoothly:

Ensure that both Slave_IO_Running and Slave_SQL_Running are Yes. Once the replica is fully synced, you can safely pause it or run your backup tools (mariadb-backup or mariadb-dump) directly against this replica without affecting the primary server's performance.

See Also

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

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.

• with Galera Cluster.

• Microsoft Windows support.

Supported Features in MariaDB Enterprise Backup

Backup Types

mariadb-backup supports various type of backups (and restores from those backups), documented on separate pages:

• Restoring ,

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. Many Linux distributions provide MariaDB software "out of the box", including mariadb-backup. If your Linux distribution doesn't, however, you can install using a MariaDB repository.

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 .

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

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 . For example:

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:

Installing on Windows

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

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

Usage

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

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

Options

Options supported by mariadb-backup can be found on the 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 from :

Client Option Groups

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

Backup History Table

mariadb-backup can optionally track your backup operations in a database table. This provides a centralized audit log and allows you to automate incremental backups by referencing the logical name of the previous backup instead of managing file paths.

Table Location and Schema Changes (MariaDB 10.11):

• MariaDB 10.11 and later: The history table is mysql.mariadb_backup_history and uses the InnoDB storage engine (transactional).

• MariaDB 10.10 and earlier: The history table is PERCONA_SCHEMA.xtrabackup_history and uses the CSV storage engine.

Authentication and Privileges

mariadb-backup needs to authenticate with the database server when it performs a backup operation (i.e. when the 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.

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

CONNECTION ADMIN is also required where is greater than 0, and isn't applied in order to queries.

REPLICA MONITOR (or alias SLAVE MONITOR) is also required where or is specified.

To use the option(or the incremental history options), the backup user requires specific privileges on the history table.

For Upgrading to 10.11 (One-Time Migration)

If upgrading from an older version, mariadb-backup will attempt to migrate the old table to the new location on the first run. The backup user needs privileges to move and modify the old table:

Alternatively, you can perform this migration manually before running the backup:

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

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

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 .

mariadb-backup will query the server to determine which 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 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 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 for more information.

Files Backed up by mariadb-backup

mariadb-backup backs up many different files in order to perform its backup operation. See 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 for a list of these files.

Binary Logs

mariadb-backup can store the binary log position in the backup. See . 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

No Default Data Directory

mariadb-backup defaults to the server's default datadir value. See 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:

mariadb-backup throws an error and aborts when this error is encountered. See for more information.

When this error is encountered, one solution is to explicitly specify a value for the option either on the command line or in one of the supported s in an . For example:

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

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:

See Also

• (video)

• (video)

• (video)

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

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

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

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

Nonblocking 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

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

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.

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:

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

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

Once this is done, start MariaDB Enterprise Server:

When the Server starts, it works from the restored data directory.

Incremental Backup and Restore

Full backups of large data-sets can be time-consuming and resource-intensive. MariaDB 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