MySQL Enterprise Backup 3.12.0 (MEB) introduces a new feature for restoring an InnoDB table from a backup. Now it is possible to rename the table during restore. This is useful when the user wants to restore a table from a backup without overwriting the existing version of the table in the database.
The following example illustrates how the renaming feature could be used. Suppose that the DBA has deleted three rows from a table T1 by mistake and he now wishes to get them back from a backup. He wants to leave the database online and to restore the 3 deleted rows from a TTS backup (a backup created with the --use-tts option) that contains the table T1. He can do this with this feature in three steps:
Now the accidentally dropped rows have been restored and the restore took place when the MySQL server was online and the restore did not disturb the normal operation of the server in any way.
The command-line interface for restore is extended with the --rename option that specifies a mapping of from the old name to the new name. The --rename option has the following syntax:
--rename="OLD-NAME to NEW-NAME"
The OLD-NAME and NEW-NAME are either fully-qualified tablenames of the form DB.TABLE, or tablenames without the database part. The OLD-NAME must match the name of the table selected for restore.
$ mysqlbackup --include-tables="test\.abc"
--rename="abc TO abc_new"
A sample command-line for restoring the table db.abc to db2.abc:
$ mysqlbackup --include-tables="db\.abc"
--rename="abc to db2.abc"
Note that if the database db2 does not exist, the restore will create it.
Below is an excerpt of the printouts MEB produces when a table is renamed during restore using the command-line from Example 1:
$ mysqlbackup --backup-dir=/full-backup --include-tables="test\.abc" --rename="abc TO new_abc" copy-back
MySQL Enterprise Backup version 3.12.0 Linux-3.2.0-69-generic-i686 [2015/01/22]
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
mysqlbackup: INFO: Starting with following command line ...
--rename=abc TO new_abc copy-back
IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'copy-back' run mysqlbackup
prints "mysqlbackup completed OK!".
150312 11:17:53 mysqlbackup: INFO: MEB logfile created at /full-backup/meta/MEB_2015-03-12.11-17-53_copy_back.log
mysqlbackup: INFO: MySQL server version is '5.6.11'.
mysqlbackup: INFO: Got some server configuration information from running server.
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150312 11:17:53 mysqlbackup: INFO: Copy-back operation starts with following threads
1 read-threads 1 write-threads
150312 11:17:53 mysqlbackup: INFO: Creating table: test.abc.
150312 11:17:53 mysqlbackup: INFO: Copying /full-backup/datadir/test/abc.ibd.
150312 11:17:53 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150312 11:17:54 mysqlbackup: INFO: Importing table: test.abc and renaming it to test.new_abc.
150312 11:17:55 mysqlbackup: INFO: Analyzing table: test.new_abc.
150312 11:17:55 mysqlbackup: INFO: Copy-back operation completed successfully.
150312 11:17:55 mysqlbackup: INFO: Finished copying backup files to '/sqldata/tts-5.6'
mysqlbackup completed OK!
Renaming works only when restoring a single table from a TTS backup (a backup created with the --use-tts option). If the backup contains multiple tables, then a single table should be specified for restore with the --include-tables and --exclude-tables options.