This article briefly discusses the main ways to backup MariaDB. For detailed descriptions and syntax, see the individual pages. More detail is in the process of being added.
Logical backups consist of the SQL statements necessary to restore the data, such as CREATE DATABASE, CREATE TABLE and INSERT.
Physical backups are performed by copying the individual data files or directories.
The main differences are as follows:
Mariabackup is a fork of Percona XtraBackup with added support for MariaDB 10.1 compression and data-at-rest encryption. It is included with MariaDB 10.1.23 and later.
mysqldump performs a logical backup. It is the most flexible way to perform a backup and restore, and a good choice when the data size is relatively small.
For large datasets, the backup file can be large, and the restore time lengthy.
mysqldump dumps the data into SQL format (it can also dump into other formats, such as CSV or XML) which can then easily be imported into another database. The data can be imported into other versions of MariaDB, MySQL, or even another DBMS entirely, assuming there are no version or DBMS-specific statements in the dump.
mysqldump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines
and --events
). Procedures and functions are however also part of the system tables (for example mysql.proc).
InnoDB uses the buffer pool, which stores data and indexes from its tables in memory. This buffer is very important for performance. If InnoDB data doesn't fit the memory, it is important that the buffer contains the most frequently accessed data. However, last accessed data is candidate for insertion into the buffer pool. If not properly configured, when a table scan happens, InnoDB may copy the whole contents of a table into the buffer pool. The problem with logical backups is that they always imply full table scans.
An easy way to avoid this is by increasing the value of the innodb_old_blocks_time system variable. It represents the number of milliseconds that must pass before a recently accessed page can be put into the "new" sublist in the buffer pool. Data which is accessed only once should remain in the "old" sublist. This means that they will soon be evicted from the buffer pool. Since during the backup process the "old" sublist is likely to store data that is not useful, one could also consider resizing it by changing the value of the innodb_old_blocks_pct system variable.
Since MariaDB 10.0, it is also possible to explicitly dump the buffer pool on disk before starting a logical backup, and restore it after the process. This will undo any negative change to the buffer pool which happens during the backup. To dump the buffer pool, the innodb_buffer_pool_dump_now system variable can be set to ON. To restore it, the innodb_buffer_pool_load_now system variable can be set to ON.
Backing up a single database
shell> mysqldump db_name > backup-file.sql
Restoring or loading the database
shell> mysql db_name < backup-file.sql
See the mysqldump page for detailed syntax and examples.
mysqlhotcopy is currently deprecated.
mysqlhotcopy performs a physical backup, and works only for backing up MyISAM and ARCHIVE tables. It can only be run on the same machine as the location of the database directories.
shell> mysqlhotcopy db_name [/path/to/new_directory] shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
In MariaDB 10.1 and later, Mariabackup is the recommended backup method to use instead of Percona XtraBackup.
In MariaDB 10.3, Percona XtraBackup is not supported. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.
In MariaDB 10.2 and MariaDB 10.1, Percona XtraBackup is only partially supported. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.
Percona XtraBackup is a tool for performing fast, hot backups. It was designed specifically for XtraDB/InnoDB databases, but can be used with any storage engine (although not with MariaDB 10.1 encryption and compression). It is not included by default with MariaDB.
Some filesystems, like Veritas, support snapshots. During the snapshot, the table must be locked. The proper steps to obtain a snapshot are:
mount vxfs snapshot
umount snapshot
. Widely-used physical backup method, using a Perl script as a wrapper. See http://www.lenzg.net/mylvmbackup/.
For details, see:
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/backup-and-restore-overview/