Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17269

Document how to safely decrypt InnoDB tables

    XMLWordPrintable

Details

    Description

      We should document how to safely decrypt InnoDB tables. The documentation doesn't currently say:

      https://mariadb.com/kb/en/library/encrypting-data-for-innodb-xtradb/

      It looks like the process would go like this:

      1.) Set innodb_encrypt_tables=OFF:

      SET GLOBAL innodb_encrypt_tables=OFF;
      

      Make sure to also set it in the configuration file.

      https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_encrypt_tables

      2.) Make sure innodb_encryption_threads>0 is set:

      SET GLOBAL innodb_encryption_threads=4;
      

      https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_encryption_threads

      3.) Make sure that innodb_encryption_rotate_key_age=1 is set:

      SET GLOBAL innodb_encryption_rotate_key_age=1;
      

      https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_encryption_rotate_key_age

      4.) Check for individual tables that have explicitly been encrypted:

      SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS 
      FROM information_schema.TABLES
      WHERE CREATE_OPTIONS LIKE '%`ENCRYPTED`=YES%';
      

      5.) For each table that has explicitly been encrypted, explicitly decrypt it:

      ALTER TABLE encrypted_tab ENCRYPTED=NO;
      

      6.) Allow the background encryption threads to decrypt all tables. You will know all tables are decrypted when the following query returns 0 rows:

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

      Attachments

        Issue Links

          Activity

            People

              KennethDyer Kenneth Dyer (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.