MariaDB Backups Overview for SQL Server Users
MariaDB has essentially three types of backups:
- Logical backups (dumps).
- Hot backups with mariabackup.
- Incremental backups (binary log).
- Snapshots.
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.
