Use this procedure to take a file-per-table .ibd file from one instance and import it into another. You might want to do this because you have lost the system tablespace for your instance. You may also choose to shut down your source instance and use this method to transfer a tablespace from one database to another without using tools like mysqldump, mysqlpump, MySQL Enterprise Backup or Percona XtraBackup. Note that any Routines (e.g. Stored Procedures, Functions or Triggers) cannot be transferred using this method as theire definitions are stored in the system tablespace.

Assumptions

• Your source and target databases are at the same major version (e.g. 5.7).

• You only need to copy file-per-table .ibd files (restore of general tablespace .ibd files using this process is not supported).

• The .ibd file was copied from a normally shut down instance of MySQL.

Create Database

A database with the same name as the source database needs to exist on the target server. Create it if necessary...

CREATE DATABASE mydatabase;

Create Table

On the target database server...

USE mydatabase;

CREATE TABLE mytable...

Replace Tablespace

On the target database server...

Discard the tablespace for the empty table you just created...

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

Import the tablespace...

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

SHOW WARNINGS;

These warnings are normal...

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning | 1814 | InnoDB: Tablespace has been discarded for table 'mytable' |

| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './mydatabase/mytable.cfg', will attempt to import without schema verification |

+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------+

Test...

DESCRIBE mydatabase.mytable;

SELECT COUNT(*) FROM mydatabase.mytable;

Bibliography