MySQL and MySQL Community information

  • March 28, 2014

Faster and Space efficient Restore with MySQL Enterprise Backup

A typical backup cycle
with MySQL Enterprise Backup had always involved 3 steps:

1. backup (to

2. apply-log (prepare
the backup)

3. copy-back (restore)

In case of directory,
the 'apply-log' is done on the backup directory which does not
involve any additional space.

But in case of image,
the image has to be extracted, before doing 'apply-log'. This
involves additional space for extraction of image and the steps
involved are:

1. backup-to-image

2. Extract

3. apply-log

4. copy-back

How about, combining
the 3 steps – extract, apply-log and copy-back, so that the image
is extracted directly on the target directory (which is the datadir
of a server instance) and then apply-log is performed on the
extracted contents. This way, the need for additional space involved
in the legacy

backup -> extract -> apply-log -> copy-back

cycle is removed.

This is what is exactly
done in MySQL Enterprise Backup 3.9, where the image/directory backup
can be directly restored and prepared on the datadir through the new
option “copy-back-and-apply-log

Now the backup cycle
for a directory as well as an image, involves 2 steps:

1. backup


copy-back-and-apply-log operation combines steps 2,3 and 4 to extract
the image contents, apply the data and restore the contents onto data
directory directly at the time of restoration.

Here is a sample
command to illustrate the usage:

–backup-image=/logs/image.mbi –backup-dir=/logs/backuptmp

–backup-dir=/logs/restoretmp –datadir=/logs/restoredir
–backup-image=/logs/image.mbi copy-back-and-apply-log.

Here, the defaults-file
could be the file used on the server, or the server-my.cnf file which
is stored in the tmp directory(/logs/backuptmp) at the time of image

Note: server-my.cnf and server-all.cnf files are created by MySQL Enterprise Backup in the backup directory. These files are the replica of the server configuration used at the time of backup. These files help in restoring the backup with exactly the same server configuration that existed while taking backup. 

Advantages of using

  • No additional

    space required for image extraction

  • Time spent on

    performing multiple operations to restore a backup is reduced.

  • Overhead involved

    in verifying the success of intermediate operations is reduced.

The operation
copy-back-and-apply-log can be performed on the following backups:

  • Directory backup

  • Image backup

  • Incremental backup

  • Incremental image


  • Partial backup

    (--include, --use-tts)

  • Image stored on


  • Compressed backup

Note 1 : Incremental
backup can be restored directly on the datadir by specifying
“--incremental” option in copy-back-and-apply-log.

Incremental backups can
be directly applied onto the data directory specified by the user and
MEB will validate the LSNs when applying incremental backups.

Note 2 : Compressed
backup can be restored directly on the datadir by specifying
“--uncompress” option in copy-back-and-apply-log.

Here, MEB uncompresses
the files on the fly.

This operation is
helpful to stream the backup image and restore at the destination in
one shot.

For more
details, refer restore
directly from backup stream
 and  restore operation

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.