Choosing Backup and DR Strategies for your MariaDB Infrastructure
Backup in simple terms is just copying data and retaining the same in some persistent storage ( in logical / SQL or binary format ) to recover in case of data corruption or database outage ( infrastructure crash ) . So with backup, should you lose your original data, you can use the backup to make it available again. In MariaDB, You have multiple database backup solutions – Physical backup, logical backup and hot backup( Mariabackup) . You can use one of them or combination of multiple backup solutions for maximum data reliability. In this post we are talking about all the backup solutions available with MariaDB, Please don’t consider this post as MariaDB DR strategy for your database infrastructure operations, You definitely can use this blog post to understand all the solutions available with MariaDB for Disaster Recovery ( DR )
Table of Contents
MariaDB DR Solutions
- mysqldump
- Filesystem Snapshots
- mylvmbackup
- Percona XtraBackup
- Mariabackup
Using mysqldump for MariaDB DR
mysqldump is a logical backup solution for MariaDB which is very easy to implement and also reliable ( mysqldump dumps the data into SQL format, it can also dump into other formats, such as CSV or XML), We strongly recommend to include mysqldump in MariaDB DR strategy even for very large database infrastructure, This is usually done from a DR Slave instance in MariaDB Replication. Of course we are aware of cost and some operational complexities with more MariaDB instances but nothing compared to the ownership of highly reliable MariaDB instances. mysqldump copy can be used to import other versions of MariaDB, MySQL, or even another DBMS entirely. But, please consider some challenges with data type mapping when migrating data between versions and it get more complex with other distribution of MySQL or totally different DBMS platforms.
How to plan mysqldump usage for efficiency ?
mysqldump is a logical backup solution and they always imply full table scans and when innodb buffer pool is not configured properly, the whole contents of a table will fit into the buffer pool which is definitely not good for InnoDB performance. so how can we avoid this situation ? You can configure number of milliseconds that must pass before a recently accessed page can be put into the “new” sublist in the buffer pool by increasing the value of innodb_old_blocks_time system variable. This makes data which is accessed only once will soon be evicted from the buffer pool ( and remain in “old” sublist) and by setting optimal value for innodb_old_blocks_pct system variable you can improve the overall InnoDB buffer pool performance.
Starting with MariaDB 10.0 you can explicitly dump the buffer pool on disk before starting mysqldump ( logical backup ) and restore it after the process for data reliability. To dump buffer pool set innodb_buffer_pool_dump_now system variable to ON and to restore set innodb_buffer_pool_load_now system variable can be set to ON.
MariaDB backup with Filesystem Snapshots
Even though several storage vendors call this a reliable hot backup solution, it contains only the snapshot of data in the file system and not in the memory. Another serious concern is Filesystem Snapshots does FLUSH TABLES WITH READ LOCK before backup ( though only for a fraction of second ) which is causes sometimes serious performance bottleneck with long-running and especially batch processing queries. We carefully audit the customer database infrastructure before recommending Filesystem Snapshots.
Benefits of Filesystem Snapshots:
- Compatibility with both MyISAM, InnoDB and RocksDB
- Backup Performance – It’s a binary backup and so is faster
- Recovery Performance – Faster restoration because it’s just putting files back
Backup MariaDB with mylvmbackup
mylvmbackup is a high performance physical hot backup for MariaDB data files by implementing read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.
References: http://www.lenzg.net/mylvmbackup/
Percona XtraBackup
Percona Xtrabackup is a high performance hot backup solution from Percona compatible with InnoDB / XtraDB and RocksDB providing both backup validation and point-in-time recovery. But, In MariaDB 10.3 and later, Percona XtraBackup is not supported.
Some interesting references links on MariaDB and Percona XtraBackup compatibility:
- In MariaDB 10.3 and later, Percona XtraBackup is not supported. This limitation is being tracked by Percona XtraBackup bug PXB-1550. However, it does not appear that there are plans to fix it.
- In MariaDB 10.2, Percona XtraBackup 2.4 is supported in some cases if InnoDB page compression is not used, and if data at rest encryption is not used, and if innodb_page_size is set to 16K. But, users should be aware that problems are likely due to the MySQL 5.7 undo log format incompatibility bug that was fixed in MariaDB 10.2.2 in MDEV-12289. Due to this bug, backups prepared with Percona XtraBackup 2.4 may fail to recover some transactions. Only if you ran the server with the setting innodb_undo_logs=1 this would not be a problem. Percona XtraBackup 2.4 may also fail to work entirely with MariaDB 10.2.19 and later if innodb_safe_truncate=ON is set due to changes in the redo log format introduced by MDEV-14717.
- In MariaDB 10.1, Percona XtraBackup 2.3 is supported if InnoDB page compression is not used, and if data at rest encryption is not used, and if innodb_page_size is set to 16K.
- In MariaDB 10.0 and before, Percona XtraBackup 2.3 is supported.
Mariabackup
- Forked from Percona XtraBackup 2.3.8., for performing physical online / hot backups of InnoDB, Aria , MyISAM and MyRocks tables.
- Mariabackup supports MariaDB 10.1 exclusive features InnoDB Page Compression and Data-at-Rest Encryption .
- Mariabackup supports mariabackup SST method with Galera Cluster.
- Microsoft Windows support.
- Backup MariaDB with Mariabackup:
