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)