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.
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 system variable to OFF:
Next, set the system variable to a non-zero value:
Then, set the system variable to 1:
Once set, any InnoDB tablespaces that have the table option set to DEFAULT will be in the background by the InnoDB .
You can of the decryption process using the table in the database.
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 and the storage engine before removing the encryption key management settings from your configuration file.
In the case of manually encrypted InnoDB tablespaces, (that is, those where the table option is set to YES), you must issue an 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 table to find the encrypted tables. This can be done with a WHERE clause filtering the CREATE_OPTIONS column.
For each table in the result-set, issue an statement, setting the table option to NO.
Once you have removed encryption from all the tables, your InnoDB deployment is unencrypted. Be sure to also remove encryption from the as well as and any other storage engines that support encryption before removing the encryption key management settings from your configuration file.
InnoDB does not permit manual encryption changes to tables in the tablespace using . Encryption of the tablespace can only be configured by setting the value of the system variable. This means that when you want to encrypt or decrypt the tablespace, you must also set a non-zero value for the system variable, and you must also set the system variable to 1 to ensure that the system tablespace is properly encrypted or decrypted by the background threads. See for more information.
The system variable controls the configuration of encryption for the . To disable it, remove the system variable from your server's , and then restart the server.
InnoDB uses the 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, set the system variable to OFF in a server in an . Once this is done, restart the MariaDB Server. When the Server comes back online, it begins writing unencrypted data to the Redo Log.
After the server has been successfully restarted with encryption disabled, you may remove the that had been used. If you try to disable encryption for the Redo Log and remove the plugin in a single step, InnoDB will be unable to decrypt the log in order to remove the encryption.
This page is licensed: CC BY-SA / Gnu FDL
SET GLOBAL innodb_encrypt_tables = OFF;SET GLOBAL innodb_encryption_threads = 4;SET GLOBAL innodb_encryption_rotate_key_age = 1;SELECT COUNT(*) AS "Number of Encrypted Tablespaces"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE ENCRYPTION_SCHEME != 0
OR ROTATING_OR_FLUSHING != 0;SELECT TABLE_SCHEMA AS "Database", TABLE_NAME AS "Table"
FROM information_schema.TABLES
WHERE ENGINE='InnoDB'
AND CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+
ALTER TABLE tab1
ENCRYPTED=NO;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 0 | 100 |
+----------+-------------------+----------------+