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

Files that mariadb-backup generates during 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 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 option to update the base backup with the deltas from an incremental backup.

Once the backup is ready, you can use the or the 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 are performed, and other stages of crash recovery are 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 option.

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 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 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 or commands to restore the database.

For more information, see and .

--binlog-info

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

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.

Currently, the LOCKLESS option depends on features unsupported by MariaDB Server. See the description of the 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.

--compress

Defines the compression algorithm for backup files.

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

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

--compress-chunk-size

Defines the working buffer size for compression threads.

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.

To further configure backup compression, see the and options.

--compress-threads

Defines the number of threads to use in compression.

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.

To further configure backup compression, see the and 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.

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

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

--databases

Defines the databases and tables you want to back up.

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.

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 can in the normal mode.

--databases-exclude

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

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.

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

If a backup is a partial backup, then mariadb-backup records 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.

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

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, listing the databases and tables for a backup in a file called main-backup:

If a backup is a partial backup, mariadb-backup records that detail in the xtrabackup_info file.

-h, --datadir

Defines the path to the database root.

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.

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

Then, decompress the backup:

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

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.

--defaults-file

Defines the path to the default option file.

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.

--defaults-group

Defines the option group to read in the option file.

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:

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

See and 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 did not support the --export option. See 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.

When using the --backup 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.

This is especially useful 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. Pass in the --extra-lsndir of the previous backup as --incremental-basedir .

--force-non-empty-directories

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

When using mariadb-backup with the --copy-back or --move-back 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.

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.

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.

--ftwrl-wait-threshold

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

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.

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

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

mariadb-backup exits if it can't acquire the global lock after waiting for the configured number of seconds.

The --ftwrl-wait-timeout option specifies the maximum time that mariadb-backup will wait to obtain the global lock required to begin a consistent backup.

If the lock cannot be obtained within the configured timeout, the backup process fails.

This option helps avoid failures caused by long-running MariaDB queries that block backup locks.

Example Errors

When the timeout is not set appropriately, backups may fail with messages such as:

or

Example log excerpt:

Originally, mariadb-backup could wait indefinitely for the lock. Starting with the fix for MDEV-20230:

• The --ftwrl-wait-timeout option also ensures mariadb backup exits gracefully if the lock cannot be obtained within the timeout period.

• This prevents backups from hanging when lock acquisition is blocked by long-running queries.

When to Use

Use --ftwrl-wait-timeout when:

• Your workload includes long-running queries (for example, ALTER TABLE or large INSERT batches).

• Backups sometimes fail with lock wait timeout errors.

• You want mariadb-backup to either wait longer for the lock or exit cleanly if it cannot be obtained.

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

--history

-H, --host

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

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

Warning: No Remote Backups. This option does not allow you to back up a remote server. mariabackup must be run on the same server where the database files reside. The --host option is used only to establish the client connection for managing locks and retrieving metadata. The actual data files are always read from the local filesystem. Attempting to use this option to back up a remote host will result in a backup of the local machine's data, associated with the remote machine's binary log coordinates.

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

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

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

--incremental-basedir

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

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

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

--incremental-dir

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

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.

If a backup is a incremental backup, then mariadb-backup records 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 for more information.

--incremental-history-name

Defines a logical name for the backup.

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.

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

mariadb-backup also records this in the xtrabackup_info file.

--incremental-history-uuid

Defines a UUID for the backup.

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.

The table it uses is named PERCONA_SCHEMA.xtrabackup_history, but expect that name to change in future releases. See for more information.

Table Name and Schema Changes (MariaDB 10.11):

• MariaDB 10.11 and later: Uses mysql.mariadb_backup_history (InnoDB).

• MariaDB 10.10 and earlier: Uses PERCONA_SCHEMA.xtrabackup_history (CSV).

mariadb-backup also records this in the xtrabackup_info file.

--incremental-lsn

Defines the sequence number for incremental backups.

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.

--innobackupex

Use to enable innobackupex mode, which is a compatibility mode.

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

--innodb-autoextend-increment

Defines the increment in megabytes for auto-extending the size of tablespace 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 set the increment in megabytes for automatically extending the size of tablespace data file in InnoDB.

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

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.

--innodb-checksum-algorithm

innodb_checksum_algorithm has been removed.

--innodb-data-file-path

Defines the path to individual data 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 define the path to InnoDB data files. Each path is appended to the --innodb-data-home-dir option.

--innodb-data-home-dir

Defines the home directory for InnoDB data 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 define the path to the directory containing InnoDB data files. You can specific the files using the --innodb-data-file-path option.

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

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

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.

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

--innodb-io-capacity

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

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.

--innodb-log-buffer-size

