Expand Table Import

Unlike MyISAM, InnoDB does not allow users to copy datafiles for a single table between servers. If exported with XtraBackup, a table can now be imported on another server running XtraDB.

This feature implements the abililty to import arbitrary .ibd files exported using the XtraBackup --export option. The innodb_expand_import variable makes to convert .ibd file during import process.

The normal version can import only the backed-up .ibd file at the same place.

Note

This feature is unsupported with InnoDB data files created with MySQL 5.0 and MySQL 5.1 prior to version 5.1.7 due to InnoDB file format limitation. It may work in some cases, but may result in crashes on import as well, see bug #1000221 and bug #727704 for examples and details.

Percona Server 5.5.28-29.2 extended the innochecksum with an option -f to read the file format information from a given InnoDB data file. As only the first page needs to be read to detect the format/version information, it can also be used on a running server. Example of the output should look like this:

$ innochecksum -f ibdata1
Detected file format: Antelope (5.1.7 or newer).

Example

Assuming that:

  • innodb_expand_import is set to 1.
  • the files (.ibd and .exp) are prepared by the xtrabackup --prepare --export command.

First create “exactly same” structured tables to the target database.

Then discard the tables as preparation of import, for example,

mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table customer discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table district discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table history discard tablespace;
Query OK, 0 rows affected (0.00 sec)

...
put the .ibd and .exp files at the same place to .frm file.
import the tables
(command example)
mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_expand_import=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table customer import tablespace;
Query OK, 0 rows affected (0.17 sec)

mysql> alter table district import tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table history import tablespace;
Query OK, 0 rows affected (0.04 sec)

...
(.err file example)
InnoDB: import: extended import of tpcc2/customer is started.
InnoDB: import: 2 indexes are detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
InnoDB: import: extended import of tpcc2/district is started.
InnoDB: import: 1 indexes are detected.
InnoDB: Progress in %: 16 33 50 66 83 100 done.
InnoDB: import: extended import of tpcc2/history is started.
InnoDB: import: 3 indexes are detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
...

Version Specific Information

System Variables

variable innodb_expand_import
Version Info:
Command Line:

Yes

Config File:

Yes

Scope:

Global

Dynamic:

Yes

Variable Type:

ULONG

Default Value:

0

Range:

0-1

If set to 1, .ibd file is converted (space id, index id, etc.) with index information in .exp file during the import process (ALTER TABLE ... IMPORT TABLESPACE command).

This variable was renamed to innodb_import_table_from_xtrabackup, beginning in release 5.5.10-20.1. It still exists as innodb_expand_import in versions prior to that.
variable innodb_import_table_from_xtrabackup
Version Info:
Command Line:

Yes

Config File:

Yes

Scope:

Global

Dynamic:

Yes

Variable Type:

ULONG

Default Value:

0

Range:

0-1

If set to 1, .ibd file is converted (space id, index id, etc.) with index information in .exp file during the import process (ALTER TABLE ... IMPORT TABLESPACE command).

This variable was added in release 5.5.10-20.1. Prior to that, it was named innodb_expand_import, which still exists in earlier versions.
© Copyright Percona LLC and/or its affiliates 2009-2014.
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 Server 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.