Tuesday Jun 19, 2012

MEB: Taking Incremental Backup using last successful backup

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

Thursday Jan 12, 2012

MySQL Enterprise Backup: Redo-log-only Incremental Backups

The latest release of MySQL Enterprise Backup (MEB 3.7.0) introduces a new method for performing incremental hot backups - the redo-log-only incremental backup. This new method of incremental backups allows for highly compact and fast incremental backups and MEB users now have the choice between data-file based incremental backups and the redo-log-only incremental backups.

In data-file based incremental backups (performed using the '--incremental' option) MEB scans all InnoDB datafiles but copies to the backup only modified pages. The main benefit of this is that an incremental backup is much smaller than a full backup but the downside is that during the process of taking an incremental backup MEB still reads all data-files.

With the new redo-log-only incremental hot backups MEB copies just redo logs accumulated since the previous backup. So, no scanning of the data-files is needed and just sequential copy of the redo log is performed. The redo-log-only incremental backup and data-file based incremental backup treat the non-InnoDB data in the same way: the backup of InnoDB data is incremental but the backup of non-InnoDB data is not. Some important aspects of this backup method are:

  • Incremental redo-log-only backup is not always possible. Redo log in InnoDB is implemented with fixed-size circular log files. This means that oldest log entries are overwritten by newer ones after some time. Incremental backup using only redo log is possible only from the log position that is not yet overwritten.
  • Efficiency of the method depends on how the database is modified. If many database pages are modified, but each page is modified only once or a few times, then copying just redo log might work well. On the other hand, if only a small fraction of the database pages is modified, but each page is modified many times, then this method might give poorer performance.

Let us consider a typical usage scenario in which the redo-log-only incremental hot backup is used to back up a database once a day. This requires that InnoDB log files are large enough to hold at least one day's worth of redo logs. This also means that InnoDB log files are pretty large: for a terabyte sized database with 1% of datafile pages modified each day the minimum combined log file size would be 10 gigabytes.

Our experiments showed that the redo-log-only incremental backup method offers significant performance improvements over the normal incremental backup when the database is suitable for this method: the backup process takes less time and the resulting backup is smaller.

For taking redo-log-only incremental hot backups the user needs to issue the incremental backup command with the '–incremental-with-redo-log-only' option instead of the normal '–incremental' option. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --start-lsn=18974478 backup

Redo-log-only incremental backups are also compatible with the '–incremental-base' option introduced in MEB 3.7.0. An example:

$ mysqlbackup --incremental-with-redo-log-only --incremental-backup-dir=/media/backups/incr_bak1 --incremental-base=dir:/media/backups/fullback backup

To ensure the LSN values match up exactly between successive incremental backups using this option, we recommend always using the --incremental-base option when you use the --incremental-with-redo-log-only option. Using the --incremental-base option has also been described in the blog post 'Taking Incremental Backups without specifying LSN'.

It should be noted that there may be times when MEB cannot perform the redo-log-only incremental hot backup. These are the times when the redo logs of the database have been over-written and page modifications reside only within the pages themselves. In such cases the data-file based incremental backup should be taken since it will successfully backup the remaining redo-logs as well as the data files. Also, incremental backup produced by redo-log-only method is different from the current incremental backup. So, the apply-log step can not be performed on a redo-log-only backup by older versions of MEB i.e. prior to MEB 3.7.0 .

Wednesday Jan 11, 2012

MySQL Enterprise Backup: Taking Incremental Backups without specifying LSN

In its latest release MySQL Enterprise Backup (MEB 3.7.0) rolled out a new feature called 'incremental-base' which can save a lot of time and effort of the users when taking incremental backups. Let us understand this new feature and how it can be helpful:

What is an incremental backup ?

With MySQL Enterprise Backup v3.6.0 the functionality of performing incremental backups was introduced. An incremental backup is one in which only the changes made since your last backup are saved. So let's say you took a full backup of your MySQL database on 1/1/2011 and its size was 1TB. Now on 1/5/2011 the size of your database has reached to 1.1TB and you want to take another backup. Without incremental backups you would have to take a full backup and effectively backup the entire 1.1TB database For a typical user this is going to take a lot of time and disk space! Incremental backup feature comes to the rescue in such situations because with incremental backups you can save only the changes made in your database since the last backup. And this,of course, is very fast and space saving.