The size of the buffer that will be used for reading log during mariadb-backup --prepare. Ignored when using --innodb-log-file-mmap.

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

--innodb-log-checkpoint-now

At the start of a backup, instruct the server to write out all modified pages to the data files, to minimize the size of the ib_logfile0 that needs to be copied.

--innodb-log-file-mmap

MariaDB starting with 10.11.10, 11.4.4: When this option is enabled, mariadb-backup will read the ib_logfile0 via a memory mapping, rather than by reading into a separately allocated buffer of --innodb-log-buffer-size.

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

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.

--innodb-max-dirty-pages-pct

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

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.

--innodb-open-files

Defines the number of files kept open at a time.

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.

--innodb-page-size

Defines the universal 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.

--innodb-read-io-threads

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

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.

--innodb-undo-directory

Defines the directory for the undo tablespace 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 define the path to the directory where you want MariaDB to store the undo tablespace on InnoDB tables. The path can be absolute.

--innodb-undo-tablespaces

Defines the number of undo tablespaces to use.

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.

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

--innodb-write-io-threads

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

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.

--kill-long-queries-timeout

Defines the timeout for blocking queries.

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.

--kill-long-query-type

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

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.

--lock-ddl-per-table

Prevents DDL for each table to be backed up by acquiring MDL lock on that.

--log

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

--log-bin

Defines the base name for the log sequence.

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.

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.

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

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

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

--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 , , and as MariaDB now always uses backup locks for better performance. See .

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

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.

--open-files-limit

Defines the maximum number of file descriptors.

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

--parallel

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

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.

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.

--plugin-dir

Defines the directory for server plugins.

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.

--plugin-load

The option has been removed.

-P, --port

Defines the server port to connect to.

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.

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

Once the backup is ready, you can use the --copy-back or the --move-back options 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.

--print-param

Prints the MariaDB Server options needed for copy-back.

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

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

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.

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

Deprecated and has no effect from , , and as rsync will not work on tables that are in use. See .

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

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.

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.

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

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

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

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

--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 statement 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 are 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 statement with the MASTER_USE_GTID option set to slave_pos. Otherwise, it writes the CHANGE MASTER statement with the MASTER_LOG_FILE and MASTER_LOG_POS options using the primary's binary log file and position. See for more information.

-S, --socket

Defines the socket for connecting to local database.

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.

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

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

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:

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

The directory specified by this option needs to be run through the 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:

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

This option implies the --ssl option.

--ssl-cipher

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

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

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:

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

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:

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

The directory specified by this option needs to be run through the 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:

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

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:

--stream

Streams backup files to stdout.

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.

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

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

--tables

Defines the tables you want to include in the backup.

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 (regex). To define the tables you want to exclude from the backup, see the --tables-exclude option.

In the example, nodes_* matches tables named nodes, nodes_, nodes__, and so forth, because * means zero or more occurrences of the previous character (_).

If instead you want to back up all tables whose names start with nodes, the regular expression is ^nodes., and to exclude tables starting with nodes_tmp, the expression is ^nodes_tmp.. (Notice the trailing period (.); it means zero or more occurrences of characters following nodes.) The command looks like this:

In that example, some of the tables included via the --tables option are excluded by --tables-excludes. That works because --tables-exclude takes precedence over --tables.

You can specify multiple table name regex patterns as a comma-separated list, for both the --tables and the --tables-exclude options.

The following command backs up all tables in the test1 and test2 databases, except the exclude_table table in the test2 database, and stores the backup files under /path/to/backups/:

If a backup is a partial backup, mariadb-backup records that detail in the xtrabackup_info file.

--tables-exclude

Defines the tables you want to exclude from the backup.

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.

If a backup is a partial backup, mariadb-backup records that detail in the xtrabackup_info file.

--tables-file

Defines path to file with tables for backups.

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.

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.

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 does not create the full path recursively, i.e. at least parent directory if the --target-dir must exist.

--throttle

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

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

--tls-version

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

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

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

-t, --tmpdir

Defines path for temporary files.

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.

--use-memory

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

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

--user

Defines the username for connecting to the MariaDB Server.

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 , , , , , , , if the --user option is omitted, the user name is detected from the OS.

--verbose

Displays verbose output.

--version

Prints the mariadb-backup version information to stdout.

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

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.

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

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 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 partitions in the backup, and then import them using the process.

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

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}

Files Included in Backup

mariadb-backup backs up the files listed below.

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

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

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:

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, it creates it. If the target directory exists and contains files, it raises an error and aborts.

Here is an example backup directory:

Using the Backup History Feature

You can optionally use the --history option to record metadata about your full backup in the database. This creates a centralized log and allows future incremental backups to reference this full backup by name instead of by directory path.

