开启冷数据归档功能后,如何手动归档冷数据 - 云原生数据库 PolarDB

本文介绍了将冷数据归档为CSV或ORC格式的方法，以及将OSS表数据导回至PolarStore的方法。

前提条件

若要归档为CSV格式的文件，集群版本需满足以下要求，您可以通过查询版本号来确认集群版本。
- 产品系列为集群版，且版本为以下版本之一：
  - PolarDB MySQL版8.0.1版本且小版本为8.0.1.1.32及以上。
  - PolarDB MySQL版8.0.2版本且小版本为8.0.2.2.10及以上。
    说明
    集群版本为以下版本时，手动归档冷数据不记录Binlog日志。
    PolarDB MySQL版8.0.1版本且小版本为8.0.1.1.33及以上。
    PolarDB MySQL版8.0.2版本且小版本为8.0.2.2.11.1及以上。
- 产品系列为多主集群（库表），且版本为PolarDB MySQL版8.0.1.0.13及以上。
若要归档为ORC格式的文件，集群版本需为PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.16.2及以上。您可以通过查询版本号来确认集群版本。
已开启冷数据归档功能。
已连接数据库集群。

使用说明

归档普通表

冷数据归档执行的是表归档操作，执行冷数据归档操作后的表称为归档表（只读），归档表的引擎为OSS引擎，归档表的数据文件存储在OSS上。执行冷数据归档操作后，原来本地表在PolarStore上占用的空间将被释放。

语法

归档为CSV格式的文件：
```
ALTER TABLE table_name ENGINE = CSV CONNECTION = 'default_oss_server';
```
当集群版本为PolarDB MySQL版8.0.1.1.33版本及以上，或PolarDB MySQL版8.0.2.2.13版本及以上，或多主集群（库表）为PolarDB MySQL版8.0.1.1.15及以上时，除上述语法外，还支持使用以下语法来手动归档冷数据：
```
ALTER TABLE table_name ENGINE = CSV STORAGE OSS;
```

归档为ORC格式的文件：

ALTER TABLE table_name ENGINE = ORC STORAGE OSS;

说明

如果OSS对象存储中存在与归档的数据文件名称冲突的数据文件，系统会报文件已经存在的错误。例如：

Target file for archived table exists on oss.

您可以在以上三个语法中通过添加FORCE关键字来覆盖OSS对象存储中已经存在的数据文件。以归档为CSV格式为例，添加FORCE关键字的语法如下：

ALTER TABLE table_name ENGINE = CSV CONNECTION = 'default_oss_server' , FORCE;

参数说明

参数	说明
table_name	需要归档到OSS对象存储中的表名。

注意事项

支持对InnoDB引擎和X-Engine引擎上的表使用冷数据归档功能。
冷数据归档过程中不支持对表进行修改（DDL和DML）。
冷数据归档功能不支持将数据文件归档到用户自建的OSS Server中。
对InnoDB引擎中的表使用冷数据归档功能时，执行冷数据归档操作的表中必须有主键。
冷数据归档完成后，OSS上的归档表只读，且查询性能较差。您需要提前测试数据归档后是否能满足您的查询性能要求。

示例

将表t中的数据以CSV或ORC格式归档至OSS。

在数据库oss_test中创建InnoDB表t。

CREATE TABLE t(a int, b int, c int, primary key(a)) ENGINE = INNODB;

在表t中插入数据。

INSERT INTO t VALUES (1,2,3);

通过ALTER命令归档冷数据。
- 归档为CSV格式：
```
ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';
```
  集群版本为PolarDB MySQL版8.0.1.1.33版本及以上，或PolarDB MySQL版8.0.2.2.13版本及以上，或多主集群（库表）为PolarDB MySQL版8.0.1.1.15及以上时，使用以下命令：
```
ALTER TABLE t ENGINE = CSV STORAGE OSS; 
```
- 归档为ORC格式：
```
ALTER TABLE t ENGINE = ORC STORAGE OSS;
```
归档完成后，您可以登录PolarDB控制台查看归档在OSS上的库表信息，或通过SQL语句查看归档表上的数据：
- 查看归档在OSS上的库表信息：登录PolarDB控制台。在目标集群的配置与管理 > 冷数据归档路径下，查看归档在OSS上的库表信息。
- 查看归档表上的数据：通过SQL语句查看归档表上的数据，而不需要修改表的访问方式。例如：
```
SELECT * FROM t;
```

