Wednesday Mar 28, 2012

Announcing MySQL Enterprise Backup 3.7.1

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.1, a maintenance release version that includes bug fixes and enhancements to some of the existing features.

The most important feature introduced in this release is Automatic Incremental Backup. The new  argument syntax for the --incremental-base option is introduced which makes it simpler to perform automatic incremental backups. When the options --incremental & --incremental-base=history:last_backup are combined, the mysqlbackup command  uses the metadata in the mysql.backup_history table to determine the LSN to use as the lower limit of the incremental backup. You no longer need to keep track of the actual LSN (as in the option --start-lsn=LSN) or even the location of the previous backup (as in the option --incremental-base=dir:directory_path)

This release also incudes various bug fixes related to some options used in MEB. The most important are few of them as listed below,

1. The option --force now allows overwriting InnoDB data and log files in  combination with the apply-log and apply-incremental-backup options, and replacing the image file in combination with the backup-to-image and backup-dir-to-image options.

2. Resolved a bug that prevented MEB to interface with third-party storage managers to execute backup and restore jobs in combination with the SBT interface and associated --sbt* options for mysqlbackup.

3. When MEB is run with the copy-back option,  it now displays warnings as existing files are overwritten.

For more information about other bug fixes, please refer to the change-log in http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/meb-news.html

The complete MEB documentation is located at http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html.

You will find the binaries for the new release in My Oracle Support,  https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature. If you haven't looked at MEB 3.7.1 recently, please do so now and let us know how MEB works for you. Send your feedback to mysql-backup_ww@oracle.com.


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.

Tuesday Jan 10, 2012

Announcing MySQL Enterprise Backup 3.7.0

The MySQL Enterprise Backup (MEB) Team is pleased to announce the release of MEB 3.7.0, with several exciting and advanced features to benefit a wide audience. Included in this release are,

 The gist and usefulness of all these new features are described in short below,

Redo Log Only Incremental Backup:
This is a new type of incremental backup that copies only the InnoDB redo log accumulated since the previous full or incremental backup. The original incremental backup technique copies from the InnoDB data files only those pages that were modified since the previous backup. This incremental backup technique based on the redo log is much faster in most cases than incremental backups that read the data files, if incremental backups are taken frequently. You can choose this new method by specifying the --incremental-with-redo-log-only option on the mysqlbackup command line.

Incremental Backup without specifying LSN:
The new option --incremental-base=dir:/  helps you to perform incremental backup without needing to specify lsn value. The input to --incremental-base=dir:/ option is location of any previous backup over which incremental backup is to be done.

Performance Improvements:
Performance of backup-related I/O operations is improved, particularly on Windows, by reusing I/O library code and best practices from the MySQL Server product. To avoid memory fragmentation and overhead from frequent malloc() / free() sequences, the mysqlbackup command now does all read, compress, uncompress, and comparison operations within a fixed-size buffer that remains allocated while the command runs.

Validation of Backup Image using Checksums:
The new validate option of the mysqlbackup command tests the individual files within a single-file backup using a checksum mechanism. Validation helps to verify the integrity of the single-file backup image as the image file is moved between servers and thereby ensure that backups are reliable and consistent.

Hot Backup of InnoDB .frm files
:
With this new feature, you do not have to manually copy the .frm files to perform restore. The new option --only-innodb-with-frm performs an InnoDB-only backup and backs up even the .frm files of InnoDB tables in non locking mode. Formerly, the InnoDB-only backup required putting the database briefly into a read-only state and copying the .frm files within your own backup script.

Enhancements to Third-Party Media Managers
:
 To customize the interactions between MySQL Enterprise Backup and media management software (MMS), the --sbt-environment option lets you pass application-specific environment settings to the MMS (for example, Oracle Secure Backup). Each vendor that uses the SBT programming interface could implement its own set of environment variables. The --sbt-environment variable lets you pass environment variable values from any invocation method (for example, a Makefile) rather than setting and unsetting the variables within a wrapper shell script.

For more information about MEB features and examples, please see the MEB documentation located <http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/index.html>. My sincere thanks to Lars Thalmann, Sanjay Manwani and all the MEB team members, who have provided valuable features and improvements for every release.

Download the MEB 3.7.0 package from the Oracle Software Delivery Cloud web site <https://edelivery.oracle.com/>. MySQL Enterprise customers can begin deploying MEB 3.7.0 immediately. Users without a MySQL Enterprise license can evaluate MEB 3.7.0 for free for 30 days; please try it out and send your feedback to mysql-backup_ww@oracle.com.



Monday Nov 28, 2011

MySQL 5.5.18 Debian packaging now available

I am happy to announce that MySQL 5.5.18 is now available via Debian native packaging.  We have gotten many requests for this and our build and release teams have pulled together to ensure that our DEB packages are delivered with the highest quality. 

You can download MySQL 5.5.18 Debian 5 and 6 packages from the MySQL Community Download page or from the My Oracle Support portal.

As always, thanks for your continued support of MySQL!

Tuesday Jul 19, 2011

MySQL Enterprise Backup 3.6 - New backup streaming, integration with Oracle Secure Backup and other common backup media solutions

All DBAs understand the importance and priority of quick, reliable database backup and recovery operations.  In fact, dating back to my early days with MySQL, the most commonly requested product features from the MySQL user base have been around online, non-blocking backup solutions for running MySQL servers.  In response, Oracle now provides MySQL Enterprise Backup ("MEB") which performs high performant, online "hot" backups for MySQL databases.  MEB provides all of the backup/recovery features and functionality DBAs expect, all from a scriptable command line interface.  You can learn all about MEB in the related MySQL docs.

My congratulations and appreciation go out to Lars Thalmann and the MySQL Enterprise Backup engineering team for the recent release of MEB 3.6.  While there are many great improvements in this specific release, as an operational DBA I am most excited about the new support for single file streaming and for the SBT interface features, described here:

Single File Streaming - This allows DBAs to offload the footprint of backup images to a different server or storage device without having to store them locally on the MySQL database server.  This removes storage and related overhead from the server being backed up and speeds up total backup time by removing the need to copy local backup images (which even when compressed can be very large) over the network to their ultimate network destination.  You can learn about this specific MEB option along with a good usage example here.

Support for SBT interface - The "Secure Backup to Tape" interface was originally developed by Oracle as a standard way for third-party backup media providers to easily integrate their solutions with Oracle Recovery Manager ("RMAN").  SBT is now supported in MEB 3.6 so MySQL backup images can now be generated by and streamed directly to advanced enterprise backup media management solutions (Oracle Secure Backup, Symantec Netbackup, most others) that are already deployed within an environment.  This simplifies MySQL administration by enabling DBAs to incorporate MySQL backup/recovery operations and media rotation/retention policies into existing standard operating procedures.  You can learn all about this new option, again with a useful example here.

MySQL Enterprise Backup is part of the commercial MySQL Enterprise Edition but like all Oracle products is free to download and use without obligation for 30 days.  This is a great way to try it out to see if it fits your needs.  

You can download and begin working with MEB 3.6 now:

1. Go to Oracle eDelivery.
2. Enter some basic details and click through the agreement.
3. Select "MySQL Product Pack", then your platform, then Go.


I will keep you posted as new MySQL product features and interesting Oracle integrations become available.  As always, thanks for your continued support of MySQL! 
About

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

Twitter


Facebook

Search

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