Enable storage encryption for an existing table

This topic describes how to enable storage encryption for an existing table.

Limitations

You cannot enable encryption for the sys tenant.

Background information

The following sections describe how to enable storage encryption for the t1 table in the encrypted sectest_ts1 tablespace.

Enable storage encryption in internal mode

In internal mode, the encryption information of the master key is managed in internal tables, and clogs are not encrypted to avoid circular dependency during log replay.

1. Log on to a MySQL tenant as an administrator.

2. Execute the following statement to enable storage encryption in internal mode:

   The tde_method parameter specifies the encryption method for a transparent tablespace. The default value is none, which indicates that encryption is disabled for the transparent tablespace.

   For more information about the tde_method parameter, see tde_method.

   Notice

   After the tde_method parameter is set, it cannot be modified.

   obclient> ALTER SYSTEM SET tde_method='internal';
   

3. Execute the following statement to check whether the value of the tde_method parameter is internal on all OBServer nodes of the tenant:

   obclient> SHOW PARAMETERS LIKE 'tde_method';
   

4. If yes, execute the following statement to generate the master key:

   Note

   The statement takes effect only when the value of the tde_method parameter is internal on all OBServer nodes of the tenant.

   obclient> ALTER INSTANCE ROTATE INNODB MASTER KEY;
   

5. Create a tablespace and specify the encryption algorithm.

   You can specify one of the following encryption algorithms: aes-256, aes-128, aes-192, and sm4-cbc. If you set the sectest_ts1 encryption parameter to y, the aes-256 algorithm is used.

   For example:

   obclient> CREATE TABLESPACE sectest_ts1 encryption = 'y';
   

Move an existing table into an encrypted tablespace

1. Log on to a MySQL tenant of the database as a regular user.

2. Log on to the database where the t1 table resides and move the table to the sectest_ts1 tablespace.

   obclient> ALTER TABLE t1 TABLESPACE sectest_ts1;
   

Perform storage encryption on a table

1. Log on to a MySQL tenant of the database as a regular user.

2. Set the progressive_merge_num parameter to configure a full compaction or progressive compaction task for the table.

   The progressive_merge_num parameter specifies the number of rounds of progressive compactions on a table. The default value is 0, which indicates incremental compaction. If you set the parameter to 1, a full compaction is performed.

   In general, a full compaction is performed on a table. However, a full compaction of a table with a large amount of data may take a long time. In that case, we recommend that you perform a progressive compaction.

   • Perform a full compaction on the table

     1. Set the progressive_merge_num parameter to 1.

        obclient> ALTER TABLE t1 set progressive_merge_num = 1;
        

     2. Log on to the sys tenant as the root user and manually initiate a major compaction.

        For more information, see Manually initiate a major compaction.

        Note

        After a full compaction, all table data is encrypted. You can query the sys.v$encrypted_tables view to check the encryption status.

     3. After the compaction is completed, set the progressive_merge_num parameter to 0.

        obclient> ALTER TABLE t1 set progressive_merge_num = 0;
        

   • Perform a progressive compaction on the table

     1. Set the progressive_merge_num parameter to a value greater than 1, and run the OPTIMIZE command.

        For example:

        obclient> ALTER TABLE t1 SET progressive_merge_num = 3;
        
        obclient> OPTIMIZE TABLE t1;
        

     2. Log on to the sys tenant as the root user, and manually initiate multiple rounds of progressive compactions to encrypt all the macroblocks of the table and table indexes.

        Execute the following statement to initiate a progressive compaction:

        obclient> ALTER SYSTEM MAJOR FREEZE;
        

        The number of rounds of manual incremental compactions is progressive_merge_num × max_kept_major_version_number.

        Note

        During a progressive compaction, you can query the sys.v$encrypted_tables view to check the encryption status in real time.

        The max_kept_major_version_number parameter specifies the number of frozen versions to be retained. It can have a value between 1 and 16, with a default value of 2.

        For more information about the max_kept_major_version_number parameter, see max_kept_major_version_number.

3. After the compaction is completed, you can execute the following statement to check the view and verify whether all macroblocks are encrypted:

   For example:

   obclient> SELECT * FROM oceanbase.v$encrypted_tables;
   +-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
   | TENANT_ID | TABLE_ID         | TABLE_NAME | TABLESPACE_ID    | ENCRYPTIONALG | ENCRYPTED | ENCRYPTEDKEY                     | MASTERKEYID      | BLOCKS_ENCRYPTED | BLOCKS_DECRYPTED | STATUS |
   +-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
   |      1003 | 1102810162709330 |   t1       | 1102810162660329 | aes-256       | YES       | 4B0C00AFEFEEF506D4AB804E5071A733 | 1102810162660329 |                2 |                0 | NORMAL |
   +-----------+------------------+------------+------------------+---------------+-----------+----------------------------------+------------------+------------------+------------------+--------+
   1 row in set
   

   Check the query results. If the value of the BLOCKS_DECRYPTED field is 0, all macroblocks are encrypted.

   For more information about fields in the v$encrypted_tables view, see v$encrypted_tables.