Mariabackup Overview
Mariabackup 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.
Contents
Backup Support for MariaDB-Exclusive Features
MariaDB 10.1 introduced features that are exclusive to MariaDB, such as InnoDB Page Compression and Data-at-Rest Encryption. These exclusive features have been very popular with MariaDB users. However, existing backup solutions from the MySQL ecosystem, such as Percona XtraBackup, did not support full backup capability for these features.
To address the needs of our users, we decided to develop a backup solution that would fully support these popular MariaDB-exclusive features. We did this by creating Mariabackup, which is based on the well-known and commonly used backup tool called Percona XtraBackup. Mariabackup was originally extended from version 2.3.8.
Supported Features
Mariabackup 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.
- mariabackup SST method with Galera Cluster.
- Microsoft Windows support.
- Backup/Restore of tables using the MyRocks storage engine starting with MariaDB 10.2.16 and MariaDB 10.3.8. See Files Backed up by Mariabackup: MyRocks Data Files for more information.
Supported Features in MariaDB Enterprise Backup
MariaDB Enterprise Backup supports some additional features, such as:
- Minimizes locks during the backup to permit more concurrency and to enable faster backups.
- This relies on the usage of
BACKUP STAGE
commands and DDL logging. - This includes no locking during the copy phase of
ALTER TABLE
statements, which tends to be the longest phase of these statements.
- This relies on the usage of
- Provides optimal backup support for all storage engines that store things on local disk.
Differences Compared to Percona XtraBackup
- Percona XtraBackup copies its InnoDB redo log files to the file
xtrabackup_logfile
, while Mariabackup uses the fileib_logfile0
.
- Percona XtraBackup's libgcrypt-based encryption of backups is not supported by Mariabackup.
- There is no symbolic link from
mariabackup
toinnobackupex
, as there is forxtrabackup
. Instead,mariabackup
has the--innobackupex
command-line option to enable innobackupex-compatible options.
- The
--compact
and--rebuild_indexes
options are not supported.
- Support for
--stream=tar
was removed from Mariabackup in MariaDB 10.1.24.
- The
xbstream
utility has been renamed tombstream
. However, to select this output format when creating a backup, Mariabackup's--stream
option still expects thexbstream
value.
- Mariabackup does not support lockless binlog.
Difference in Versioning Schemes
Each Percona XtraBackup release has two version numbers--the Percona XtraBackup version number and the version number of the MySQL Server release that it is based on. For example:
xtrabackup version 2.2.8 based on MySQL server 5.6.22
Each Mariabackup release only has one version number, and it is the same as the version number of the MariaDB Server release that it is based on. For example:
mariabackup based on MariaDB server 10.2.15-MariaDB Linux (x86_64)
See Compatibility of Mariabackup Releases with MariaDB Server Releases for more information on Mariabackup versions.
Compatibility of Mariabackup Releases with MariaDB Server Releases
It is not generally possible, or supported, to prepare a backup in a different MariaDB version than the database version at the time when backup was taken. For example, if you backup MariaDB 10.4, you should use mariabackup version 10.4, rather than e.g 10.5.
A MariaDB Server version can often be backed up with most other Mariabackup releases in the same release series. For example, MariaDB 10.2.21 and MariaDB 10.2.22 are both in the MariaDB 10.2 release series, so MariaDB Server from MariaDB 10.2.21 could be backed up by Mariabackup from MariaDB 10.2.22, or vice versa.
However, occasionally, a MariaDB Server or Mariabackup release will include bug fixes that will break compatibility with previous releases. For example, the fix for MDEV-13564 changed the InnoDB redo log format in MariaDB 10.2.19 which broke compatibility with previous releases. To be safest, a MariaDB Server release should generally be backed up with the Mariabackup release that has the same version number.
Mariabackup from MariaDB 10.1 releases may also be able to back up MariaDB Server from MariaDB 5.5 and MariaDB 10.0 releases in many cases. However, this is not fully supported. See MDEV-14936 for more information.
Installing Mariabackup
Installing on Linux
The mariabackup
executable is included in binary tarballs on Linux.
Installing with a Package Manager
Mariabackup can also be installed via a package manager on Linux. In order to do so, your system needs to be configured to install from one of the MariaDB repositories.
You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the MariaDB Package Repository setup script.
You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the MariaDB Repository Configuration Tool.
Installing with yum/dnf
On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's
repository using yum
or dnf
. Starting with RHEL 8 and Fedora 22, yum
has been replaced by dnf
, which is the next major version of yum
. However, yum
commands still work on many systems that use dnf
. For example:
sudo yum install MariaDB-backup
Installing with apt-get
On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant DEB package from MariaDB's
repository using apt-get
. For example:
sudo apt-get install mariadb-backup
Installing with zypper
On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using zypper
. For example:
sudo zypper install MariaDB-backup
Installing on Windows
The mariabackup
executable is included in MSI and ZIP packages on Windows.
When using the Windows MSI installer, mariabackup
can be installed by selecting Backup utilities:
Using Mariabackup
The command to use mariabackup
and the general syntax is:
mariabackup <options>
For in-depth explanations on how to use Mariabackup, see:
- Full Backup and Restore with Mariabackup
- Incremental Backup and Restore with Mariabackup
- Partial Backup and Restore with Mariabackup
- Restoring Individual Tables and Partitions with Mariabackup
- Setting up a Replication Slave with Mariabackup
- Using Encryption and Compression Tools With Mariabackup
Options
Options supported by Mariabackup can be found here.
mariabackup
will currently silently ignore unknown command-line options, so be extra careful about accidentally including typos in options or accidentally using options from later mariabackup
versions. The reason for this is that mariabackup
currently treats command-line options and options from option files equivalently. When it reads from these option files, it has to read a lot of options from the server option groups read by mysqld
. However, mariabackup
does not know about many of the options that it normally reads in these option groups. If mariabackup
raised an error or warning when it encountered an unknown option, then this process would generate a large amount of log messages under normal use. Therefore, mariabackup
is designed to silently ignore the unknown options instead. See MDEV-18215 about that.
Option Files
In addition to reading options from the command-line, Mariabackup 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:
Option | Description |
---|---|
--print-defaults | Print the program argument list and exit. |
--no-defaults | Don't read default options from any option file. |
--defaults-file=# | Only read default options from the given option file. |
--defaults-extra-file=# | Read this file after the global files are read. |
--defaults-group-suffix=# | In addition to the default option groups, also read option groups with this suffix. |
Server Option Groups
Mariabackup reads server options from the following option groups from option files:
Group | Description |
---|---|
[mariabackup] | Options read by Mariabackup. Available starting with MariaDB 10.1.31 and MariaDB 10.2.13. |
[mariadb-backup] | Options read by Mariabackup. Available starting with MariaDB 10.4.14 and MariaDB 10.5.4. |
[xtrabackup] | Options read by Mariabackup and Percona XtraBackup. |
[server] | Options read by MariaDB Server. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mysqld] | Options read by mysqld , which includes both MariaDB Server and MySQL Server. |
[mysqld-X.Y] | Options read by a specific version of mysqld , which includes both MariaDB Server and MySQL Server. For example, [mysqld-10.4] . Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mariadb] | Options read by MariaDB Server. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mariadb-X.Y] | Options read by a specific version of MariaDB Server. For example, [mariadb-10.4] . Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mariadbd] | Options read by MariaDB Server. Available starting with MariaDB 10.4.14 and MariaDB 10.5.4. |
[mariadbd-X.Y] | Options read by a specific version of MariaDB Server. For example, [mariadbd-10.4] . Available starting with MariaDB 10.4.14 and MariaDB 10.5.4. |
[client-server] | Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[galera] | Options read by MariaDB Server, but only if it is compiled with Galera Cluster support. In MariaDB 10.1 and later, all builds on Linux are compiled with Galera Cluster support. When using one of these builds, options from this option group are read even if the Galera Cluster functionality is not enabled. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13 on systems compiled with Galera Cluster support. |
Client Option Groups
Mariabackup reads client options from the following option groups from option files:
Group | Description |
---|---|
[mariabackup] | Options read by Mariabackup. Available starting with MariaDB 10.1.31 and MariaDB 10.2.13. |
[mariadb-backup] | Options read by Mariabackup. Available starting with MariaDB 10.4.14 and MariaDB 10.5.4. |
[xtrabackup] | Options read by Mariabackup and Percona XtraBackup. |
[client] | Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump . |
[client-server] | Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[client-mariadb] | Options read by all MariaDB client programs. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
Authentication and Privileges
Mariabackup needs to authenticate with the database server when it performs a backup operation (i.e. when the --backup
option is specified). For most use cases, the user account that performs the backup needs to have the following global privileges on the database server.
In 10.5 and later the required privileges are:
CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword'; GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';
Prior to 10.5, the required privileges are:
CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword'; GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';
If your database server is also using the MyRocks storage engine, then the user account that performs the backup will also need the SUPER
global privilege. This is because Mariabackup creates a checkpoint of this data by setting the rocksdb_create_checkpoint
system variable, which requires this privilege. See MDEV-20577 for more information.
CONNECTION ADMIN
is also required where -kill-long-queries-timeout
is greater than 0, and --no-lock
isn't applied in order to KILL
queries. Prior to 10.5 a SUPER
privilege is required instead of CONNECTION ADMIN
.
To use the --history
option, the backup user also needs to have the following privileges granted:
GRANT CREATE, INSERT ON mysql.* TO 'mariabackup'@'localhost';
Prior to MariaDB 10.11, the necessary permissions to use --history
were:
GRANT CREATE, INSERT ON PERCONA_SCHEMA.* TO 'mariabackup'@'localhost';
If you're upgrading from an older version and you want to use the new default table without losing your backup history, you can move and rename the current table in this way:
RENAME TABLE PERCONA_SCHEMA.xtrabackup_history TO mysql.mariadb_backup_history;
The user account information can be specified with the --user
and --password
command-line options. For example:
$ mariabackup --backup \ --target-dir=/var/mariadb/backup/ \ --user=mariabackup --password=mypassword
The user account information can also be specified in a supported client option group in an option file. For example:
[mariabackup] user=mariabackup password=mypassword
Mariabackup does not need to authenticate with the database server when preparing or restoring a backup.
File System Permissions
Mariabackup has to read MariaDB's files from the file system. Therefore, when you run Mariabackup 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 Mariabackup with Data-at-Rest Encryption
Mariabackup supports Data-at-Rest Encryption.
Mariabackup will query the server to determine which key management and encryption plugin is being used, and then it will load that plugin itself, which means that Mariabackup needs to be able to load the key management and encryption plugin's shared library.
Mariabackup will also query the server to determine which encryption keys it needs to use.
In other words, Mariabackup 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.
Mariabackup 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 Mariabackup needs to be able to encrypt and decrypt data is that it needs to apply InnoDB redo log records to make the data consistent when the backup is prepared. As a consequence, Mariabackup 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 Mariabackup for Galera SSTs
The mariabackup
SST method uses the Mariabackup utility for performing SSTs. See mariabackup SST method for more information.
Files Backed up by Mariabackup
Mariabackup backs up many different files in order to perform its backup operation. See Files Backed up by Mariabackup for a list of these files.
Files Created by Mariabackup
Mariabackup creates several different types of files during the backup and prepare phases. See Files Created by Mariabackup for a list of these files.
Known Issues
Unsupported Server Option Groups
Prior to MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13, Mariabackup doesn't read server options from all option groups supported by the server. In those versions, it only looks for server options in the following server option groups:
Group | Description |
---|---|
[xtrabackup] | Options read by Percona XtraBackup and Mariabackup. |
[mariabackup] | Options read by Percona XtraBackup and Mariabackup. Available starting with MariaDB 10.1.31 and MariaDB 10.2.13. |
[mysqld] | Options read by mysqld , which includes both MariaDB Server and MySQL Server. |
Those versions do not read server options from the following option groups supported by the server:
Group | Description |
---|---|
[server] | Options read by MariaDB Server. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mysqld-X.Y] | Options read by a specific version of mysqld , which includes both MariaDB Server and MySQL Server. For example, [mysqld-5.5] Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mariadb] | Options read by MariaDB Server. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[mariadb-X.Y] | Options read by a specific version of MariaDB Server. For example, [mariadb-10.3] Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[client-server] | Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13. |
[galera] | Options read by MariaDB Server, but only if it is compiled with Galera Cluster support. In MariaDB 10.1 and later, all builds on Linux are compiled with Galera Cluster support. When using one of these builds, options from this option group are read even if the Galera Cluster functionality is not enabled. Available starting with MariaDB 10.1.38, MariaDB 10.2.22, and MariaDB 10.3.13 on systems compiled with Galera Cluster support. |
See MDEV-18347 for more information.
No Default Datadir
Prior to MariaDB 10.1.36, MariaDB 10.2.18, and MariaDB 10.3.10, if you were performing a --copy-back
operation, and if you did not explicitly specify a value for the datadir
option either on the command line or one of the supported server option groups in an option file, then Mariabackup would not default to the server's default datadir
. Instead, Mariabackup would fail with an error. For example:
Error: datadir must be specified.
The solution is to explicitly specify a value for the datadir
option either on the command line or in one of the supported server option groups in an option file. For example:
[mysqld] datadir=/var/lib/mysql
In MariaDB 10.1.36, MariaDB 10.2.18, and MariaDB 10.3.10 and later, Mariabackup will default to the server's default datadir
value.
See MDEV-12956 for more information.
Concurrent DDL and Backup Issues
Prior to MariaDB 10.2.19 and MariaDB 10.3.10, if concurrent DDL was executed while the backup was taken, then that could cause various kinds of problems to occur.
One example is that if DDL caused any tablespace IDs to change (such as TRUNCATE TABLE
or RENAME TABLE
), then that could cause the effected tables to be inconsistent in the backup. In this scenario, you might see errors about mismatched tablespace IDs when the backup is prepared.
For example, the errors might look like this:
2018-12-07 07:49:32 7f51b3184820 InnoDB: Error: table 'DB1/TAB_TEMP' InnoDB: in InnoDB data dictionary has tablespace id 1355633, InnoDB: but a tablespace with that id does not exist. There is InnoDB: a tablespace of name DB1/TAB_TEMP and id 1354713, though. Have InnoDB: you deleted or moved .ibd files? InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html InnoDB: for how to resolve the issue.
Or they might look like this:
2018-07-12 21:24:14 139666981324672 [Note] InnoDB: Ignoring data file 'db1/tab1.ibd' with space ID 200485, since the redo log references db1/tab1.ibd with space ID 200484.
Some of the problems related to concurrent DDL are described below.
Problems solved by setting --lock-ddl-per-table
(Mariabackup command-line option added in MariaDB 10.2.9):
- If a table is dropped during the backup, then it might still exists after the backup is prepared.
- If a table exists when the backup starts, but it is dropped before the backup copies it, then the tablespace file can't be copied, so the backup would fail.
Problems solved by setting innodb_log_optimize_ddl=OFF
(MariaDB Server system variable added in MariaDB 10.2.17 and removed in 10.6.0):
- If the backup noticed concurrent DDL, then it might fail with "ALTER TABLE or OPTIMIZE TABLE was executed during backup".
Problems solved by innodb_safe_truncate=ON
(MariaDB Server system variable in MariaDB 10.2.19 and removed in 10.3.0):
- If a table is created during the backup, then it might not exist in the backup after prepare.
- If a table is renamed during the backup after the tablespace file was copied, then the table may not exist after the backup is prepared.
- If a table is dropped and created under the same name during the backup after the tablespace file was copied, then the table will have the wrong tablespace ID when the backup is prepared.
Note that, with the removal of innodb_log_optimize_ddl
and innodb_safe_truncate
, the above problems were definitely solved.
Problems solved by other bug fixes:
- If
--lock-ddl-per-table
is used and if a table is concurrently being dropped or renamed, then Mariabackup can fail to acquire the MDL lock.
These problems are only fixed in MariaDB 10.2 and later, so it is not recommended to execute concurrent DDL when using Mariabackup with MariaDB 10.1.
See MDEV-13563, MDEV-13564, MDEV-16809, and MDEV-16791 for more information.
Manual Restore with Pre-existing InnoDB Redo Log files
Prior to MariaDB 10.2.10, Mariabackup users could run into issues if they restored a backup by manually copying the files from the backup into the datadir
while the directory still contained pre-existing InnoDB redo log files. The backup itself did not contain InnoDB redo log files with the traditional ib_logfileN
file names, so the pre-existing log files would remain in the datadir
. If the server were started with these pre-existing log files, then it could perform crash recovery with them, which could cause the database to become inconsistent or corrupt.
In these MariaDB versions, this problem could be avoided by not restoring the backup by manually copying the files and instead restoring the backup by using Mariabackup and providing the --copy-back
option, since Mariabackup deletes pre-existing InnoDB redo log files from the datadir
during the restore process.
In MariaDB 10.2.10 and later, Mariabackup prevents this issue by creating an empty InnoDB redo log file called ib_logfile0
as part of the --prepare
stage. That way, if the backup is manually restored, any pre-existing InnoDB redo log files would get overwritten by the empty one.
See MDEV-13311 for more information.
Too Many Open Files
If Mariabackup uses more file descriptors than the system is configured to allow, then users can see errors like the following:
2019-02-12 09:48:38 7ffff7fdb820 InnoDB: Operating system error number 23 in a file operation. InnoDB: Error number 23 means 'Too many open files in system'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html InnoDB: Error: could not open single-table tablespace file ./db1/tab1.ibd InnoDB: We do not continue the crash recovery, because the table may become InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it. InnoDB: To fix the problem and start mysqld: InnoDB: 1) If there is a permission problem in the file and mysqld cannot InnoDB: open the file, you should modify the permissions. InnoDB: 2) If the table is not needed, or you can restore it from a backup, InnoDB: then you can remove the .ibd file, and InnoDB will do a normal InnoDB: crash recovery and ignore that table. InnoDB: 3) If the file system or the disk is broken, and you cannot remove InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf InnoDB: and force InnoDB to continue crash recovery here.
Prior to MariaDB 10.1.39, MariaDB 10.2.24, and MariaDB 10.3.14, Mariabackup would actually ignore the error and continue the backup. In some of those cases, Mariabackup would even report a successful completion of the backup to the user. In later versions, Mariabackup will properly throw an error and abort when this error is encountered. See MDEV-19060 for more information.
When this error is encountered, one solution is to explicitly specify a value for the open-files-limit
option either on the command line or in one of the supported server option groups in an option file. For example:
[mariabackup] open_files_limit=65535
An alternative solution is to set the soft and hard limits for the user account that runs Mariabackup by adding new limits to /etc/security/limits.conf
. For example, if Mariabackup is run by the mysql
user, then you could add lines like the following:
mysql soft nofile 65535 mysql hard nofile 65535
After the system is rebooted, the above configuration should set new open file limits for the mysql
user, and the user's ulimit
output should look like the following:
$ ulimit -Sn 65535 $ ulimit -Hn 65535
Versions
Mariabackup/Server Version | Maturity |
---|---|
MariaDB 10.2.10+, MariaDB 10.1.26+ | Stable |
MariaDB 10.2.7+, MariaDB 10.1.25 | Beta |
MariaDB 10.1.23 | Alpha |
See Also
- mariadb-dump/mysqldump
- How to backup with MariaDB (video)
- MariaDB point-in-time recovery (video)
- Mariabackup and Restic (video)
- MariaDB Enterprise backup. An updated version of mariabackup.
- Percona Xtrabackup 2.3 documentation