Restoring Individual Tables

In server versions prior to 5.6, it is not possible to copy tables between servers by copying the files, even with innodb_file_per_table. However, with Percona XtraBackup, you can export individual tables from any InnoDB database, and import them into Percona Server with XtraDB or MySQL 5.6. (The source doesn’t have to be XtraDB or or MySQL 5.6, but the destination does.) This only works on individual .ibd files, and cannot export a table that is not contained in its own .ibd file.

Let’s see how to export and import the following table:

CREATE TABLE export_test (
  a int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Note

If you’re running Percona Server version older than 5.5.10-20.1, variable innodb_expand_import should be used instead of innodb_import_table_from_xtrabackup.

Exporting the Table

This table should have been created in innodb_file_per_table mode, so after taking a backup as usual with --backup, the .ibd file should exist in the target directory:

$ find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.ibd

when you prepare the backup, add the extra parameter --export to the command. Here is an example:

$ xtrabackup --prepare --export --target-dir=/data/backups/mysql/

Now you should see a .exp file in the target directory:

$ find /data/backups/mysql/ -name export_test.*
/data/backups/mysql/test/export_test.exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

These three files are all you need to import the table into a server running Percona Server with XtraDB or MySQL 5.6.

Note

MySQL uses .cfg file which contains InnoDB dictionary dump in special format. This format is different from the .exp one which is used in XtraDB for the same purpose. Strictly speaking, a .cfg file is not required to import a tablespace to MySQL 5.6 or Percona Server 5.6. A tablespace will be imported successfully even if it is from another server, but InnoDB will do schema validation if the corresponding .cfg file is present in the same directory.

Importing the Table

On the destination server running Percona Server with XtraDB and innodb_import_table_from_xtrabackup option enabled, or MySQL 5.6, create a table with the same structure, and then perform the following steps:

  • Execute ALTER TABLE test.export_test DISCARD TABLESPACE;
    • If you see the following message, then you must enable innodb_file_per_table and create the table again: ERROR 1030 (HY000): Got error -1 from storage engine
  • Copy the exported files to the test/ subdirectory of the destination server’s data directory
  • Execute ALTER TABLE test.export_test IMPORT TABLESPACE;

The table should now be imported, and you should be able to SELECT from it and see the imported data.

Percona XtraBackup
Call Us
+1-888-316-9775 (USA - Sales)
+1-208-473-2904 (USA - Sales)
+44-208-133-0309 (UK - Sales)
0-800-051-8984 (UK - Sales)
0-800-181-0665 (GER - Sales)
+1-877-862-4316 (Emergency)
+1-855-55TRAIN (Training)
+1-925-271-5054 (Training)

Table Of Contents

Previous topic

Working with Binary Logs

Next topic

LRU dump backup

This Page



© Copyright 2009-2014, Percona LLC and/or its affiliates.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.2.2.
This documentation is developed in Launchpad as part of the Percona XtraBackup source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.
]]>