Buy Percona SupportEmergency? Get 24/7 Help Now!

Importing and Exporting Individual Tables

In standard InnoDB, it is not normally possible to copy tables between servers by copying the files, even with innodb_file_per_table enabled. But XtraBackup allows to migrate individual table from any InnoDB database to Percona Server with XtraDB.

The table is required to be created with the option innodb_file_per_table enabled in the server, as exporting is only possible when table is stored in its own table space.

The importing server (at the moment it only supported by Percona Server) should have innodb_file_per_table and innodb_expand_import options enabled.

Exporting tables

Exporting is done in the preparation stage, not at the moment of creating the backup. Once a full backup is created, prepare it with the --export option:

$ innobackupex --apply-log --export /path/to/backup

This will create for each InnoDB with its own tablespace a file with .exp extension. An output of this procedure would contain:

..
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'mydatabase/mytable' to file
`./mydatabase/mytable.exp` (1 indexes)
..

Each .exp file will be used for importing that table.

Importing tables

To import a table to other server, first create a new table with the same structure as the one that will be imported at that server:

OTHERSERVER|mysql> CREATE TABLE mytable (...) ENGINE=InnoDB;

then discard its tablespace:

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

After this, copy mytable.ibd and mytable.exp files to database’s home, and import its tablespace:

OTHERSERVER|mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Once this is executed, data in the imported table will be available.

This documentation is developed in Launchpad as part of the Percona XtraBackup 1.6.
If you spotted inaccuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.


General Inquiries

For general inquiries, please send us your question and someone will contact you.