Friday Sep 05, 2014

Selective Restore of InnoDB Tables with MySQL Enterprise Backup 3.11

Introduction

Sometimes the best way to repair data issues and problems within a MySQL database is to restore only some of the tables from a backup. For example, suppose that some data was accidentally deleted from one table due to a software error, then the easiest way to recover the lost data might be to restore only one table from a backup. Previously this kind of partial restore was not supported by MySQL Enterprise Backup (MEB). However, MEB 3.11 introduces support for selective restore from backups created with the --use-tts option (or TTS backups).

TTS backups are backups that are created with the transportable tablespaces feature of InnoDB. These backups consist of InnoDB tables that have their own tablespaces (.ibd files). A TTS backup can be restored only to a running server, because MEB needs the server to execute SQL statements during the restore.

In selective restore the user specifies the names of the tables to be restored from a TTS backup using the --include-tables=REGEX1 or the --exclude-tables=REGEX2 option (or both), where REGEX1 and REGEX2 denote regular expressions. A table is restored from a backup if the fully-qualified name of the table (that is, a name of the form DATABASE.TABLE) matches REGEX1 and does not match REGEX2.

Selective restore does not overwrite any existing tables at the server. So, the user should first delete (or rename) all those tables at the server that are going to be restored from a backup.

Examples

The following examples demonstrate how selective restore can be used. Suppose there is a database called "color" with five InnoDB tables that have their own tablespaces:

  blue1
  blue2
  dark_blue
  light_blue
  red

We have created a TTS backup of the "color" database with the following mysqlbackup command

$ mysqlbackup --include-tables="^color\." --use-tts --backup-dir=/full-backup 
              backup

and prepared the backup in the "/full-backup" directory with

$ mysqlbackup --backup-dir=/full-backup apply-log

Let's now assume that the table "red" is now broken and we wish to restore it from the backup. We must first delete the table by issuing a DROP TABLE statement with the MySQL client. After removing the table we can restore it from a TTS backup as follows

$ mysqlbackup --backup-dir=/full-backup --include-tables="^color\.red$" 
              copy-back

Here we specified a single table with the --include-tables option.

In the next example we work with the same "color" database as above, but we make a backup in a single file

$ mysqlbackup --include-tables="^color\." --use-tts --backup-dir=/full-backup 
              --backup-image=/backups/image backup-to-image

The created backup image contains all five InnoDB tables of the "color" database.

Now we wish to restore the tables "blue2", "dark_blue" and "light_blue" from the backup image. But before we can do this, we have to drop these tables at the server. After that the following mysqlbackup command restores these three tables.

$ mysqlbackup --backup-image=/backups/image --backup-dir=/full-backup 
              --include-tables="^color\..*blue.*$" --exclude-tables="blue1" 
              copy-back-and-apply-log

We have specified the tables to be restored with the --include-tables and --exclude-tables options illustrating the power of regular expressions in specifying the set of tables to be restored.

If only a few tables are to be restored, then usually the easiest way to do this is to specify these tables with the --include-tables option. The mysqlbackup command shown below restores the tables "blue2" and "dark_blue" from a single-file backup.

$ mysqlbackup --backup-image=/backups/image --backup-dir=/full-backup 
              --include-tables="^color\.blue2$|^color\.dark_blue$" 
              copy-back-and-apply-log

A user more familiar with regular expressions might want to replace the value for the --include-tables option in the example above with

--include-tables="^color\.(blue2|dark_blue)$"

which is an equivalent but shorter regular expression.

Working with multiple InnoDB datafile formats

There are currently two InnoDB datafile formats - the older Antelope format and the newer Barracuda format that offers features not available with the Antelope format. If either the server or the backup contain InnoDB tables in different formats, then this might cause problems for selective restore. Fortunately, these problems can be solved by invoking the restore command with suitable options.

To understand this problem we have to look at how InnoDB database engine creates new tables.  When an InnoDB table that has its own datafile (.ibd file) is created at the server, the format of the InnoDB datafile is determined by the CREATE TABLE statement and the value of the innodb_file_format system variable. If the CREATE TABLE statement specifies features that require the Barracuda format and the value of the innodb_file_format system variable is also "Barracuda", then the datafile is created in Barracuda format. But if the value of innodb_file_format is "Antelope", then the format of the datafile will be "Antelope" even if the CREATE TABLE statement actually specified features requiring the Barracuda file format.

During selective restore MEB issues first a CREATE TABLE statement (obtained from the backup) to create an empty table at the server. After this, the original (empty) datafile is replaced with the datafile copied from the backup. But this step fails, if the original datafile and the datafile in the backup are not in the same format. This happens, for example, if the datafile in the backup is in Barracuda format, but the value of the innodb_file_format system variable is "Antelope" when selective restore is attempted.

This problem can be solved by specifying the --force option for the mysqlbackup command-line invoking selective restore. With the --force option, mysqlbackup changes temporarily the value of the innodb_file_format system variable for each table that is restored. This quarantees that selective restore succeeds regardless of the format of InnoDB tables in the backup and the value of innodb_file_format, provided, of course, that other users do not change the value of innodb_file_format during the restore.

Warnings

The integrity of the database is not enforced by MEB. Instead, it is the user's responsibility to keep track of the foreign-key constraints on the restored data.

Limitations

Selective restore is not possible from TTS backups created in the full-locking mode (specified with --use-tts=with-full-locking). Selective restore works only with TTS backups created in the minimum-locking mode (--use-tts=with-minimum-locking), which is the default mode for TTS backups.

Friday Mar 28, 2014

Faster and Space efficient Restore with MySQL Enterprise Backup

The new operation 'Copy-back-and-apply-log' introduced in MySQL Enterprise Backup 3.9.0 helps in faster restoration and helps in reducing the amount of space involved in restoring the backup contained in an image, because of elimination of the intermediate step of extraction of image contents. 

[Read More]

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 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
  
       
Today