美文网首页
Importing InnoDB Tables

Importing InnoDB Tables

作者: 左轮Lee | 来源:发表于2020-03-27 19:57 被阅读0次

环境：MySQL 5.7.25

介绍下使用Transportable Tablespaces功能进行InnoDB表导入，可导入表，分区表或分区表的单个分区。
一、使用此功能的场景
二、使用此功能的前置条件
三、Importing Tables
四、Importing Partitioned Tables
五、Importing Table Partitions
六、局限性

一、使用此功能的场景：

1.在非生产MySQL服务器实例上运行报告，以避免在生产服务器上增加额外的负载；
2.将数据复制到新的从库；
3.从备份的表空间文件还原表；
4.与导入dump文件相比，移动数据是一种更快的方法，但需要重新插入数据和重建索引；
5.将数据移动到更适合您的存储要求的存储介质的服务器上。 例如，您可能将忙碌的表移至SSD设备，或将大表移至高容量HDD设备。

二、使用此功能的前置条件：

1.必须打开innodb_file_per_table=1参数；
2.表空间的页面大小必须与目标MySQL服务器实例的页面大小匹配。 InnoDB页面大小由innodb_page_size变量定义，该变量在初始化MySQL服务器实例时配置；
3.如果表有外键，则在执行DISCARD TABLESPACE之前必须禁用foreign_key_checks。 另外，您应该在同一逻辑时间点导出所有与外键相关的表，因为ALTER TABLE ... IMPORT TABLESPACE不会对导入的数据实施外键约束。 为此，请停止更新相关表，提交所有事务，获取表上的共享锁，然后执行导出操作；
4.从另一个MySQL服务器实例导入表时，两个MySQL服务器实例都必须具有通用状态（GA），并且必须具有相同的版本。 否则，必须在将表导入到的同一MySQL服务器实例上创建表；
5.如果通过在CREATE TABLE语句中指定DATA DIRECTORY子句在外部目录中创建了表，则必须使用相同的DATA DIRECTORY子句定义在目标实例上替换的表。 如果子句不匹配，则报schema mismatch error。 若要确定源表是否已使用DATA DIRECTORY子句定义，请使用SHOW CREATE TABLE查看表定义。
6.如果未在表定义中明确定义ROW_FORMAT选项，或者使用ROW_FORMAT = DEFAULT，则源实例和目标实例上的innodb_default_row_format设置必须相同。 否则，当您尝试导入操作时，则报schema mismatch error。 使用SHOW CREATE TABLE检查表定义。 使用SHOW VARIABLES检查innodb_default_row_format设置。

三、Importing Tables

1.源端建测试表并写入数据

use test; 
create table test_tts(id int, name VARCHAR(10)) engine=InnoDB;
insert into test_tts select 1,'a';
insert into test_tts select 2,'aa';
insert into test_tts select 3,'aaa';

2.目标端建表

use test;
create table test_tts(id int, name VARCHAR(10)) engine=InnoDB;

3.目标端discard已存在的表空间

ALTER TABLE test_tts DISCARD TABLESPACE;

4.源端执行FLUSH TABLES ... FOR EXPORT

use test;
FLUSH TABLES test_tts FOR EXPORT;

[SQL]insert into test_tts values(4,'aaaa');
[Err] 1099 - Table 'test_tts' was locked with a READ lock and can't be updated
##  执行后会在数据文件目录生产 .cfg 的元数据文件,此时表只允许读，不允许DML操作。

5.scp .cfg 和 .ibd 文件到目标端

scp /path/to/datadir/test/test_tts.{ibd,cfg} destination-server:/path/to/datadir/test
## copy完成后注意文件权限
chown mysql:mysql /path/to/datadir/test

6.拷贝完成后源端释放表锁

use test; 
unlock tables;

7.目标端，导入表空间

use test; 
ALTER TABLE test_tts IMPORT TABLESPACE;
## 验证访问 
select * from test_tts ;
四、Importing Partitioned Tables

1.源端建测试表并写入数据

use test;
create table test_partition (id int  ,ctime datetime ) 
 partition by range COLUMNS (ctime) 
    (PARTITION  P1  VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
     PARTITION  P2  VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
     PARTITION  P3  VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
     PARTITION  P4  VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
     PARTITION  P5  VALUES LESS THAN (maxvalue) ENGINE = InnoDB ) ;
     
### 查看生成分区表文件
[root@localhost test]# ls -l
-rw-r----- 1 mysql mysql    65 Mar 25 16:38 db.opt
-rw-r----- 1 mysql mysql  8588 Mar 27 18:57 test_partition.frm
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P1.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P2.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P3.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P4.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P5.ibd

2.目标端建表

use test;
create table test_partition (id int,ctime datetime) 
 partition by range COLUMNS (ctime) 
    (PARTITION  P1  VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
     PARTITION  P2  VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
     PARTITION  P3  VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
     PARTITION  P4  VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
     PARTITION  P5  VALUES LESS THAN (maxvalue) ENGINE = InnoDB ) ;

3.目标端discard已存在的表空间

ALTER TABLE test_partition DISCARD TABLESPACE;