Taking incremental backup prior to MEB 3.7.0

Every backup done using MEB saves with itself meta-data which describes the backup along with its various parameters. This meta-data also includes two values - Start Log Sequence Number (start_lsn) and End Log Sequence Number (end_lsn). A Log Sequence Numbers is a unique ID of a log record made by the MySQL Server when any DDL/DML operations were performed. So a backup consists of all the modifications that were made from the start_lsn to the end_lsn.

Now suppose you want to take an incremental backup. This means that you want to backup only those modifications that were made in the database(s) after your last backup. Later, during the time of recovery, you will incorporate these additional changes (of incremental backup) into the previous full backup. MEB 3.6+ allows you to do this with the '–incremental' option and the '–start-lsn' option where '–start-lsn' is the end_lsn of your last backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/data/backups/incr_bak1 --start-lsn=18974478 backup

This would speedily produce a backup of fractional size as compared to the full backup and when you want to prepare your full backup for recovery you need to use the command 'apply-incremental-backup':

$ mysqlbackup --backup-dir=/media/data/backups/full_bak --incremental-backup-dir=/media/data/backups/incr_bak1 apply-incremental-backup

And there you are! Your full backup is now incorporated with all the page modifications saved in the incremental backup and is ready to be restored whenever you want. Note that when you are using apply-incremental-backup over a full backup make sure that you have used the apply-log command over the full backup before applying the incremental backup.

So this was how you took an incremental backup before MEB 3.7.0

Taking incremental backup with MEB 3.7+

In the method described above, you should have noticed that you either need to look it up or keep saved the value end_lsn of the previous backup after which you want to take an incremental backup. With the new option '–incremental-base' introduced in MEB 3.7.0 things become much easier. The backup defined by 'incremental_base' is simply the 'base' backup for your new incremental backup i.e. the backup whose end_lsn you want to use as the start_lsn for your incremental backup. So looking up the old backup directory or saving the end_lsn of your previous backup is no longer required. When you want to take an incremental backup use the –incremental-base option with the 'dir' prefix (as shown below) instead of the –start-lsn and you are ready to take a backup. On the command line:

$ mysqlbackup --incremental --incremental-backup-dir=/media/backups/incr-bak2 --incremental-base=dir:/media/backups/fullbackup backup

The theory behind incremental backup remains the same as described with the only difference that you do not need to provide the start_lsn explicitly - just point to your old backup (called the 'base' backup) using the '–incremental-base' option and MEB will extract its end_lsn automatically.

Behind the scenes

The picking up of the end_lsn of the 'base' backup is not as straight forward as it seems. To protect the backup and to make sure that the correct end_lsn is extracted MEB compares the end_lsn in the backup_history table of MySQL server (for the last backup done at the location specified by –incremental-base=dir:) with that found in the backup_variables.txt file of the 'base' backup and MEB aborts operation with an error in case the LSNs do not match. This is probably the case if the meta files of your base backup are corrupt or the values in the backup_history table are altered.

Moving the backup

Consider the case when you moved your old backup to a new location. When the old backup was performed the MySQL server saved all the details of the backup in the mysql.backup_history table. This also included the field 'backup_destination'. For the new incremental backup if you now provide –incremental-base=dir:<new location> MEB will first try to query the server's backup_history table for any previous backups performed at this location. If it doesn't find any such backups, it will extract the end_lsn found in the meta data files at the new location of your base backup and continue with the incremental backup. Similarly, if you provide –incremental-base=dir:<old location> MEB will extract the end_lsn of the previous backup done at that location from the backup_history table. After this, if it cannot find any backup at the old location (since it has been moved) it will silently continue with the incremental backup using the end_lsn found in the server's backup_history table. MEB will not continue with the backup operation if the end_lsn can be extracted from both the backup_variables.txt file as well as the server's backup_history table and the two values do not match! The description above can be summarized as follows:

A: end_lsn could be extracted from backup_variables.txt file

B: end_lsn could be extracted from backup_history table


A B both LSNs match successful backup
yes no - yes
no yes - yes
yes yes yes yes
yes yes no no
no no - no

So MEB allows you to use the '–incremental-base' option even after you have moved your previous backups. In case of any confusion or difficulty you can always use the '–start-lsn' option to provide the start_lsn explicitly.

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today