InnoDB / XtraDB Disabling Encryption
Contents
The process involved in safely disabling encryption for your InnoDB tables is a little more complicated than that of enabling encryption. Turning off the relevant system variables doesn't decrypt the tables. If you turn it off and remove the encryption key management plugin, it'll render the encrypted data inaccessible.
In order to safely disable encryption, you first need to decrypt the tablespaces and the Redo Log, then turn off the system variables. The specifics of this process depends on whether you are using automatic or manual encryption of the InnoDB tablespaces.
Disabling Encryption for Automatically Encrypted Tablespaces
When an InnoDB tablespace has the ENCRYPTED
table option set to DEFAULT
and the innodb_encrypt_tables
system variable is set to ON
or FORCE
, the tablespace's encryption is automatically managed by the background encryption threads. When you want to disable encryption for these tablespaces, you must ensure that the background encryption threads decrypt the tablespaces before removing the encryption keys. Otherwise, the tablespace remains encrypted and becomes inaccessible once you've removed the keys.
To safely decrypt the tablespaces, first, set the innodb_encrypt_tables
system variable to OFF
:
SET GLOBAL innodb_encrypt_tables = OFF;
Next, set the innodb_encryption_threads
system variable to a non-zero value:
SET GLOBAL innodb_encryption_threads = 4;
Then, set the innodb_encryption_rotate_key_age
system variable to 1
:
SET GLOBAL innodb_encryption_rotate_key_age = 1;
Once set, any InnoDB tablespaces where the ENCRYPTED
table option is DEFAULT
, decrypt in the background using the InnoDB background encryption threads.
Decryption Status
You can check the status of the decryption process using the INNODB_TABLESPACES_ENCRYPTION
table in the information_schema
database.
SELECT COUNT(*) AS "Number of Encrypted Tablespaces" FROM information_schema.INNODB_TABLESPACES_ENCRYPTION WHERE ENCRYPTION_SCHEME != 0 OR ROTATING_OR_FLUSHING != 0;
This query shows the number of InnoDB tablespaces that currently using background encryption threads. Once the count reaches 0, then all of your InnoDB tablespaces are unencrypted. Be sure to also remove encryption on the Redo Log and the Aria storage engine before removing the encryption key management settings from your configuration file.
Disabling Encryption for Manually Encrypted Tablespaces
In the case of manually encrypted InnoDB tablespaces, (that is, those where the ENCRYPTED
table option is set to YES
), you must issue an ALTER TABLE
statement to decrypt each tablespace before removing the encryption keys. Otherwise, the tablespace remains encrypted and becomes inaccessible without the keys.
First, query the Information Schema TABLES
table to find the encrypted tables. This can be done with a WHERE
clause filtering the CREATE_OPTIONS
column.
SELECT TABLE_SCHEMA AS "Database", TABLE_NAME AS "Table" FROM information_schema.TABLES WHERE ENGINE='InnoDB' AND CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';
For each table in the result-set, issue an ALTER TABLE
statement, setting the ENCRYPTED
table option to NO
.
ALTER TABLE test.t1 ENCRYPTED=NO;
Once you have removed encryption from all the tables, your InnoDB deployment is unencrypted. Be sure to also remove encryption from the Redo Log as well as Aria and any other storage engines that support encryption before removing the encryption key management settings from your configuration file.
Disabling Encryption for the Redo Log
InnoDB uses the Redo Log in crash recovery. By default, these events are written to file in an unencrypted state. In removing data-at-rest encryption for InnoDB, be sure to also disable encryption for the Redo Log before removing encryption key settings. Otherwise the Redo Log can become inaccessible without the encryption keys.
First, check the value on the innodb_fast_shutdown
system variable.
SHOW VARIABLES LIKE 'innodb_fast_shutdown'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | innodb_fast_shutdown | 2 | +----------------------+-------+
When the value is set to 2
, InnoDB purges the Redo Log during shutdown. Ensure that the variable is set to 1
.
SET GLOBAL innodb_fast_shutdown = 1;
Then, update the configuration file, setting the innodb_encrypt_log
to OFF
in the configuration file. Once this is done, restart the MariaDB Server. When the Server comes back online, it begins writing unencrypted data to the Redo Log.