## 查看分区表对应的文件，发现只有表结构文件存在，分区数据文件已被丢弃
[root@localhost test1]# ls -l *partition*
-rw-r----- 1 mysql mysql 8588 Mar 27 19:02 test_partition.frm

4.源端执行 FLUSH TABLES ... FOR EXPORT

use test;
FLUSH TABLES test_partition FOR EXPORT;

[root@localhost kkk]# ls -l
-rw-r----- 1 mysql mysql    65 Mar 25 16:38 db.opt
-rw-r----- 1 mysql mysql  8588 Mar 27 18:57 test_partition.frm
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P1.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P1.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P2.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P2.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P3.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P3.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P4.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P4.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P5.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P5.ibd

5.scp .cfg 和 .ibd 文件到目标端

scp /path/to/datadir/test/test_tts.{ibd,cfg} destination-server:/path/to/datadir/test
copy完成后注意文件权限
chown mysql:mysql /path/to/datadir/test

6.拷贝完成后源端释放表锁

use test;
unlock tables;

7.目标端，导入表空间

use test;
ALTER TABLE test_partition  IMPORT TABLESPACE;

## 验证访问
select * from test_partition;
五、Importing Table Partitions

1.源端建测试表并写入数据

use test;
create table test_partition (id int  ,ctime datetime ) 
 partition by range COLUMNS (ctime) 
    (PARTITION  P1  VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
     PARTITION  P2  VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
     PARTITION  P3  VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
     PARTITION  P4  VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
     PARTITION  P5  VALUES LESS THAN (maxvalue) ENGINE = InnoDB ) ;
     
### 查看生成分区表文件
[root@localhost test]# ls -l
-rw-r----- 1 mysql mysql    65 Mar 25 16:38 db.opt
-rw-r----- 1 mysql mysql  8588 Mar 27 18:57 test_partition.frm
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P1.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P2.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P3.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P4.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P5.ibd

2.目标端建表

use test;
create table test_partition (id int,ctime datetime) 
 partition by range COLUMNS (ctime) 
    (PARTITION  P1  VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
     PARTITION  P2  VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
     PARTITION  P3  VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
     PARTITION  P4  VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
     PARTITION  P5  VALUES LESS THAN (maxvalue) ENGINE = InnoDB ) ;

3.目标端discard需要导入的表分区

ALTER TABLE test_partition DISCARD PARTITION p2, p3 TABLESPACE;

## 查看分区表对应的文件，发现需要导入的分区数据文件已被丢弃
[root@localhost test]# ls -l *partition*
-rw-r----- 1 mysql mysql  8588 Mar 27 19:09 test_partition.frm
-rw-r----- 1 mysql mysql 98304 Mar 27 19:09 test_partition#P#P1.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 19:09 test_partition#P#P4.ibd
-rw-r----- 1 mysql mysql 98304 Mar 27 19:09 test_partition#P#P5.ibd

4.源端执行 FLUSH TABLES ... FOR EXPORT

use test;
FLUSH TABLES test_partition FOR EXPORT;

[root@localhost kkk]# ls -l
-rw-r----- 1 mysql mysql    65 Mar 25 16:38 db.opt
-rw-r----- 1 mysql mysql  8588 Mar 27 18:57 test_partition.frm
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P1.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P1.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P2.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P2.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P3.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P3.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P4.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P4.ibd
-rw-r----- 1 mysql mysql   444 Mar 27 19:03 test_partition#P#P5.cfg
-rw-r----- 1 mysql mysql 98304 Mar 27 18:57 test_partition#P#P5.ibd

5.scp 指定分区的.cfg 和 .ibd 文件到目标端

scp test_partition#P#P2.cfg test_partition#P#P2.ibd test_partition#P#P3.cfg test_partition#P#P3.ibd ../test/
## copy完成后注意文件权限
chown mysql:mysql /path/to/datadir/test

6.拷贝完成后源端释放表锁

use test;
unlock tables;

7.目标端，导入表空间

use test;
ALTER TABLE test_partition  IMPORT PARTITION p2, p3 TABLESPACE;

## 验证访问
select * from test_partition partition (P2);
select * from test_partition partition (P3);
五、局限性

1.只有开启独立表空间(innodb_file_per_table=1)才支持可传输表空间功能，不支持系统表空间或常规表空间中的表， 共享表空间中的表不能被quiesced；
2.带有FULLTEXT索引的表不支持FLUSH TABLES ... FOR EXPORT，因为无法刷新全文搜索辅助表。 导入具有FULLTEXT索引的表后，运行OPTIMIZE TABLE重建FULLTEXT索引。 或者，在导出操作之前删除FULLTEXT索引，并在目标实例上导入表后重新创建索引；
3.由于.cfg元数据文件的限制，在导入分区表时，不会报告分区类型或分区定义差异的schema mismatches errors，而是报Column differences errors。

参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html#innodb-table-import-limitations

相关文章

网友评论

      本文标题：Importing InnoDB Tables

      本文链接：https://www.haomeiwen.com/subject/wlyruhtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|Importing InnoDB Tables|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！