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服务器实例复制到另一个正在运行的实例。可以使用相同的过程在相同的实例上执行全表恢复,只是做了一些小小的调整。
1. 在源实例上,如果不存在表,则创建一个表:

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从一个实例迁移到另一个实例上。

发表评论

电子邮件地址不会被公开。