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

拷贝InnoDB表分区到另一个实例
这个过程演示了如何将InnoDB表分区从一个正在运行的MySQL服务器实例复制到另一个正在运行的MySQL服务器实例。同样的过程，只要稍微调整一下，就可以在同一个实例上执行InnoDB表分区的恢复。在下面的示例中，在源实例上创建了一个包含四个分区(p0, p1, p2, p3)的分区表。将两个分区(p2和p3)复制到目标实例。

1.在源实例上，如果不存在分区表，则创建分区表。在下面的示例中，创建了一个包含四个分区(p0, p1, p2, p3)的表
mysql> use test;
Database changed
mysql> create table t2 (i int) engine=innodb partition by key(i) partitions 4;
Query OK, 0 rows affected (0.41 sec)
mysql> insert into t2 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);
Query OK, 16 rows affected (0.12 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from t2 partition(p0);
+------+
| i    |
+------+
|    1 |
|    5 |
|    9 |
|   13 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t2 partition(p1);
+------+
| i    |
+------+
|    4 |
|    8 |
|   12 |
|   16 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t2 partition(p2);
+------+
| i    |
+------+
|    3 |
|    7 |
|   11 |
|   15 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t2 partition(p3);
+------+
| i    |
+------+
|    2 |
|    6 |
|   10 |
|   14 |
+------+
4 rows in set (0.00 sec)

在/mysqldata/mysql/test目录中，四个分区都有一个单独的表空间(.ibd)文件。
[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 400
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 16:26 t2.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p3.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p1.ibd

2.在目标实例上，创建相同的分区表:
mysql> use test;
Database changed
mysql> create table t2 (i int) engine=innodb partition by key(i) partitions 4;
Query OK, 0 rows affected (0.35 sec)

在/mysqldata/mysql/test目录中，四个分区都有一个单独的表空间(.ibd)文件。
[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 400
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 16:31 t2.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p3.ibd

3.在目标实例上，丢弃计划从源实例导入的表空间分区。(在将表空间分区导入目标实例之前，必须丢弃附加到接收表的相应分区。)
mysql> select * from t2 partition(p0);
Empty set (0.00 sec)

mysql> select * from t2 partition(p1);
Empty set (0.00 sec)

mysql> select * from t2 partition(p2);
Empty set (0.00 sec)

mysql> select * from t2 partition(p3);
Empty set (0.00 sec)

mysql> alter table t2 discard partition p2,p3 tablespace;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from t2 partition(p2);
ERROR 1814 (HY000): Tablespace has been discarded for table 't2'
mysql> select * from t2 partition(p3);
ERROR 1814 (HY000): Tablespace has been discarded for table 't2'

两个丢弃分区的.ibd文件将从目标实例的/mysqldata/mysql/test目录中删除，留下以下文件
[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 208
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 16:31 t2.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:31 t2#P#p1.ibd

4.在源实例上，运行FLUSH TABLES… FOR EXPORT用于暂停分区表并创建.cfg元数据文件
mysql> flush tables t2 for export;
Query OK, 0 rows affected (0.00 sec)

在源实例的/datadir/test目录中创建元数据文件(.cfg文件)。每个表空间(.ibd)文件都有一个.cfg文件
[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
总用量 416
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 16:26 t2.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p3.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:28 t2#P#p1.ibd
-rw-r-----. 1 mysql mysql   375 3月  16 16:44 t2#P#p0.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:44 t2#P#p3.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:44 t2#P#p2.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:44 t2#P#p1.cfg

5.将.ibd和.cfg文件从源实例数据库目录复制到目标实例数据库目录。在本例中，只有分区2 (p2)和分区3 (p3)的.ibd和.cfg文件被复制到目标实例的数据目录中。分区0 (p0)和分区1 (p1)仍然在源实例上。
[root@localhost test]# scp t2#P#p2.{ibd,cfg} t2#P#p3.{ibd,cfg} root@192.168.1.243:/mysqldata/mysql/test
root@192.168.1.243's password:
t2#P#p2.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t2#P#p2.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
t2#P#p3.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t2#P#p3.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
[root@localhost test]#

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

6.在源实例上，使用unlock tables语句来释放由flush tables ... for export所获取的锁:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

在源实例上释放锁时，会向mysql日志文件写入删除.cfg文件的信息:
2022-03-16T08:50:32.043415Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p0.cfg'
2022-03-16T08:50:32.043671Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p1.cfg'
2022-03-16T08:50:32.043838Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p2.cfg'
2022-03-16T08:50:32.044021Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p3.cfg'
2022-03-16T08:50:32.044064Z 12 [Note] InnoDB: Resuming purge

7.在目标实例上，导入表空间:
mysql> select * from t2 partition(p2);
ERROR 1814 (HY000): Tablespace has been discarded for table 't2'
mysql> select * from t2 partition(p3);
ERROR 1814 (HY000): Tablespace has been discarded for table 't2'
mysql> alter table t2 import partition p2,p3 tablespace;
Query OK, 0 rows affected (0.46 sec)

mysql> select * from t2 partition(p2);
+------+
| i    |
+------+
|    3 |
|    7 |
|   11 |
|   15 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t2 partition(p3);
+------+
| i    |
+------+
|    2 |
|    6 |
|   10 |
|   14 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| i    |
+------+
|    3 |
|    7 |
|   11 |
|   15 |
|    2 |
|    6 |
|   10 |
|   14 |
+------+
8 rows in set (0.00 sec)

而源数据库中的表t2记录如下:
mysql> select * from t2;
+------+
| i    |
+------+
|    1 |
|    5 |
|    9 |
|   13 |
|    4 |
|    8 |
|   12 |
|   16 |
|    3 |
|    7 |
|   11 |
|   15 |
|    2 |
|    6 |
|   10 |
|   14 |
+------+
16 rows in set (0.00 sec)

可以看到目录实例中的t2由于执行alter table discard tablespace后只从源数据库实例中迁移了表的两个分区，所以整个表t2也只有这两个分区的数据了。

迁移表空间的内部操作
下面的信息描述了一个普通InnoDB表的可迁移表空间复制过程的内部信息和错误日志消息。
当在目标实例上执行alter table ... discard tablespace时:
.表将以X模式被锁定。
.表空间与表分离

在源数据库实例上执行flush tables ... for export时:
.被执行刷新导出操作的表以共享模式被锁定
.清除协调程序线程被停止
.脏页被同步到磁盘。
.表元数据被写入二进制的.cfg文件

此操作所需的错误日志信息如下:
2022-03-16T08:44:00.775739Z 12 [Note] InnoDB: Sync to disk of `test`.`t2` /* Partition `p0` */ started.
2022-03-16T08:44:00.775851Z 12 [Note] InnoDB: Stopping purge
2022-03-16T08:44:00.776587Z 12 [Note] InnoDB: Writing table metadata to './test/t2#P#p0.cfg'
2022-03-16T08:44:00.777261Z 12 [Note] InnoDB: Table `test`.`t2` /* Partition `p0` */ flushed to disk
2022-03-16T08:44:00.777309Z 12 [Note] InnoDB: Sync to disk of `test`.`t2` /* Partition `p1` */ started.
2022-03-16T08:44:00.777345Z 12 [Note] InnoDB: Writing table metadata to './test/t2#P#p1.cfg'
2022-03-16T08:44:00.777663Z 12 [Note] InnoDB: Table `test`.`t2` /* Partition `p1` */ flushed to disk
2022-03-16T08:44:00.777711Z 12 [Note] InnoDB: Sync to disk of `test`.`t2` /* Partition `p2` */ started.
2022-03-16T08:44:00.777749Z 12 [Note] InnoDB: Writing table metadata to './test/t2#P#p2.cfg'
2022-03-16T08:44:00.777982Z 12 [Note] InnoDB: Table `test`.`t2` /* Partition `p2` */ flushed to disk
2022-03-16T08:44:00.778028Z 12 [Note] InnoDB: Sync to disk of `test`.`t2` /* Partition `p3` */ started.
2022-03-16T08:44:00.778063Z 12 [Note] InnoDB: Writing table metadata to './test/t2#P#p3.cfg'
2022-03-16T08:44:00.778305Z 12 [Note] InnoDB: Table `test`.`t2` /* Partition `p3` */ flushed to disk

在源数据库实例上执行unlock tables时:
.二进制.cfg文件会被删除
.释放将要被导入的表上的共享锁，并重新启动清除协调器线程。

此操作的预期错误日志信息:
2022-03-16T08:50:32.043415Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p0.cfg'
2022-03-16T08:50:32.043671Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p1.cfg'
2022-03-16T08:50:32.043838Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p2.cfg'
2022-03-16T08:50:32.044021Z 12 [Note] InnoDB: Deleting the meta-data file './test/t2#P#p3.cfg'
2022-03-16T08:50:32.044064Z 12 [Note] InnoDB: Resuming purge

当ALTER TABLE…IMPORT TABLESPACE在目标实例上运行时，导入算法将对导入的每个表空间执行以下操作:
.检查每个表空间页是否损坏
.更新每个页面上的空间ID和日志序列号(lsn)
.头页的标记会被验证并更新LSN。
.更新Btree页面
.将页面状态设置为dirty，以便将其写入磁盘

此操作的预期错误日志信息:
2022-03-16T08:51:29.030434Z 5 [Note] InnoDB: Importing tablespace for table 'test/t2#P#p2' that was exported from host 'localhost.localdomain'
2022-03-16T08:51:29.030618Z 5 [Note] InnoDB: Phase I - Update all pages
2022-03-16T08:51:29.031345Z 5 [Note] InnoDB: Sync to disk
2022-03-16T08:51:29.138482Z 5 [Note] InnoDB: Sync to disk - done!
2022-03-16T08:51:29.146126Z 5 [Note] InnoDB: Phase III - Flush changes to disk
2022-03-16T08:51:29.171189Z 5 [Note] InnoDB: Phase IV - Flush complete
2022-03-16T08:51:29.171561Z 5 [Note] InnoDB: `test`.`t2` /* Partition `p2` */ autoinc value set to 0
2022-03-16T08:51:29.337929Z 5 [Note] InnoDB: Importing tablespace for table 'test/t2#P#p3' that was exported from host 'localhost.localdomain'
2022-03-16T08:51:29.338023Z 5 [Note] InnoDB: Phase I - Update all pages
2022-03-16T08:51:29.338410Z 5 [Note] InnoDB: Sync to disk
2022-03-16T08:51:29.344607Z 5 [Note] InnoDB: Sync to disk - done!
2022-03-16T08:51:29.346067Z 5 [Note] InnoDB: Phase III - Flush changes to disk
2022-03-16T08:51:29.362360Z 5 [Note] InnoDB: Phase IV - Flush complete
2022-03-16T08:51:29.362645Z 5 [Note] InnoDB: `test`.`t2` /* Partition `p3` */ autoinc value set to 0