InnoDB之传输表空间

一、介绍

传输表空间允许将一个单独的表或分区很快速的复制到另外一台mysql实例中。

1、使用场景
• 在非生产MySQL服务器实例上运行报表，以避免在生产服务器上增加额外的负载。

• 将数据复制到新的从服务器。

• 从备份的表空间文件恢复表。

• 作为一种比导入转储文件更快的移动数据的方式，这需要重新插入数据和重建索引。

• 将数据移动到存储介质更适合您的存储需求的服务器。例如，您可以将繁忙的表移动到SSD设备，或者将大表移动到大容量的HDD设备。

2、先决条件

• innodb_file_per_table 必须开启
• innodb_page_size 源端和目标端必须一致
• 如果表是外键关系，在执行DISCARD TABLESPACE之前必须禁用foreign_key_checks。此外，您应该在相同的逻辑时间点导出所有与外键相关的表，如ALTER TABLE…IMPORT TABLESPACE不会对导入的数据强制外键约束。为此，停止更新相关的表，提交所有事务，获取表上的共享锁，并执行导出操作。
• 源端和目标端数据库版本要一致
• 如果表是外部表，目标端必须创建和源端相同的外部表路径
• 源端和目标端的ROW_FORMAT必须相同

二、实验步骤

（传输表空间可以实现不同实例间的表复制，本实验环境为同一实例不同库的传输）

1、传输表
1）根据源端表结构，在目标端创建表
源端：
mysql> show create table test\G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `emp_no` int NOT NULL,
  `salary` int NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
