Move innodb tablespace from one to another server

A post from dbpandit

This blog is about transferring tablespace from one to another server in case of data loss or disaster recovery of specific tables in quickest way.

prerequisites : innodbfileper_table should be 1 at both source and destination servers. This variables comes enable by default in MySQL 5.6

In order to move InnoDB tablespace, below steps should be followed:

Note: Following steps consider table : t1 for movement from source to destination servers.

Step 1) create table t1 (innodb) at destination server, if not already exists.

Step 2) At destination server :
alter table t1 discard tablespace;

This will discard current tablespace and open doors for import new one from source.

Step 3) At source server: flush table t1 for export;
If there is any parent/child foreign key constraint enabled, you should set foreignkeychecks=0; first.
Also, When a table is quiesced, only read-only transactions are allowed on the affected table and this operation is not allowed on partitioned tables.

Note : Page size should be same at both source and dest server to transfer tablespace.You can check it on both instance by using utility : getconf. Just run the below command on your box as : getconf PAGESIZE

Issuing command : flush table t1 for export; will generate .cfg file in db dir which contains metadata info about table. Though, it's not required for importing table at destination server but as a verification process, mysql use it for comparing table schema before to import tablespace.

Step 4) Transfer .ibd and .cfg of table t1 from source to destination server:
scp /var/lib/mysql/db/t1.{cfg,ibd} user@ip:/var/lib/mysql/db/

Don't forget to change owner of newly created ibd and cfg files. Use this:
chown mysql:mysql /var/lib/mysql/db/t1.{cfg,ibd}

Step 5) Source server: Issue following commands -- Unlock tables; and

Step 6) destination server : import tablespace by issuing following command:
alter table t1 import tablespace;

and you are done.

Note : The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.

Ref :