分区表归档至OSS外表

说明

PolarDB MySQL版需要为8.0.2版本且小版本为8.0.2.2.25及以上。
请在控制台上将集群参数partition_level_mdl_enabled设置为ON，开启分区级别的元数据锁功能（MDL），集群详细参数配置请参考设置集群参数和节点参数。
请在控制台上将集群参数loose_use_oss_meta设置为ON，开启use_oss_meta功能，集群详细参数配置请参考设置集群参数和节点参数。

语法

CALL dbms_dlm.archive_partition2table('source_db', 'source_tb', 'source_part', 'archive_db', 'archive_table', 'archive_table_file_filter');

参数说明

参数	说明
source_db	源表数据库名称。
source_tb	源表表名。
source_part	源表归档的分区可以通过逗号分隔，以支持多个分区。
archive_db	目标表数据库名称。
archive_table	目标表表名。
archive_table_file_filter	用于指定目标表是否需要新创建FILE FILTER。

注意事项

如果归档目标OSS表不存在，会自动创建目标OSS表，且目标表上会自动在archive_table_file_filter指定的列上创建oss_file_filter，以提供查询加速功能。同时archive_table_file_filter中会自动加入主键和分区键，帮助提升查询速度。
如果归档目标OSS表存在，则需要比较两个表中列名或者列类型定义是否一致。如果一致，则可以进行归档；如果不一致，则会出错。您可以使用DDL语句让这两个表定义保持一致，具体请参考相关文档冷数据DDL。同时，如果目标表上有oss_file_filter，且定义与call dbms_dlm.archive_partition2table中定义的不一致，则以目标表上的oss_file_filter为准。

如果目标表OSS不存在，但OSS表上有同名文件，则进行归档操作会出现报错信息。报错信息如下：

mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
ERROR 8181 (HY000): [Data Lifecycle Management] errmsg: Target file for archived table exists on oss, please remove it first, or use flag 'FORCE' to overwrite on existing files.

此时如果确认OSS上的残留文件不需要，可以通过存储过程删除OSS上的数据，再执行归档操作。

-- 删除OSS数据
mysql> CALL dbms_oss.delete_table_file('test', 'sales_history');

Query OK, 0 rows affected (0.76 sec)

-- 执行归档操作
mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');

Query OK, 0 rows affected (4.24 sec)

目标表OSS仅支持CSV格式冷数据。
分区表归档后至少还需要包含一个InnoDB分区。
归档后的数据丢失了分区信息，无法直接取回。但是可以通过insert select取回。
不支持单独归档二级分区，可以把整个一级分区下的二级分区全部归档。

下表列出了PolarDB MySQL版分区函数支持的分区类型。

一级分区	二级分区	是否支持归档至OSS外表
HASH	任意类型	不支持一级分区HASH类型归档至OSS外表。
LIST	任意类型	支持。
RANGE	任意类型	支持。
KEY	任意类型	支持。
LIST DEFAULT	任意类型	不支持归档DEFAULT分区。

示例

创建InnoDB分区表并插入数据。

