The MySQL backup tool in Bacula Enterprise Edition is designed to both simplify and accelerate the backup of your MySQL databases. Designed to save your valuable time, the system administrator does not need to know the internals of MySQL DB backup techniques or programming to write complex scripts. This MySQL backup software will automatically backup essential information such as configuration or user definitions. Putting speed and accurate data recovery into the hands of the user, our solution supports both Dump and binary backup of MySQL database and data recovery techniques.
The Point In Time Recovery feature is available for both Dump and Binary. Bacula's MySQL backup tools also backup vital meta-data information and manages Object filtering during database backup and restore. No special scripting skills are required.
This MySQL backup tool is available on Linux 32 and 64 bits platforms and supports nearly all previous versions of MySQL, up to and including 8.0.
MySQL Percona module for Bacula Enterprise is a hot backup utility for MySQL-based servers which removes the need to lock your database during the backup.
This module uses techniques that ensure consistency of the whole backup. Another advantage is that Bacula's Percona backup software does not require the use of large temporary files.
During restore, databases are restored to a temporary location, then made consistent using the 'Prepare' option on the Percona tools, prior to actually modifying the live database. Rather than doing the 'Prepare' work to make the database consistent at restore time, the 'Prepare' can be done automatically by the module during the backup phase.Download MySQL whitepaper
Key MySQL Backup Software Benefits
The Bacula Systems' MySQL backup software allows the administrator to choose between Dump (slower and smaller backups) or Binary (faster and larger backups). Our backup tool takes care of the MySQL log files when using the Point In Time Recovery (PITR) feature.
The following table might help you choose between the MySQL backup techniques supported by Bacula Enterprise. Key functionalities such as being able to restore your database at any point in time, or being able to filter objects during backup or restore should guide you. It is also possible to combine Dump and Binary techniques for the same server.
|Can restore directly a single object (table, schema, . . . )||Yes1||No|
|Restore speed||Very Slow||Fast|
|Can restore at any point in time||Yes||Yes|
|Can restore to previous major version of MySQL||Yes2||No|
|Can restore to newer major version of MySQL||Yes||No|
1To restore a single object, the dump file must be edited.
2To restore an SQL dump to a previous version of MySQL, you might have to edit the SQL file if you use features that are not available on the previous version. Generally, restoring to a previous version of MySQL is neither supported nor guaranteed.
During a database’s life, MySQL generates logs that can be used to do replication and/or protect your database using P.I.T.R (Point In Time Recovery).
By default, the MySQL backup software from Bacula will dump each database separately, which means that if you need to restore the entire server, databases will be consistent separately, but they are not backed up at exactly the same time, so the databases are not globally consistent. To address this issue, the Bacula Enterprise MySQL backup tool will also save log files generated during the backup. These log files may later be played back to ensure that the databases are consistent at a particular point in time.
In the example presented in Figure 1, during the backup of the databases “DB1”, “DB2” and “DB3” (that can take several hours), 3 log files were generated, and will be included in the Full backup.
The next incremental or differential backup will save only new binary logs generated after the Full. To ensure that only one copy of each log file is included in your backup, you should activate the Accurate option for your Job.
In the example show above, the first Incremental job after the Full backup will include logs 5 and 6, and the second Incremental job will include logs 7 and 8. A Differential backup would include log files 5, 6, 7 and 8.
When you use the all_databases option, all databases will be dumped at the same time, and the log files will not be flushed at the end of the Full backup, but logs generated before the end of the job are included in the backup. In the example shown in Figure 2, the Full backup will generate a single dump “all-databases.sql” and will include log files 2 and 3. The first subsequent Incremental backup will include log files 4, 5 and 6.
In binary mode, the MySQL backup tool uses Percona XtraBackup, which is an open-source hot backup utility for MySQL based servers that doesn’t need to lock your database during the backup. The Percona technology uses techniques that ensure consistency of the the whole backup.
It can back up data from InnoDB, XtraDB, and MyISAM tables on unmodified MySQL, as well as a Percona Server with XtraDB.
More information about Percona can be found at: http://www.percona.com/doc/percona-xtrabackup .
Further help on MySQL Backup Tools and more: