InnoDB / XtraDB Disabling Encryption

Disabling encryption is a little more complicated than turning off the relevant system variables. There are a couple different pieces to this.

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.

You can check the status of the decryption process using the INNODB_TABLESPACES_ENCRYPTION table in the information_schema database. For example, when the following query returns an empty result-set, all of your InnoDB tablespaces are unencrypted.

SELECT * FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ENCRYPTION_SCHEME != 0
   OR ROTATING_OR_FLUSHING != 0 \G

Disabling Encryption for Manually Encrypted Tablespaces

For InnoDB tablespaces that have ENCRYPTED=YES set as a table option, you will need to issue ALTER TABLE statements for each table to set ENCRYPTED=NO before the encryption keys are removed. Otherwise, the tablespace remains encrypted and becomes inaccessible once you've removed the keys. We can use the following process to safely disable encryption for these tablespaces.

First, we can query information_schema.TABLES table and filter on the CREATE_OPTIONS column to find the InnoDB tablespaces that have ENCRYPTED=YES set:

SELECT TABLE_SCHEMA AS "Database", TABLE_NAME AS "Table"
FROM information_schema.TABLES
WHERE ENGINE='InnoDB' 
   AND CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';

Then, for each table in the result-set, you need to issue an ALTER TABLE statement that setsENCRYPTED=NO:

ALTER TABLE my_db.my_encrypted_table ENCRYPTED=NO;

Disabling Encryption for Redo Log

We can use the following process to safely disable encryption of the InnoDB redo log:

1.) If innodb_fast_shutdown=2 is set, then set it to 1:

SET GLOBAL innodb_fast_shutdown = 1;

2.) Stop the MariaDB server process.

3.) Set innodb_encrypt_log=OFF in the MariaDB configuration file.

4.) Delete the existing redo logs.

5.) Start the MariaDB server process.