Faster and Space efficient Restore with MySQL Enterprise Backup
By jothir on Mar 28, 2014
A typical backup cycle with MySQL Enterprise Backup had always involved 3 steps:
1. backup (to directory)
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:
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:
where 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:
./mysqlbackup –backup-image=/logs/image.mbi –backup-dir=/logs/backuptmp backup-to-image
./mysqlbackup –defaults-file=/logs/backuptmp/server-my.cnf –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 backup.
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 copy-back-and-apply-log:
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:
Incremental image backup
Partial backup (--include, --use-tts)
Image stored on tape
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.