解除加密

将表从加密的表空间中移出即可解除表的加密。

背景信息

假设表 t1 的加密状态如下，且表 t1 在加密的表空间 sectest_ts1 中。

SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES;
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
| TABLE_ID | TABLE_NAME | TABLESPACE_ID | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS | CON_ID |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
|   500010 | t1         |        500009 | aes-256       | YES       | xxxxxxxxxxxxxxxxxxxxxxxxxxxx7882 |      xxxx08 |                0 |                0 | NORMAL |      0 |
+----------+------------+---------------+---------------+-----------+----------------------------------+-------------+------------------+------------------+--------+--------+
1 row in set

SHOW CREATE TABLE t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table

                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE `sectest_ts1` |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

将表从加密表空间中移出

1. 管理员用户登录到数据库的 MySQL 租户。

2. 对表执行渐进合并。

   a. 将 progressive_merge_num 的值设置为大于 1 的数。

   示例如下：

   ALTER TABLE t1 SET progressive_merge_num = 3;       
   

   b. 手动发起多轮渐进合并。

   发起一轮渐进合并的语句如下：

   ALTER SYSTEM MAJOR FREEZE;
   

   说明

   解除加密是异步过程，解密过程需要对表进行渐进合并保证所有数据解密。

3. 创建未加密的表空间 ts2。

   CREATE TABLESPACE ts2;
   

4. 进入表所在的数据库。

5. 将表 t1 从加密的表空间 sectest_ts1 中移到未加密的表空间 ts2 中。

   ALTER TABLE t1 TABLESPACE ts2;
   

   操作成功后，执行 SHOW CREATE TABLE 语句，可以看到表 t1 已经移到了表空间 ts2 中。

   SHOW CREATE TABLE t1;
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Table | Create Table                                                                                                                                                                                                                                                                 |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | t2    | CREATE TABLE `t1` (
     `id1` int(11) DEFAULT NULL,
     `id2` int(11) DEFAULT NULL
   ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0  TABLESPACE `ts2` |
   +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

6. 查询 oceanbase.V$OB_ENCRYPTED_TABLES 视图，确认表的加密状态。

   SELECT * FROM oceanbase.V$OB_ENCRYPTED_TABLES;
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY | MASTERKEYID | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS     | CON_ID |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   | 500010           | t1         | 500015           |               | NO        |              |           0 |                2 |                0 | NORMAL     |      0 |
   +------------------+------------+------------------+---------------+-----------+--------------+-------------+------------------+------------------+------------+--------+
   1 row in set
   

   可以看到表 t1 的 ENCRYPTED 字段为 NO，说明表 t1 解除加密操作成功。

相关文档

• 为新创建的表设置存储加密
• 为已有表设置存储加密