MySQL 传输表空间

将file-per-table表空间复制到另一个实例

如何将一个file-per-table表空间从一个Mysql实例复制到另一个实例中，也就是众所周知的可传输表空间特性。

有很多原因可以解释为什么你可以将一个InnoDB文件表空间复制到不同的实例中:

.在不增加生产服务器额外负载的情况下运行报表。

.在新的从服务器上为表设置相同的数据

.在出现问题或错误后恢复表或分区的备份版本。

.作为一种比mysqldump命令导入更快的移动数据的方法。数据立即可用，而不需要重新插入和重建索引

.将每个file-per-table表空间移动到具有更适合系统需求的存储介质的服务器。例如，您可能希望在SSD设备上有繁忙的表，或者在高容量HDD设备上有大型表。

限制和使用说明

.只有当innodb_file_per_table设置为ON(默认设置)时，才可以拷贝表空间。驻留在共享系统表空间中的表不能被静默。

.当一个表被静默时，只允许在受影响的表上执行只读事务

.在导入表空间时，页面大小必须与导入实例的页面大小相匹配。

.当foreign_key_checks设置为1时，对于父-子(主-外键)关系的表空间不支持DISCARD TABLESPACE。在丢弃父-子表的表空间之前，设置foreign_key_checks=0。分区InnoDB表不支持外键。

.ALTER TABLE……IMPORT TABLESPACE不会对导入的数据强制外键约束。如果表之间存在外键约束，那么所有表都应该在同一(逻辑)时间点导出。分区InnoDB表不支持外键。

.ALTER TABLE……IMPORT TABLESPACE 和 ALTER TABLE…IMPORT PARTITION…TABLESPACE不需要.cfg元数据文件来导入一个表空间。但是，如果导入时没有.cfg文件，则不会执行元数据检查，并且会发出类似于下面的警告：

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening ‘.

test\t.cfg’, will attempt to import without schema verification

1 row in set (0.00 sec)

在期待没用模式不匹配的情况下，不使用.cfg文件进行导入可能会更方便。此外，在无法从.ibd文件收集元数据的崩溃恢复场景中，不需要.cfg文件就可以导入。

.由于.cfg元数据文件的限制，当为分区表导入表空间文件时，不会对分区类型或分区定义差异报告模式不匹配。列差异被报告。

.当在子分区表上运行ALTER TABLE … DISCARD PARTITION … TABLESPACE和ALTER TABLE … IMPORT PARTITION … TABLESPACE，分区和子分区表名都是允许的。当指定分区名时，该分区的子分区将包含在操作中。

.如果两个实例都有GA(通用可用性)状态，并且它们的版本在同一系列可以从另一个MySQL服务器实例导入表空间文件。否则，该文件必须是在导入它的同一个服务器实例上所创建

.在复制场景中，innodb_file_per_table必须在主节点和从节点上都设置为ON。

.在Windows环境下，InnoDB内部存储数据库、表空间和表名时使用小写字母。为了避免在区分大小写的操作系统(如Linux、UNIX)上的导入问题，请在创建数据库、表空间和表时使用小写名称。一种方便的方法是在创建数据库、表空间或表之前，在my.cnf或my.ini文件的[mysqld]部分中添加下面这一行:

[mysqld]

lower_case_table_names=1

.alter table … discard tablespace和alter table … import tabelspace不支持属于InnoDB通用表空间中的表。

.InnoDB表的默认行格式可以通过innodb_default_row_format配置选项进行配置。如果导入的表没有明确定义行格式(ROW_FORMAT)，或者使用了ROW_FORMAT=DEFAULT，那么如果源实例上的innodb_default_row_format设置与目标实例上的innodb_default_row_format设置不一致，可能会导致模式不匹配错误

.在使用InnoDB表空间加密特性导出加密的表空间时，InnoDB除了生成一个.cfg元数据文件外，还会生成一个.cfp文件。在目标实例上执行ALTER TABLE…IMPORT TABLESPACE之前，必须将.cfp文件与.cfg文件和表空间文件一起复制到目标实例中。cfp文件包含一个传输密钥和一个加密的表空间密钥。在导入时，InnoDB使用传输密钥来解密表空间密钥。

