Thursday Sep 19, 2013

How to restore directly on a remote machine from the backup stream

MySQL Enterprise Backup has been improved to support single step restore from the latest release 3.9.0. It enables you to restore the backup image to remote machine in single step. However, first you would have to create the backup image in local disk, copy the backup image to remote machine, and then restore in remote machine by running copy-back-and-apply-log command.

This approach has two overheads:

    Serial execution: You have to wait for each step to finish before beginning the next (e.g. You must have to wait for backup-to-image operation to finish before beginning copy).
    Disk consumption: You might not have enough space on the source disk to store that backup-image in the first place.

By means of restoring directly on a remote machine via piping backup stream over SSH, you could overcome both these problems.
That means, 
You don't have to store the backup contents anywhere,
Pipe backup stream directly to remote machine,
Optionally, perform compression and decompression on the fly and
Perform restore operation simultaneously.

How to do it:

    Use SSH and pipes to transfer data between backup and restore operations, and
    Perform the backup to stream and restore in remote machine simultaneously.

Steps:

    a) perform image backup and stream the data to stdout --backup-image=- --backup-to-image
    b) pipe the stdout to remote server using ssh and restore data using copy-back-and-apply-log.
Sample command:

mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock  --backup-image=- backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --backup-image=- copy-back-and-apply-log'


In case of slower network, you could perform compressed backups to reduce the network traffic.  Compressed backups would require more cpu cycles, but provides faster data transfer.
Sample command with compression:

mysqlbackup --user=root --port=3306 --backup-dir=backup --socket=/tmp/mysql.sock  --backup-image=-  --compress backup-to-image | ssh <user name>@<remote host name> 'mysqlbackup --backup-dir=backup_img --datadir=/data/datadir --innodb_log_group_home_dir=. --innodb_log_files_in_group=8 --innodb_log_file_size=5242880 --innodb_data_file_path="ibdata1:12M:autoextend" --uncompress --backup-image=- copy-back-and-apply-log'


On successful completion of above command, your remote server is being restored and ready to use. This would also be useful to create a data snapshot for replication without any additional storage space.
About

MySQL MEB Team Blog

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
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
   
       
Today