mysql innodb数据迁移_Innodb 表空间传输迁移数据

在mysql5.5之前，mysql实例中innodb引擎表的迁移是个头疼的问题，要么使用mysqldump导出，要么使用物理备份的方法，但是在mysql5.6之后的版本中，可以使用一个新特性，方便地迁移Innodb表，这样一来大的innodb表的迁移就显得很easy，这个特性就是innodb表空间传输：

1.在迁出实例数据库：

mysql> use xiaoboluo;

mysql>select * from test1;  #查询一下迁出实例表数据，这里是测试表，大表请不要这么干

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

| product_id | param | create_time |

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

| 1          |     1 |           1 |

| 10         |    10 |          10 |

| 11         |     1 |           1 |

| 12         |   100 |         101 |

| 13         |   101 |         101 |

| 14         |   100 |         101 |

| 2          |     2 |           2 |

| 3          |     3 |           3 |

| 4          |     4 |           4 |

| 5          |     5 |           5 |

| 6          |     6 |           6 |

| 7          |     7 |           7 |

| 8          |     8 |           8 |

| 9          |     9 |           9 |

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

14 rows in set (0.00 sec)

mysql>show create table test1; #查看表定义语句，复制出来

2.在迁入实例数据库：

mysql>create database xiaoboluo;

建表：

mysql>use xiaoboluo

mysql>CREATE TABLE `test1` (

`product_id` varchar(50) NOT NULL,

`param` int(11) DEFAULT NULL,

`create_time` int(11) DEFAULT NULL,

PRIMARY KEY (`product_id`),

KEY `i_create_time` (`create_time`),

KEY `i_product_create_time` (`product_id`,`create_time`),

KEY `i_param` (`param`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表相关文件：

shell >ll /data/mysql3306/xiaoboluo/test1*

-rw-rw---- 1 mysql mysql   8648 1月   7 09:31 /data/mysql3306/xiaoboluo/test1.frm

-rw-rw---- 1 mysql mysql 147456 1月   7 09:33 /data/mysql3306/xiaoboluo/test1.ibd

mysql>alter table test1 discard tablespace; #注意再三确认这个命令有没有搞错对象，一执行就会把ibd文件给删掉

再次查看表相关文件，可以看到没有了表空间文件ibd：

shell >ll /data/mysql3306/xiaoboluo/test1*

-rw-rw---- 1 mysql mysql 8648 1月   7 09:37 /data/mysql3306/xiaoboluo/test1.frm

3.在迁出实例数据库：

mysql>use xiaoboluo;

mysql>flush tables  test1 for export; #这个时候会加表锁，只能读不能写，同时也会产生一个tb_name.cfg文件

然后复制这个表的ibd和cfg文件到迁入实例的新建同名库目录下：

shell >scp /data/mysql3306/xiaoboluo/test1.{cfg,ibd} 192.168.0.37:/data/mysql3306/xiaoboluo

root@192.168.0.37's password:

test1.cfg                                                                                                  100%  801     0.8KB/s   00:00

test1.ibd                                                                                                  100%  144KB 144.0KB/s   00:00

mysql>unlock tables; #解锁迁出实例表

4.在迁入目标实例上：mysql>use xiaoboluo;

shell >chown mysql.mysql /data/mysql3306/xiaoboluo/test1.{ibd,cfg}

查看文件权限：

shell > ll /data/mysql3306/xiaoboluo/test1*

-rw-r----- 1 mysql mysql    801 1月   7 09:40 /data/mysql3306/xiaoboluo/test1.cfg

-rw-rw---- 1 mysql mysql   8648 1月   7 09:37 /data/mysql3306/xiaoboluo/test1.frm

-rw-r----- 1 mysql mysql 147456 1月   7 09:41 /data/mysql3306/xiaoboluo/test1.ibd

mysql>Alter table test1 import tablespace;#导入表空间文件

mysql>select * from test1; #校验数据是否正常,这里是测试表，大表请用其他方法验证数据+------------+-------+-------------+

| product_id | param | create_time |

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

| 1          |     1 |           1 |

| 10         |    10 |          10 |

| 11         |     1 |           1 |

| 12         |   100 |         101 |

| 13         |   101 |         101 |

| 14         |   100 |         101 |

| 2          |     2 |           2 |

| 3          |     3 |           3 |

| 4          |     4 |           4 |

| 5          |     5 |           5 |

| 6          |     6 |           6 |

| 7          |     7 |           7 |

| 8          |     8 |           8 |

| 9          |     9 |           9 |

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

14 rows in set (0.00 sec)

到这里整个innodb表就被迁移完成，是不是比以前的方法方便很多呢！！