This is a small article telling how to move a innoDB table from one MySQL server to another.

Moving MyISAM table from one server to another is very straight forward process. MyISAM table generates 3 files at file system level.

• .frm file contains table structure/definition
• .MYD file contains table data
• .MYI file contains index data

We can simply copy these 3 files to another MySQL server installation under desired database and it will show up in “SHOW TABLES” command.

But things are different in InnoDB, because InnoDB also stores metadata in default tablespace represented by ibdata* datafiles in data directory.

InnoDB table has 2 types of files generated

• .frm file contains table structure/definition
• .ibd file contains data of table as well as index

Apart from above files, we also have metadata like table ID, database name and transaction information about tables in default tablespace (ibdata1 datafile).

This makes InnoDB table special and, simply copying .frm and .ibd files from source server to destination server will not migrate/copy InnoDB table to different server.

We need to do additional steps to complete the copy/migration. Please follow below steps to copy InnoDB table from one server to another.

 

Step 1) On destination database, create required database if not available.

So lets say, I want to move one table called flavors from fctest database on source server to destination server.

For that to happen, I have to make sure that on destination server, I have fctest database created. If its not created, create the same.

You need to do this, even if you want to move this table to some other existing database on destination server.

We have to first move the table to fctest database on destination (same as source) and then we will rename the same to desired database.

On destination server:

root [deo] >show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| deo                |
| menagerie          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

I don’t have fctest database. Creating the same.

root [deo] >create database fctest;
Query OK, 1 row affected (0.00 sec)

root [deo] >
root [deo] >use fctest;
Database changed
root [fctest] >

Step 2) Create empty table structure same as source table

Once you create the database fctest, create empty table flavors whose structure/definition is same as source.

You can get the definition from source using “SHOW CREATE TABLE” command

On Source server:

mysql> show create table flavors;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                       |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| flavors | CREATE TABLE `flavors` (
  `flavor_id` int(11) NOT NULL AUTO_INCREMENT,
  `build_type` varchar(255) NOT NULL DEFAULT '',
  `variant` varchar(255) NOT NULL DEFAULT '',
  `threads` varchar(255) NOT NULL DEFAULT '',
  `first_event_id` int(11) NOT NULL DEFAULT '0',
  `last_event_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`flavor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Create the same on destination server

On Destination server:

root [fctest] >CREATE TABLE flavors (
    ->   flavor_id int(11) NOT NULL AUTO_INCREMENT,
    ->   build_type varchar(255) NOT NULL DEFAULT '',
    ->   variant varchar(255) NOT NULL DEFAULT '',
    ->   threads varchar(255) NOT NULL DEFAULT '',
    ->   first_event_id int(11) NOT NULL DEFAULT '0',
    ->   last_event_id int(11) DEFAULT NULL,
    ->   PRIMARY KEY (flavor_id))
    ->  ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

root [fctest] >show tables;
+------------------+
| Tables_in_fctest |
+------------------+
| flavors          |
+------------------+
1 row in set (0.00 sec)

Step 3) Discard tablespace on destination server

This step is required so that MySQL will discard current .ibd file generated for this table.

root [fctest] >ALTER TABLE flavors DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

Step 4) Copy .frm and .ibd file from source server to destination server

At this point copy the .frm and .ibb files from source server to destination server.

Before copying these files, make sure that no transactions are happening on this table (which you are migrating).

Safest way is to use “FLUSH TABLE <table_name> WITH READ LOCK” on source database which will flush table data and takes read only lock so that no DML will happen on the table.

While you keep that session active, copy the .frm and .ibd files to destination server.

Step 5) Import tablespace on destination server

Once you copy .frm and .ibd files to destination server, import the tablespace, which will make required metadata imported into default tablespace and accept the .ibd file.

root [fctest] >ALTER TABLE flavors IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)

root [fctest] >show tables;
+------------------+
| Tables_in_fctest |
+------------------+
| flavors          |
+------------------+
1 row in set (0.00 sec)

root [fctest] >select count(1) from flavors;
+----------+
| count(1) |
+----------+
|       29 |
+----------+
1 row in set (0.00 sec)

Step 6) Rename the table to desired database

Since we wanted to move this table to deo database on destination server, we can simply run below rename command and it will move the tables to deo database.

root [fctest] >rename table fctest.flavors to deo.flavors;
Query OK, 0 rows affected (0.00 sec)

you can later drop fctest database on destination server.

Hope this helps !!