An introduction to the mariadb-backup utility, detailing its features, installation process, and support for hot online backups of InnoDB tables.
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.
mariadb-backup supports all of the main features of Percona XtraBackup 2.3.8, plus:
Backup/Restore of tables using Data-at-Rest Encryption.
Backup/Restore of tables using InnoDB Page Compression.
with Galera Cluster.
Microsoft Windows support.
MariaDB 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.
mariadb-backupThe mariadb-backup executable is included in binary tarballs on Linux.
mariadb-backup can also be installed via a package manager on Linux. Many Linux distributions provide MariaDB software "out of the box", including mariadb-backup. If your Linux distribution doesn't, however, you can install using a MariaDB repository.
In order to do so, your system needs to be configured to install from one of the MariaDB repositories.
You can configure your package manager to install it from MariaDB Corporation's MariaDB Package Repository by using the MariaDB Package Repository setup script.
You can also configure your package manager to install it from MariaDB Foundation's MariaDB Repository by using the .
Installing with yum/dnf
On RHEL, CentOS, Fedora, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using yum or . Starting with RHEL 8 and Fedora 22, yum has been replaced by dnf, which is the next major version of yum. However, yum commands still work on many systems that use dnf. For example:
Installing with apt-get
On Debian, Ubuntu, and other similar Linux distributions, it is highly recommended to install the relevant DEB package from MariaDB's repository using . For example:
Installing with zypper
On SLES, OpenSUSE, and other similar Linux distributions, it is highly recommended to install the relevant RPM package from MariaDB's repository using zypper. For example:
The mariadb-backup executable is included in MSI and ZIP packages on Windows.
When using the , mariadb-backup can be installed by selecting Backup utilities:
The command to use mariadb-backup and the general syntax is:
For in-depth explanations on how to use mariadb-backup, see:
Options supported by mariadb-backup can be found on the page.
mariadb-backup will currently silently ignore unknown command-line options, so be extra careful about accidentally including typos in options or accidentally using options from later mariadb-backup versions. The reason for this is that mariadb-backup currently treats command-line options and options from equivalently. When it reads from these , it has to read a lot of options from the server option groups read by . However, mariadb-backup does not know about many of the options that it normally reads in these option groups. If mariadb-backup 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, mariadb-backup is designed to silently ignore the unknown options instead. See
In addition to reading options from the command-line, mariadb-backup can also read options from option files.
The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:
mariadb-backup reads server options from the following from :
mariadb-backup reads client options from the following option groups from option files:
mariadb-backup can optionally track your backup operations in a database table. This provides a centralized audit log and allows you to automate incremental backups by referencing the logical name of the previous backup instead of managing file paths.
Table Location and Schema Changes (MariaDB 10.11):
MariaDB 10.11 and later: The history table is mysql.mariadb_backup_history and uses the InnoDB storage engine (transactional).
MariaDB 10.10 and earlier: The history table is PERCONA_SCHEMA.xtrabackup_history and uses the CSV storage engine.
mariadb-backup needs to authenticate with the database server when it performs a backup operation (i.e. when the option is specified). For most use cases, the user account that performs the backup needs to have the following global privileges on the database server.
The required privileges are:
The required privileges are:
If your database server is also using the , then the user account that performs the backup will also need the SUPER . This is because mariadb-backup creates a checkpoint of this data by setting the system variable, which requires this privilege. See for more information.
CONNECTION ADMIN is also required where is greater than 0, and isn't applied in order to queries.
REPLICA MONITOR (or alias SLAVE MONITOR) is also required where or is specified.
To use the option(or the incremental history options), the backup user requires specific privileges on the history table.
The user needs INSERT to create history records and SELECT to read them for incremental backups:
The user needs privileges on the legacy PERCONA_SCHEMA:
For Upgrading to 10.11 (One-Time Migration)
If upgrading from an older version, mariadb-backup will attempt to migrate the old table to the new location on the first run. The backup user needs privileges to move and modify the old table:
Alternatively, you can perform this migration manually before running the backup:
The user account information can be specified with the and command-line options. For example:
The user account information can also be specified in a supported in an . For example:
mariadb-backup does not need to authenticate with the database server when preparing or restoring a backup.
mariadb-backup has to read MariaDB's files from the file system. Therefore, when you run mariadb-backup as a specific operating system user, you should ensure that user account has sufficient permissions to read those files.
If you are using Linux and if you installed MariaDB with a package manager, then MariaDB's files will probably be owned by the mysql user and the mysql group.
mariadb-backup with Data-at-Rest Encryptionmariadb-backup supports .
mariadb-backup will query the server to determine which is being used, and then it will load that plugin itself, which means that mariadb-backup needs to be able to load the key management and encryption plugin's shared library.
mariadb-backup will also query the server to determine which it needs to use.
In other words, mariadb-backup is able to figure out a lot of encryption-related information on its own, so normally one doesn't need to provide any extra options to backup or restore encrypted tables.
mariadb-backup backs up encrypted and unencrypted tables as they are on the original server. If a table is encrypted, then the table will remain encrypted in the backup. Similarly, if a table is unencrypted, then the table will remain unencrypted in the backup.
The primary reason that mariadb-backup needs to be able to encrypt and decrypt data is that it needs to apply records to make the data consistent when the backup is prepared. As a consequence, mariadb-backup does not perform many encryption or decryption operations when the backup is initially taken. MariaDB performs more encryption and decryption operations when the backup is prepared. This means that some encryption-related problems (such as using the wrong encryption keys) may not become apparent until the backup is prepared.
mariadb-backup for Galera SSTsThe mariadb-backup SST method uses the mariadb-backup utility for performing SSTs. See for more information.
mariadb-backupmariadb-backup backs up many different files in order to perform its backup operation. See for a list of these files.
mariadb-backupmariadb-backup creates several different types of files during the backup and prepare phases. See for a list of these files.
mariadb-backup can store the binary log position in the backup. See . This can be used for point-in-time recovery and to use the backup to setup a slave with the correct binlog position.
mariadb-backup defaults to the server's default datadir value. See for more information.
If mariadb-backup uses more file descriptors than the system is configured to allow, then users can see errors like the following:
mariadb-backup throws an error and aborts when this error is encountered. See for more information.
When this error is encountered, one solution is to explicitly specify a value for the option either on the command line or in one of the supported s in an . For example:
An alternative solution is to set the soft and hard limits for the user account that runs mariadb-backup by adding new limits to . For example, if mariadb-backup is run by the mysql user, then you could add lines like the following:
After the system is rebooted, the above configuration should set new open file limits for the mysql user, and the user's ulimit output should look like the following:
(video)
(video)
(video)
This page is licensed: CC BY-SA / Gnu FDL
Provides optimal backup support for all storage engines that store things on local disk.
MariaDB Backup does not support some additional features.
[mariadb]
Options read by MariaDB Server.
[mariadb-X.Y]
Options read by a specific version of MariaDB Server. For example: [mariadb-10.6].
[mariadbd]
Options read by MariaDB Server. Available from MariaDB 10.5.4.
[mariadbd-X.Y]
Options read by a specific version of MariaDB Server. For example: [mariadbd-10.6]. Available from 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.
[galera]
Options read by MariaDB Server, but only if it is compiled with Galera Cluster support. 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.
--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.
[mariadb-backup]
Options read by mariadb-backup.
[mariadb-backup]
Options read by mariadb-backup.
[xtrabackup]
Options read by mariadb-backup and Percona XtraBackup.
[server]
Options read by MariaDB Server.
[mysqld]
Options read by mariadbd, which includes both MariaDB Server and MySQL Server (where it is called mysqld).
[mysqld-X.Y]
Options read by a specific version of mysqld, which includes both MariaDB Server and MySQL Server. For example: [mysqld-10.6].
[mariadb-backup]
Options read by mariadb-backup. Available starting with and .
[mariadb-backup]
Options read by mariadb-backup. Available starting with and
[xtrabackup]
Options read by mariadb-backup 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 , , and .
[client-mariadb]
Options read by all MariaDB client programs. Available starting with , , and .
sudo yum install MariaDB-backupsudo apt-get install mariadb-backupsudo zypper install MariaDB-backupmariadb-backup <options>CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'mypassword';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariadb-backup'@'localhost';GRANT SELECT, INSERT, CREATE, ALTER ON mysql.mariadb_backup_history TO 'mariadb-backup'@'localhost';GRANT SELECT, INSERT, CREATE, ALTER ON PERCONA_SCHEMA.xtrabackup_history TO 'mariadb-backup'@'localhost';GRANT DROP, ALTER, RENAME ON PERCONA_SCHEMA.xtrabackup_history TO 'mariadb-backup'@'localhost';
GRANT CREATE ON PERCONA_SCHEMA TO 'mariadb-backup'@'localhost';RENAME TABLE PERCONA_SCHEMA.xtrabackup_history TO mysql.mariadb_backup_history;
ALTER TABLE mysql.mariadb_backup_history ENGINE=InnoDB;mariadb-backup --backup \
--target-dir=/var/mariadb/backup/ \
--user=mariadb-backup --password=mypassword[mariadb-backup]
user=mariadb-backup
password=mypassword2019-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.[mariadb-backup]
open_files_limit=65535mysql soft nofile 65535
mysql hard nofile 65535ulimit -Sn
65535
ulimit -Hn
65535