MariaDB Backups Overview for SQL Server Users
MariaDB has essentially three types of backups:
- Logical backups (dumps).
- Hot backups with Mariabackup.
- Incremental backups.
Logical Backups (Dumps)
Logical backups are usually taken with mysqldump. This will produce the SQL statements needed to recreate MariaDB databases and their data into another server. A dump is the slowest form of backup to restore, because it implies executing all the SQL statements needed to recreate data. However it is also the most flexible, because restoring will work on any MariaDB version. Under certain conditions, MariaDB dumps may also be restored on other DBMSs, including SQL Server.
mysqldump allows to dump all databases, a single database, or a set of tables from a database. It is even possible to specify a
WHERE clause, which under certain circumstances allows to obtain incremental dumps.
For consistency reasons, it is important to use the
--single-transaction option. This will read all data in a single transaction. It's important however to understand that long transactions may have a big impact on performance.
--master-data option adds the statements to setup a slave to the dump.
MariaDB also supports statements which make easy to write applications to obtain custom types of dumps. For most
CREATE <object_type> statement, a corresponding
SHOW CREATE <object_type> exists. For example,
SHOW CREATE TABLE returns the
CREATE TABLE statement that can be used to recreate a certain table, without data.
mysqldump uses executable comments. This increases a backup compatibility, but we should be aware of how it works. If we use a feature introduced in version 10.1, for example, it will be included in the dump inside an executable comment. If we restore that backup on a server with MariaDB 10.0, the 10.1 feature will be ignored. This is the only way to restore backups in older MariaDB versions.
Hot Backups (mariabackup)
MariaDB starting with 10.1.23
MariaDB until 10.1.23
With older versions, it is possible to use Percona Xtrabackup. The problem with that tool is that it is written for MySQL, not MariaDB. When using MariaDB features not supported by MySQL it may break or produce unexpected results.
Mariabackup is a tool for taking a backup of MariaDB files while MariaDB is working. A lock is only held for a small amount of time, so it is suitable to backup a server without causing disruptions. It works by taking corrupted backup and then bringing them to a consistent state by using InnoDB undo log. Mariabackup also backups properly MyRocks tables and non-transactional storage engines.
Cold Backups and Snapshots
A copy of all MariaDB files is a working backup. Therefore, the easiest way to backup a dataset is to shutdown the server and copy all its files. It will be entirely possible to start another server with a copy of those files. This is often referred to as a cold backup. However, in most cases we don't want to do this, because it implies a downtime for the server: it will not be working at least for the time necessary to copy the files.
Snapshots are usually a better idea, as they are a consistent copy of the files at a given moment in time, taken without stopping the normal operations.
A snapshot of the files can be taken at several levels: filesystem level, if the filesystem supports snapshots, for example zfs; Linux Logical Volume Manager (LVM) also supports snapshots; and virtual machines also allow to take snapshots. Windows shadow copies are also snapshots, with a benefit: it is possible to restore a single file from a shadow copy. A snapshot is not an expensive operation, because it does not imply a copy of the files. The current files will not be modified anymore, and changes to them will be written in separate places.
The problem with snapshots is that they behave like a logical copy of the files as they are in a given point in time. But database files are not guaranteed to be consistent in every moment, because contents can be buffered before being flushed to the disk. You can think a database snapshot like a database after an operating system crash.
With non-transactional tables, some data is typically lost. Data changes that are present in a buffer before the snapshot, but not written on a disk, canno be recovered in any way. Data changes in transactional tables, like InnoDB tables, can always be recovered after restoring a snapshot (or after a crash), as long as a commit was done. Tables will still need to be repaired, just like it happens after an SQL Server crash.
Snapshots can be taken while MariaDB is running. To restore them, stop MariaDB first - or kill the process, because you don't really care of the consequences in this case. Then restore a snapshot and start MariaDB again.
For more information about snapshots, check your filesystem, LVM or virtual machine documentation.
The term incremental backup in MariaDB indicates what SQL Server calls a differential backup. An important difference is that in SQL Server such backups are based on the transaction log, which wouldn't be possible in MariaDB because transaction logs are handled at storage engine level.
- The binary log files are copied just like any other regular file.
- To copy those files it is necessary to have the proper permissions are filesystem level, not in MariaDB.
- Backups do not expire until we delete the last needed complete backup.
Replaying the Binary Log
The page Using mysqlbinlog shows how to use the mysqlbinlog utility to replay a binary log file.
The page also shows how to edit the binary log before replaying it. This allows to undo an SQL statement that was executed by mistake, for example a
DROP TABLE against a wrong table. The high level procedure is the following:
- Restore a backup that is older than the SQL statement to undo.
mysqlbinlogto generate a file with the SQL statements that were executed after the backup.
- Edit the SQL file, erasing the unwanted statement.
- Run the SQL file.
Incremental Backups with mariabackup
The simplest way to take an incremental backup is to use Mariabackup. This tool is able to take and restore incremental backups. For the complete procedure to use, see Incremental Backup and Restore with Mariabackup.
Mariabackup can run on both Linux and Windows systems.
Flashback is a feature that allows to bring all databases, some databases or some tables back to a certain point in time. This can only be done when if the binary log is enabled. Flashback is not a proper backup, but it can be used to restore a certain set of data.
Copying Individual Tables
It is entirely possible to restore a single table from a physical backup, or to copy the table to another server.
With MyISAM storage engine it was very easy to move tables between different servers, as long as the MySQL version was the same.
InnoDB is nowadays the default storage engine, and it is more complex, as it supports transactions for example. It still supports restoring a table from a physical file, this feature is called transportable tablespaces. There is a particular procedure to follow, and some limitations. This is basically the MariaDB equivalent of detaching and re-attaching tables in SQL Server.
For more information, see InnoDB File-Per-Table Tablespaces.
By default. all table files are located in the data directory, which is defined by the system variable datadir. There may be exceptions, because a table's files can be located elsewhere using the
DATA DIRECTORY and
INDEX DIRECTORY options in
Regardless the storage engine used, each table's structure is generally stored in a file with the