X

MySQL and MySQL Community information

  • March 23, 2015

Renaming tables with MySQL Enterprise Backup 3.12.0

Introduction

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:

  1. He restores with MEB the table T1 from a TTS backup renaming it to T2.

  2. He uses MySQL client to issue SQL statements to copy the 3 mistakenly deleted rows from the table T2 to the table T1.

  3. He drops the table T2.

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.

User Interface

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.  

Example 1:

A sample command-line for restoring the table test.abc  to  test.abc_new:
$ mysqlbackup --include-tables="test\.abc"
              --rename="abc TO abc_new" 
              ...
              copy-back
In this example we assume that the backup contains several tables. Therefore, we have to specify a single table (test.abc) with the --include-tables option.

Example 2:

A sample command-line for restoring the table db.abc  to  db2.abc:

$ mysqlbackup --include-tables="db\.abc"
              --rename="abc to db2.abc"
              ...
              copy-back

Note that if the database db2 does not exist, the restore will create it.

Example 3: 

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 ...
 /home/pekka/bzr/meb-trunk/src/build/mysqlbackup 
        --backup-dir=/full-backup --include-tables=test\.abc 
        --rename=abc TO new_abc copy-back 
 mysqlbackup: INFO: 
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! 

Limitations

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. 

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.