MySQL backup with Bacula

Since MySQL is a substantially popular open-source relational database management system that’s being used by many sites, it’s only natural for it to have many different choices when it comes to database backup and recovery operations. However, each of the third-party software appliances has its own nuances and use cases, so it might be hard to find the right solution for yourself from the get-go.

Naturally, one of the most important factors when it comes to choosing the right MySQL backup software for your databases is the recovery time (RTO, or Recovery Time Objective). The majority of businesses consider their data to be one of their most valuable assets, meaning that stopping an entire database for a prolonged period of time means paralyzing the entire business, which in turn means massive financial losses in many cases. This is why a lot of companies tend to look for universal MySQL backup systems with the shortest RTO possible.

Speaking of universal MySQL appliances, Bacula Enterprise (with its Percona module) is one of the most popular choices, making it possible to work with various aspects of MySQL databases without knowing all of the nuances and methods when it comes to complex scripts and such. There’s also a plethora of different features included, like both dump and binary backup types (as well as the combination of the two), point in time recovery, vital information backup, the ability to filter objects during both the backup and the restore processes, and so on. 

The ability to choose between the dump and binary backup types is another benefit of using Bacula Enterprise as your MySQL backup and recovery software. Both of the backup types are vastly different from each other, and each of them is suitable for only specific use cases. 

For example, binary backup files are larger than with the dump technique, but the speed of both the backup and the restore process with the dump technique is several times slower. 

There’s also the fact that only dump mode allows for single object restoration and the fact that binary is incapable of restoring data to a newer or older MySQL version. However, the single object restoration operation requires for the dump file to be edited beforehand, which might prove problematic for less experienced users, and restoring an SQL dump to the previous MySQL version requires editing the SQL file beforehand and possibly losing some of the newer features that were recently introduced to the system as a whole.

In binary mode the entire process of creating a MySQL backup is much easier in general, taking advantage of the open-source hot backup utility called Percona XtraBackup (allowing the entire database to be backed up without locking the database in question beforehand, and ensuring the backup’s consistency at the same time). The applicable data sources for this case are InnoDB, XtraDB and MyISAM tables, as well as entire Percona servers with XtraDB.

The dump mode, on the other hand, is more basic, but not without some nuances here and there. For example, the default configuration of dump mode is backing up each of the databases separately, so that they are consistent on their own, but not consistent globally, since there’s little to no interaction between them in the process. 

This problem is fixed in Bacula’s case by saving the log files at the time of the backup, and these log files are later used to ensure both local and global consistency. You can also use the all_databases setting for the entire process so that all of the databases are dumped at the same time without flushing the logs at the end of the process, this is another way of ensuring global consistency for systems that have multiple MySQL databases.

Another significant advantage of Bacula Enterprise as a competitive MySQL backup system is its Percona module. There’s a number of benefits that come from using this module, including the lack of large temporary files, the ability to check the consistency of the database prior to its restoration (by restoring the database to a temporary location first and then making it consistent before copying it to the restore destination), among other things.

The software itself supports both 32 bit and 64 bit Linux platforms and works with almost all of the legacy MySQL versions, including the 8.0 version as well as the earlier ones.