DROP TABLE IF EXISTS `sales`;
-- 创建分区表;
CREATE TABLE `sales` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
  primary key (order_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(order_time) INTERVAL(month, 1)
(PARTITION p0 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB);

DROP PROCEDURE IF EXISTS proc_batch_insert;
delimiter $$
CREATE PROCEDURE proc_batch_insert(IN begin INT, IN end INT, IN name VARCHAR(20))
  BEGIN
  SET @insert_stmt = concat('INSERT INTO ', name, ' VALUES(? , ?, ?);');
  PREPARE stmt from @insert_stmt;
  WHILE begin <= end DO
    SET @ID1 = begin;
    SET @NAME = CONCAT(begin+begin*281313, '@stiven');
    SET @TIME = from_days(begin + 738368);
    EXECUTE stmt using @ID1, @NAME, @TIME;
    SET begin = begin + 1;
    END WHILE;
  END;
$$
delimiter ;

CALL proc_batch_insert(1, 1000, 'sales');

把分区表的p0分区归档至新的执行OSS表。

执行如下命令，查看sales表结构信息。

-- 查看当前InnoDB表的状态;
mysql> SHOW CREATE TABLE sales;

执行结果如下：

*************************** 1. row ***************************
       Table: sales
Create Table: CREATE TABLE `sales` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `order_time` datetime NOT NULL,
  PRIMARY KEY (`order_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800020200 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240201000000 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240301000000 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240401000000 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20240501000000 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB) */
1 row in set (0.03 sec)

执行如下命令，将p0分区归档至OSS表sales_history。

-- 把p0分区归档至OSS表 sales_history，并在id 列上建立OSS_FILE_FILTER;
mysql> CALL dbms_dlm.archive_partition2table('test', 'sales', 'p0', 'test', 'sales_history', 'id');
Query OK, 0 rows affected (1.86 sec)

执行如下命令，查看sales_history表结构信息。

SHOW CREATE TABLE sales_history;

执行结果如下：

*************************** 1. row ***************************;
       Table: sales_history
Create Table: CREATE TABLE `sales_history` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
 `order_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_time`)
) /*!99990 800020213 STORAGE OSS */ ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ /*!99990 800020223 OSS META=1 */ /*!99990 800020224 OSS_FILE_FILTER='id,order_time' */
1 row in set (0.00 sec)

在新的OSS表上进行查询。

说明

您可以启用OSS_FILE_FILTER查询加速功能，以完成加速查询。

mysql> explain SELECT * FROM sales_history WHERE id = 100;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                         |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
|  1 | SIMPLE      | sales_history | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  152 |    10.00 | Using where; With pushed engine condition (`test`.`sales_history`.`id` = 100) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM sales_history WHERE id = 100;
+------+-----------------+---------------------+
| id   | name            | order_time          |
+------+-----------------+---------------------+
|  100 | 28131400@stiven | 2021-11-09 00:00:00 |
+------+-----------------+---------------------+
1 row in set (0.24 sec)

归档分区表

说明

归档分区表功能目前处于灰度阶段，如需使用，请前往配额中心，根据配额ID polardb_mysql_hybrid_partition找到配额名称，在对应的操作列单击申请来开通该功能。
集群版本为PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.14及以上时，支持归档分区表。

归档分区表是对表的分区进行归档，归档后的表为混合分区表，归档后分区的数据文件保存在OSS上。执行完归档操作后，分区在PolarStore上占用的空间会自动释放。

语法

归档为CSV格式的文件：

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV;

归档为ORC格式的文件：

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = ORC;

说明

如果OSS对象存储中存在与归档的数据文件名称冲突的数据文件，系统会报文件已经存在的错误。例如：

Target file for archived table exists on oss.

您可以在以上三个语法中通过添加FORCE关键字来覆盖OSS对象存储中已经存在的数据文件。以归档为CSV格式为例，添加FORCE关键字的语法如下：

ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV FORCE;

参数说明

参数	说明
table_name	需要归档到OSS对象存储中的表名。
part_name	需要归档到OSS对象存储中的分区名。

注意事项

仅支持对InnoDB引擎上的分区表使用冷数据归档功能。
对分区表执行分区归档操作时，至少保留一个分区在InnoDB引擎上，即无法对最后一个InnoDB引擎上的分区执行冷数据归档操作。
归档后的表为混合分区表，不支持对混合分区表执行DDL操作。使用时的注意事项详情请参见创建混合分区。
暂不支持对归档后的分区数据进行修改。
暂不支持对分区表中的二级分区执行冷数据归档操作。
暂不支持对LIST DEFAULT HASH分区表的DEFAULT分区执行冷数据归档操作。
暂不支持对HASH或KEY类型的分区表执行冷数据归档操作。
暂不支持对整张分区表执行手动归档冷数据操作。
归档分区中的数据时，若OSS上存在同名文件，则会执行失败并报类似如下错误信息：
```
Target file for archived table exists on oss.
```
您需要执行以下命令来覆盖OSS上已存在的文件。执行命令时请根据实际情况替换table_name和part_name。
```
ALTER TABLE table_name CHANGE PARTITION part_name ENGINE = CSV/ORC FORCE;
```

示例

将表t中p1和p2分区上的数据以CSV格式归档至OSS。

在数据库中创建InnoDB表t。

CREATE TABLE t(a int, b int, c int, primary key(a))
PARTITION BY RANGE(a)
(PARTITION p1 values less than(100),
 PARTITION p2 values less than(200),
 PARTITION p3 values less than MAXVALUE
);

在表t中插入数据。

INSERT INTO t VALUES(1,1,1);
INSERT INTO t VALUES(10,10,10);
INSERT INTO t VALUES(100,100,100);
INSERT INTO t VALUES(150,150,150);
INSERT INTO t VALUES(200,200,200);
INSERT INTO t VALUES(1000,1000,1000);

执行以下命令，将p1和p2分区上的数据归档至OSS引擎。

归档为CSV格式：

ALTER TABLE t CHANGE PARTITION p1 ENGINE = csv;
ALTER TABLE t CHANGE PARTITION p2 ENGINE = csv;

归档为ORC格式：

ALTER TABLE t CHANGE PARTITION p1 ENGINE = ORC;
ALTER TABLE t CHANGE PARTITION p2 ENGINE = ORC;

归档完成后，您可以登录PolarDB控制台查看归档在OSS上的库表信息，或通过SQL语句查询混合分区表中的数据：

查看归档在OSS上的库表信息：登录PolarDB控制台。在目标集群的配置与管理 > 冷数据归档路径下，查看归档在OSS上的库表信息。
查询混合分区表中的数据：操作详情请参见查询混合分区。

将OSS数据导回至PolarStore

导回在OSS上归档的普通表中的数据

如果您有低频修改归档到OSS上冷数据的需求，您可以通过ALTER ENGINE语法将OSS数据导回至PolarStore进行修改。数据导回至PolarStore后，会同步删除OSS上的冷数据。修改完数据之后，您可以再次将修改后的表归档为OSS表。

语法

ALTER TABLE table_name ENGINE[=]engine_name;

参数说明

参数	参数说明
table_name	需要导回的OSS表的表名。
engine_name	导回后的引擎类型。

注意事项

OSS表为只读状态时，不支持执行修改操作（INSERT、UPDATE和DELETE）。如需修改已经归档的冷数据，您需要将OSS表转换成可读可写的表，如InnoDB表。修改只读状态下的OSS表时，报错信息如下：

1036 - Table 't1' is read only

示例

在数据库oss_test中将OSS表t导回至PolarStore。

ALTER TABLE `oss_test`.`t` ENGINE = InnoDB;

修改InnoDB表t的数据，修改完数据之后，再次将InnoDB引擎中的表t归档至OSS。示例如下：

ALTER TABLE t ENGINE = CSV CONNECTION = 'default_oss_server';

或

ALTER TABLE t ENGINE = CSV STORAGE OSS;

导回在OSS上归档的分区表中的数据

如果您有将已归档的分区表中的数据导回至PolarStore的需求，您可以使用ALTER语句将OSS上的数据导回至PolarStore。数据导回后，会同步删除OSS上的冷数据。

语法

ALTER TABLE table_name REORGANIZE PARTITION part_name INTO (partition_definition);

参数说明

参数	参数说明
table_name	需要导回的OSS表的表名。
part_name	需要导回的分区名称。
partition_definition	与需要导回的分区的`partition_definition`保持一致。

示例

在数据库中，将归档在OSS上的分区表t中p1分区上的数据导回至PolarStore。

ALTER TABLE t REORGANIZE PARTITION p1 INTO(PARTITION p1 values less than(100));

删除OSS上对应的文件

说明

当前仅满足以下版本的PolarDB MySQL版集群支持删除OSS上对应的文件：
- 8.0.1版本且小版本需为8.0.1.1.42及以上。
- 8.0.2版本且小版本需为8.0.2.2.23及以上。
若您的集群的小版本不满足以上版本要求时，暂不支持删除OSS上对应的文件。建议升级您集群的小版本，具体请参见版本管理。

当您将OSS上的表删除或导回至PolarStore后，OSS上的文件不会同步删除。确定数据不再使用后，您可以使用如下语法删除OSS上对应的文件：

CALL dbms_oss.delete_table_file('database_name', 'table_name');

由于删除OSS上对应文件的操作是异步执行的，故需要等待集群中的所有节点都不再依赖OSS文件后才可完全删除，且流量较大时存在一定时延。因此，如果上述命令执行失败并返回错误信息OSS files are still in use时，您可以等待一段时间后再重新执行该命令。