MariaDB Backups Overview for SQL Server Users

MariaDB has essentially three types of backups:

• Logical backups (dumps).
• Hot backups with Mariabackup.
• Snapshots.
• Incremental backups.

Logical backups

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.

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

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.

Snapshots

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

Incremental backups

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.

As mentioned here, MariaDB can use the binary log instead for backup purposes. Such incremental backups can be done manually. This means that:

• 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.
• Use mysqlbinlog to 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.

Flashback

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.