传输表空间示例

例如1:复制一个InnoDB表到另一个实例

这个过程演示了如何将一个普通的InnoDB表从一个正在运行的MySQL服务器实例复制到另一个正在运行的实例。可以使用相同的过程在相同的实例上执行全表恢复，只是做了一些小小的调整。

在源实例上，如果不存在表，则创建一个表:

mysql> use test;

Database changed

mysql> create table t(c1 int) engine=innodb;

Query OK, 0 rows affected (0.12 sec)

mysql> insert into t values(1);

Query OK, 1 row affected (0.16 sec)

2.在目标实例上，如果不存在表，则创建表:

mysql> use test;

Database changed

mysql> create table t(c1 int) engine=innodb;

Query OK, 0 rows affected (0.09 sec)

3.在目标实例上，丢弃现有的表空间。(在导入表空间之前，InnoDB必须丢弃连接到接收表空间的表空间。)

[mysql@localhost test]$ ls -lrt

总用量 112

-rw-r-----. 1 mysql mysql 67 3月 15 16:55 db.opt

-rw-r-----. 1 mysql mysql 8556 3月 15 16:57 t.frm

-rw-r-----. 1 mysql mysql 98304 3月 15 16:57 t.ibd

mysql> alter table t discard tablespace;

Query OK, 0 rows affected (0.17 sec)

[mysql@localhost test]$ ls -lrt

总用量 16

-rw-r-----. 1 mysql mysql 67 3月 15 16:55 db.opt

-rw-r-----. 1 mysql mysql 8556 3月 15 16:57 t.frm

4.在源实例上，运行FLUSH TABLES…FOR EXPORT将暂停表并创建.cfg元数据文件

mysql> flush tables t for export;

Query OK, 0 rows affected (0.00 sec)

[mysql@localhost test]$ ls -lrt

总用量 116

-rw-r-----. 1 mysql mysql 67 3月 15 16:53 db.opt

-rw-r-----. 1 mysql mysql 8556 3月 15 16:54 t.frm

-rw-r-----. 1 mysql mysql 98304 3月 15 16:54 t.ibd

-rw-r-----. 1 mysql mysql 371 3月 15 17:00 t.cfg

在InnoDB数据目录下创建元数据(.cfg)

注意:FLUSH TABLES …… FOR EXPORT在MySQL 5.6.6版本中可用。该语句确保对指定表的更改已刷新到磁盘，以便在实例运行时可以生成二进制表副本。当FLUSH TABLES … FOR EXPORT时，InnoDB会在表所在的数据库目录中生成一个.cfg文件。cfg文件中包含导入表空间文件时用于模式验证的元数据。

5.将.ibd文件和.cfg元数据文件从源实例复制到目标实例

[mysql@localhost test]$ scp t.{ibd,cfg} mysql@192.168.1.243:/mysqldata/mysql/test/

mysql@192.168.1.243’s password:

t.ibd 100% 96KB 96.0KB/s 00:00

t.cfg 100% 371 0.4KB/s 00:00

[mysql@localhost test]$

在释放共享锁之前必须复制.ibd与.cfg文件。

6.在源实例上，使用unlock tables语句来释放由flush tables … for export所获取的锁:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

7.在目标实例上，导入表空间:

mysql> alter table t import tablespace;

Query OK, 0 rows affected (0.15 sec)

mysql> desc t;

±------±--------±-----±----±--------±------+

| Field | Type | Null | Key | Default | Extra |

±------±--------±-----±----±--------±------+

| c1 | int(11) | YES | | NULL | |

±------±--------±-----±----±--------±------+

1 row in set (0.00 sec)

mysql> select * from t;

±-----+

| c1 |

±-----+

| 1 |

±-----+

1 row in set (0.00 sec)

可以看到表t从一个实例迁移到另一个实例上。

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。