MySQL Import Tablespace
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 source database server...
If the source database server is unavailable, use DDL from a source code repository or else attempt to create DDL that matches the original table structure.USE mydatabase;
SHOW CREATE TABLE myTable\G
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;
Copy the .ibd file (from the source instance or your backup location)...
time rsync -ahr --progress /backup/mytable.ibd /var/lib/mysql/mydatabase
Set ownership and permissions...
chown mysql:mysql /var/lib/mysql/mydatabase/mytable.ibd
chmod 640 /var/lib/mysql/mydatabase/mytable.ibd
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;