目标端：
mysql> CREATE TABLE `test` (
    ->   `emp_no` int NOT NULL,
    ->   `salary` int NOT NULL,
    ->   `from_date` date NOT NULL,
    ->   `to_date` date NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

2）目标端将新建的表脱机
mysql> alter table test discard tablespace;
Query OK, 0 rows affected (0.02 sec)
3）源端将将要传输的表改为导出模式，设成此模式后，关于此表的buffer中的脏块会刷新到此表，此表为只读
mysql> flush table test for export;
Query OK, 0 rows affected (0.00 sec)
4）将关于此表的数据文件复制到对应 的目标端的路径下
cp test.{ibd,cfg} ../test/

注意：复制要用mysql用户，注意复制完成后的数据文件的权限，对mysql实例没权限的数据文件import时话会报如下错误：
ERROR 1812 (HY000): Tablespace is missing for table `test`.`test`.
本实验使用的是同一主机同一实例的不同数据库，不同主机使用scp命令
cfg是test表的元数据文件，需要一并放到目标端，如果该表来自加密表空间，则还会生产cfp文件，这样的话这三个文件都要复制。

5）源端对该表解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

6）目标端对该表导入
mysql> alter table test import tablespace;
Query OK, 0 rows affected (0.05 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|      500 |
+----------+
1 row in set (0.03 sec)

2、传输分区表

和传输表步骤一致，只不过分区表的表数据文件是单个分开的，而且每个分区表都会生产一个cfg和cfp文件（如果表空间是加密的话），需要把这些文件都复制到目标端。

3、传输分区
test_part表有三个分区，我们想传输第2、3个分区
mysql> select partition_name,table_rows 
    -> from INFORMATION_SCHEMA.partitions  
    -> where TABLE_SCHEMA="employees" 
    -> AND TABLE_NAME="test_part";
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p1             |          3 |
| p2             |          3 |
+----------------+------------+
3 rows in set (0.00 sec)
mysql> select * from test_part;
+------+
| i    |
+------+
|    4 |
|    5 |
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
+------+
mysql> 

1）根据源端表结构，在目标端创建表
源端：
mysql>  show create table test_part\G;
*************************** 1. row ***************************
       Table: test_part
Create Table: CREATE TABLE `test_part` (
  `i` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY KEY (i)
PARTITIONS 3 */
1 row in set (0.00 sec)

目标端：
mysql> CREATE TABLE `test_part` (
    ->   `i` int DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    -> /*!50100 PARTITION BY KEY (i)
    -> PARTITIONS 3 */;
Query OK, 0 rows affected (0.05 sec)

mysql> \! ls /data2/3301/data/test
t2.ibd  test.cfg  test.ibd  test_part#p#p0.ibd  test_part#p#p1.ibd  test_part#p#p2.ibd

2）目标端需要传输的分区脱机
mysql> ALTER TABLE test_part DISCARD PARTITION p1, p2 TABLESPACE;
Query OK, 0 rows affected (0.02 sec)

mysql> \! ls /data2/3301/data/test
t2.ibd  test.cfg  test.ibd  test_part#p#p0.ibd
注意：DISCARD会删除对应的ibd文件，生产环境执行前要核对表名是否正确

3）源端将将要传输的表改为导出模式
mysql> flush table test for export;
Query OK, 0 rows affected (0.00 sec)
4）将关于此表的数据文件复制到对应 的目标端的路径下
[root@test2 employees]# cp test_part#p#p1.{ibd,cfg} ../test
[root@test2 employees]# cp test_part#p#p2.{ibd,cfg} ../test

5）源端对该表解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

6）目标端对该表导入
mysql> ALTER TABLE test_part IMPORT PARTITION p1, p2 TABLESPACE;
Query OK, 0 rows affected (0.05 sec)

mysql> select partition_name,table_rows 
    -> from INFORMATION_SCHEMA.partitions  
    -> where TABLE_SCHEMA="test" 
    -> AND TABLE_NAME="test_part";
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
| p2             |          3 |
+----------------+------------+
3 rows in set (0.00 sec)

mysql> select * from test_part;
+------+
| i    |
+------+
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
+------+
6 rows in set (0.00 sec)

三、限制

1、只支持file-per-table 表空间的表
2、不支持包含全文索引的表，因为full-text查询辅助表不能被flushed。可以先删除掉全文索引，在传输完成后重建 
3、由于.cfg元数据文件的限制，在导入分区表时，不会报告分区类型或分区定义差异的模式不匹配。会报告列差异。
4、在MySQL 8.0.19之前，索引关键部分排序顺序信息不存储在表空间导入操作时使用的.cfg元数据文件中。因此，假定索引键部分排序顺序为升序，这是默认值。因此，如果导入操作中涉及的一个表是用DESC索引键部分排序顺序定义的，而另一个表则不是，那么记录可能会以意想不到的顺序排序。解决方法是删除并重新创建受影响的索引。

四、拓展

1、只有在不存在模式不匹配的情况下，才应该考虑导入没有.cfg元数据文件的表。在元数据不可访问的崩溃恢复场景中，不使用.cfg文件进行导入的能力非常有用。
2、在Windows环境，未避免大小写敏感造成的错误，应lower_case_table_names=1

五、内部

1、ALTER TABLE ... DISCARD TABLESPACE 
•表被锁定为X模式。
•表空间与表分离。

2、FLUSH TABLES ... FOR EXPORT
•正在刷新用于导出的表被锁定在共享模式。
•清除协调器线程停止。
•脏页同步到磁盘。
•表元数据写入二进制的.cfg文件。

error日志会有如下输出：
[Note] InnoDB: Sync to disk of '"test"."t1"' started. 
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/t1.cfg' 
[Note] InnoDB: Table '"test"."t1"' flushed to disk

看到上面信息的前提是log_error_verbosity设置为3，
从MySQL 5.7.2开始，首选log_error_verbosity系统变量，而不是使用–log-warnings选项或log_warnings系统变量，这个参数从MySQL 8.0.3开始被移除了。而新参数log_eror_verbosity更简单，它有三个可选值， 分别对应：1 错误信息；2 错误信息和告警信息； 3：错误信息、告警信息和通知信息。8.0.4及之后默认值是2，之前是3。

3、UNLOCK TABLES
•删除二进制文件“。cfg”。
•被导入的表上的共享锁被释放，清除协调器线程重新启动。

error日志会有如下输出：
[Note] InnoDB: Deleting the meta-data file './test/t1.cfg' 
[Note] InnoDB: Resuming purge

4、ALTER TABLE ... IMPORT TABLESPACE
•检查每个表空间页面是否损坏。
•更新每一页的空间ID和日志序列号。
•验证标记和更新头部页面的LSN。
•b树页面更新。
•页面状态设置为dirty，以便写入磁盘。
 
 error日志会有如下输出：
[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
 from host 'host_name'
[Note] InnoDB: Phase I - Update all pages [Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk [Note] InnoDB: Phase IV - Flush complete