Choosing Backup and DR Strategies for your MariaDB Infrastructure

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 )

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: 

Mariabackup

About Shiv Iyer 477 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.