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表就被迁移完成,是不是比以前的方法方便很多呢!!

posted @ 2016-01-07 09:50 xiaoboluo768 阅读(...) 评论(...) 编辑 收藏