• Privileges: The backup user requires INSERT, CREATE, and ALTER privileges on the history table (mysql.mariadb_backup_history in MariaDB 10.11+, or PERCONA_SCHEMA.xtrabackup_history in older versions).

• Failure Case: If the user lacks privileges, the backup will complete the file copy process but will fail at the final step with an INSERT command denied error.

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.

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 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, 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 rsync. For example, you could also execute the following to restore the backup:

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

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:

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

Execution Stages

The 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 commands are supported. However, the version of mariadb-backup that is bundled with MariaDB Community Server does not yet use the BACKUP STAGE

This method is to solve a flaw with mariadb-backup; it cannot do single database restores from a full backup easily. There is a blog post that details a way to do this, 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.

Some of the issues that this method overcomes:

• Tables not registered in the InnoDB engine so will error when you try to select from a table if you move the data files into the datadir

• Tables with foreign keys need to be created without keys, otherwise it will error when you discard the tablespace

Single Node

Below is the process to perform a single database restore.

Firstly, we will need the table structure from a mariadb-dump backup with the --no-data option. I recommend this is done at least once per day or every six hours via a cronjob. As it is just the structure, it are very fast.

Using SED to return only the table structure we require, then use vim or another text editor to make sure nothing is left.

Prepare the backup with any incremental-backup-and-restores that you have, and then run the following on the full backup folder using the --export option to generate files with .cfg extensions which InnoDB will look for.

Once we have done these steps, we can then import the table structure. If you have used the --all-databases option, then you will need to either use SED or open it in a text editor and export out tables that you require. You will also need to log in to the database and create the database if the dump file doesn't. Run the following command below:

Once the structure is in the database, we have now registered the tables to the engine. Next, we will run the following statements in the information_schema database, to export statements to import/discard table spaces and drop and create foreign keys which we will use later. (edit the CONSTRAINT_SCHEMA and TABLE_SCHEMA WHERE clause to the database you are restoring. Also, add the following lines after your SELECT and before the FROM to have MariaDB export the files to the OS)

The following are the statements that we will need later.

Once we have run those statements, and they have been exported to a Linux directory or copied from a GUI interface.

Run the ALTER DROP KEYS statements in the database.

Once completed, run the DROP TABLE SPACE statements in the database:

Exit out the database and change into the directory of the full backup location. Run the following commands to copy all the .cfg and .ibd files to the datadir such as /var/lib/mysql/testdatabase (change the datadir location if needed). Learn more about files that mariadb-backup generates with files-created-by-mariadb-backup.

After moving the files, it is very important that MySQL is the owner of the files, otherwise it won't have access to them and will error when we import the tablespaces.

Run the import table spaces statements in the database.

Run the add key statements in the database

We have successfully restored a single database. To test that this has worked, we can do a basic check on some tables.

Replica nodes

If you have a primary-replica set up, it would be best to follow the sets above for the primary node and then either take a full mariadb-dump or take a new full mariadb-backup and restore this to the replica. You can find more information about restoring a replica with mariadb-backup in Setting up a Replica with mariadb-backup

After running the below command, copy to the replica and use the LESS linux command to grab the change master statement. Remember to follow this process: Stop replica > restore data > run CHANGE MASTER statement > start replica again.

Please follow Setting up a Replica with mariadb-backup on restoring a replica with mariadb-backup:

Galera Cluster

For this process to work with Galera cluster, we first need to understand that some statements are not replicated across Galera nodes. One of which is the DISCARD and IMPORT for ALTER TABLES statements, and these statements will need to be ran on all nodes. We also need to run the OS level steps on each server as seen below.

Run the ALTER DROP KEYS statements on ONE NODE as these are replicated.

Once completed, run the DROP TABLE SPACE statements on EVERY NODE, as these are not replicated.

Exit out the database and change into the directory of the full backup location. Run the following commands to copy all the .cfg and .ibd files to the datadir such as /var/lib/mysql/testdatabase (change the datadir location if needed). Learn more about files that mariadb-backup generates with files-created-by-mariadb-backup. This step needs to be done on all nodes. You will need to copy the backup files to each node, we can use the same backup on all nodes.

After moving the files, it is very important that MySQL is the owner of the files, otherwise it won't have access to them and will error when we import the tablespaces.

Run the import table spaces statements on EVERY NODE.

Run the add key statements on ONE NODE.

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

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

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.

3. 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 get overwritten by the empty one. This helps to prevent certain kinds of known issues.

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.

uuid

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

name

If a name was provided by the --history or the ---incremental-history-name options, then it are saved here. Otherwise, this is 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.