MEB: Taking Incremental Backup using last successful backup

Posted by Sagar Jauhari on Oracle Blogs See other posts from Oracle Blogs or by Sagar Jauhari
Published on Tue, 19 Jun 2012 05:36:04 +0000 Indexed on 2012/06/19 9:21 UTC
Read the original article Hit count: 361

Filed under:

Introduction

In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the location of the last full backup or incremental backup using the 'dir:' prefix. MEB would extract the end LSN of this backup from the mysql.backup_history table as well as the backup_variables.txt file (for verification) to use it as the start LSN of the incremental backup.

Because of popular demand, in MEB 3.7.1 the option '-incremental-base' has been extended further. The idea is to allow the user to take an incremental backup as easily as possible using the '–incremental-base' option. With the new option MEB queries the backup_history table for the last successful backup and uses its end LSN as the start LSN for the new incremental backup. It should be noted that the last successful backup is used irrespective of the location of the backup.

Details

A new prefix 'history:' has been introduced for the –incremental-base option and currently the only permissible value is the string "last_backup". So using the new option an incremental backup can be taken with the following command:

$ mysqlbackup --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

When MEB attempts to extract the end LSN of the last successful backup from the mysql.backup_history table, it also scans the corresponding backup destination for the old backup and tries to read the meta files at this backup destination. If a valid backup still exists at the backup destination and the meta files can be read, MEB compares the end LSN found in the mysql.backup_history table with the end LSN found in the backup meta files of the old backup. Assuming that the host MySQL server is alive and mysql.backup_history can be accessed by MEB, the behaviour of MEB with respect to verification of the old end LSN can be summarized as follows:

If 'BD' is the backup destination of the last successful backup in mysql.backup_history table and 'BHT' is the mysql.backup_history table

if can_read_files_at_BD:
    if end_lsn_found_at_BD == end_lsn_of_last_backup_in_BHT:
        continue_with_backup()
    else
        return_with_error()
else
    continue_with_backup()

Advantages

Apart from ease of usability an important advantage of this option is that the user can do repeated incremental backups without changing the command line. This is possible using the '–with-timestamp' option along with this new option. For example, the following command

$ mysqlbackup --with-timestamp --incremental --incremental-backup-dir=/media/mysqlbackup-repo/ --incremental-base=history:last_backup backup

 can be used to perform successive incremental backups in the directory /media/mysqlbackup-repo .

Limitations

The option '--incremental-base=history:last_backup'

  • should not be used when the user takes different kinds of concurrent backups on the same MySQL server (say different partial backups at multiple locations).
  • should not be used after any temporary or experimental backups performed on the server (which where successful!).
  • needs to be used with precaution since any intermediate successful backup without the –no-connection will be used as the base backup for the next incremental backup. 
  • will give an error in case a valid backup exists at the location of the last successful backup and whose end LSN is different from that of the last successful backup found in the backup_history table.

Date: 2012-06-19

HTML generated by org-mode 6.33x in emacs 23

© Oracle Blogs or respective owner

Related posts about /MySQL