Thursday Sep 19, 2013

Backing up selective innodb tables using MEB.

MySQL 5.6 introduced the TTS(transportable table spaces) feature which enables moving a table from one server to another. This feature coupled with MEB 3.9 enables backing up a set of tables matching (regex specified with) the –include option.

The backup of selective tables using transportable tablespaces feature of innodb is referred as tts/selective backup in the remainder of the section.

The difference between a regular partial backup and with using tts is that the regular partial backups are stand alone and cannot be plugged into a another server where as the tts backups in contrast enables the tables to be plugged into another server instance

The Selective Backup Operation

To specify a set of tables to be backed up use the --use-tts option along with the --include=[regex] option for the backup operation. --use-tts option supports two values with-minimum-locking and with-full-locking.

with-minimum-locking -  This is the default option. The tables being backed up are hot copied in parallel along with the redo log. After the data file copy, the tables are locked in read only mode, the delta of the log is copied into the backup, and the locks are released. The advantage of this option is that the tables are available for modifications during most part of the back up process and are available in read only mode for a short duration.

with-full-locking - With this options the tables are locked in read only mode during the entire duration of the backup. As there cannot be any modifications while the backup is happening, the tables are consistent and the redo log is not backed up. This saves space and makes the apply log step faster as it just involves some book keeping operations.

mysqlbackup --port=3306 --protocol=tcp --user=root –backup-dir=backupdir –include=Sales.Sales_*  --use-tts backup-and-apply-log

The Restore(copy-back) Operation

Restoring from a tts backup requires the server to be running in case of a tts backup unlike the other types of backup. The connection options of the server where the set of tables have to be restored need to be provided for the copyback of a tts backup.

mysqlbackup --defaults-file=/backup-my.cnf --port=3406 --protocol=tcp --user=root --backup-dir=backupdir --datadir=<target_server_datadir> copy-back

Advantages of using MEB for tts Backup

Have a backup strategy for backing a subset of tables(for eg: backing up only important/most used tables).
Take advantage of compressed and image backup options supported by MEB.
This feature can effectively be used to copy a set of innodb tables from one server instance to another.  

This feature handles only tables having their own tablespaces(innodb_file_per_table on) and does not support partitioned tables.

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.


    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.

MySQL MEB Team Blog


« September 2013 »