MySQL 拷贝一个InnoDB分区表到另一个实例

拷贝一个InnoDB分区表到另一个实例
这个过程演示了如何将一个InnoDB分区表从一个正在运行的MySQL服务器实例复制到另一个正在运行的实例。同样的过程,只要稍微做些调整,就可以在同一个实例上对InnoDB分区表执行完全恢复。

1.在源实例上,如果不存在分区表,则创建分区表。在下面的例子中,创建了一个包含三个分区(p0, p1, p2)的表

mysql> use test;
Database changed
mysql> create table t1(i int) engine=innodb partition by key(i) partitions 3;
Query OK, 0 rows affected (0.38 sec)

mysql> insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+
| i    |
+------+
|    4 |
|    5 |
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

在/mysqldata/mysql/test目录中,对于三个分区都有一个单独的表空间(.ibd)文件:

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 304
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:43 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p0.ibd

2.在目标实例上,创建相同的分区表:

mysql> use test;
Database changed
mysql> create table t1(i int) engine=innodb partition by key(i) partitions 3;
Query OK, 0 rows affected (0.20 sec)

在/mysqldata/mysql/test目录中,对于三个分区都有一个单独的表空间(.ibd)文件:

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 304
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:45 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p2.ibd

3.在目标实例上,丢弃分区表的表空间。(在将表空间导入目标实例之前,必须丢弃附加到接收表的表空间。)

mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)

组成分区表表空间的三个.ibd文件从/mysqldata/mysql/tes目录中被丢弃,留下以下文件

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 16
-rw-r-----. 1 mysql mysql   67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql 8554 3月  16 15:45 t1.frm

4.在源实例上,运行FLUSH TABLES… FOR EXPORT用于暂停分区表并创建.cfg元数据文件

mysql> flush tables t1 for export;
Query OK, 0 rows affected (0.01 sec)

在源实例的/mysqldata/mysql/test目录中创建元数据(.cfg)文件,每个表空间(.ibd)文件对应一个元数据文件

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 316
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:43 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p1.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p0.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p2.cfg

FLUSH TABLES……FOR EXPORT语句确保对指定表的更改已刷新到磁盘,以便在实例运行时可以进行二进制表拷贝。当运行FLUSH TABLES … FOR EXPORT时,InnoDB会在数据库目录中为表的表空间文件生成一个.cfg元数据文件。.cfg文件中包含导入表空间文件时验证模式的元数据。FLUSH TABLES … FOR EXPORT只能在表上运行,而不能在单独的表分区上运行。

5.将.ibd和.cfg文件从源实例数据库目录复制到目标实例数据库目录。例如

[root@localhost test]# scp t1*.{ibd,cfg} mysql@192.168.1.243:/mysqldata/mysql/test/
mysql@192.168.1.243's password:
t1#P#p0.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p1.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p2.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p0.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
t1#P#p1.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
t1#P#p2.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
[root@localhost test]#

[root@localhost test]# ls -lrt
总用量 316
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:45 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p0.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p1.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p2.cfg

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

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

在源实例上释放锁时,会向mysql日志文件写入删除.cfg文件的信息:

2022-03-16T08:08:27.653352Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p0.cfg'
2022-03-16T08:08:27.653656Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p1.cfg'
2022-03-16T08:08:27.654214Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p2.cfg'
2022-03-16T08:08:27.654256Z 10 [Note] InnoDB: Resuming purge

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

mysql> select * from t1;
ERROR 1814 (HY000): Tablespace has been discarded for table 't1'
mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t1 import tablespace;
Query OK, 0 rows affected (0.46 sec)

mysql> select * from t1;
+------+
| i    |
+------+
|    4 |
|    5 |
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
|    9 |
+------+
9 rows in set (0.01 sec)

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