X

MySQL and MySQL Community information

  • September 5, 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.

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.