Copying Tablespaces to Another Server (Transportable Tablespaces)

If you want take several actions on tables? It is really very exciting 😛 Start MySQL 5.6.17(as we have) with:

    [mysqld] 
    innodb_file_per_table = 1 
    lower_case_table_names = 1

Then create a huge table or find one from friend’s database to testing. First off all let’s to rename table which is nearly 2.4 gigs.

[root@linuxsrv3 xxx]# ls -lthr 
    total 2,4G 
    -rw-rw----. 1 mysql mysql 65 2014-05-07 11:05 db.opt
    -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table.frm
    -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table.ibd

We have renamed test_table to test_table2.:

    [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table to xxx.test_table2' 
    Enter password: 
    real 0m1.973s 
    user 0m0.002s 
    sys 0m0.004s 

It takes only 1.9 second. So what if we want to move table from one database to another. There is no need to backup this table and then import. Use Rename.

   [root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table2 to test.test_table2' 
   Enter password: 
   real 0m1.876s 
   user 0m0.005s 
   sys 0m0.003s 

And very mysteriously our table is in test database now:

   [root@linuxsrv3 test]# ls -lthr 
   total 2,4G 
   -rw-rw----. 1 mysql mysql 8,4K 2014-05-12 11:32 ff.frm 
   -rw-rw----. 1 mysql mysql 96K 2014-05-12 11:33 ff.ibd 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 11:34 db.opt 
   -rw-rw----. 1 mysql mysql 20K 2014-05-12 12:07 test_table2.frm 
   -rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table2.ibd

So quite fast way to move tables. But what about moving one table from one server to another server? Here is a show of transportable tablespaces begin. If you want to use this features you must have a MySQL 5.6.x a good one the latest GA. and it must be started as innodb_file_per_table = 1. Ofcourse you must have identical database names and table names on both side. We have already test_table2 table in test database on linuxsrv3. Created on linuxsrv4 same database and table. Our source server is: linuxsrv3 Destination server is: linuxsrv4 On destination server discard existing tablespace:

    mysql> use test;  
    mysql> alter table test_table2 discard tablespace; 
    Query OK, 0 rows affected (0,05 sec)

If you check you will see no .ibd file on database directory:

    [root@linuxsrv4 test]# ls db.opt test_table2.frm

On source server:

    mysql> use test; 
    Database changed 
    mysql> FLUSH TABLES test_table2 FOR EXPORT; 
    Query OK, 0 rows affected (0,00 sec)

Copy .ibd file to destination server:

    [root@linuxsrv3 test]# scp test_table2.ibd root@192.168.1.88:/var/lib/mysql/test root@192.168.1.88's password: 
    test_table2.ibd 100% 2444MB 15.4MB/s 02:39 

On source server:

    mysql> use test;  
    Database changed 
    mysql> UNLOCK TABLES; 
    Query OK, 0 rows affected (0,00 sec)

On destination server:

   [root@linuxsrv4 test]# ls 
   db.opt 
   test_table2.frm 
   test_table2.ibd 

   mysql> use test; 
   Database changed 
   mysql> alter table test_table2 IMPORT TABLESPACE; 
   ERROR 1146 (42S02): Table 'test.test_table2' doesn't exist

If you see such error it indicates that there is an owner issue:

   [root@linuxsrv4 test]# ls -l 
   total 2502684 
   -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
   -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
   -rw-r-----. 1 root root 2562719744 2014-05-12 15:16 test_table2.ibd

Change file owner to mysql:

    [root@linuxsrv4 test]# chown mysql:mysql test_table2.ibd 
    [root@linuxsrv4 test]# ls -l total 2502684 
    -rw-rw----. 1 mysql mysql 65 2014-05-12 15:03 db.opt 
    -rw-rw----. 1 mysql mysql 20166 2014-05-12 15:05 test_table2.frm 
    -rw-r-----. 1 mysql mysql 2562719744 2014-05-12 15:16 test_table2.ibd

And now try again:

    mysql> use test;  
    Database changed 
    mysql> alter table test_table2 IMPORT TABLESPACE; 
    Query OK, 0 rows affected, 1 warning (2 min 29,38 sec) 

And checking :

    mysql> select idvisit from test_table2 order by idvisit desc limit 1; 
    +---------+ 
    | idvisit | 
    +---------+ 
    | 7016951 | 
    +---------+ 
    1 row in set (0,04 sec)

That’s all in such manner you can move one or more tables to another server for testing or just for what you need.

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s