Data-at-Rest Encryption: Encrypting Tables Automatically

MariaDB supports data-at-rest encryption for tables using the InnoDB storage engines. When enabled, the server automatically encrypts data when it is written to, and decrypts the data when it is read. You must configure an encryption key management plugin, in this case, the Entrust Key Management plugin, to store, retrieve, and manage the keys that are used when encrypting and decrypting data. The plugin and system variables must be added to your configuration file.

Note: InnoDB does not permit manual encryption changes to tables in the system tablespace using ALTER TABLE statements.

All tables will be encrypted automatically.

1. Navigate to your configuration file, for example, /etc/mysql/my.conf.

2. Open the configuration file with your preferred editor, for example, vi.

3. Add the following lines to the configuration file: 

   [mariadb]

   # Entrust Key Management

   plugin_load_add = entrust_key_management

    

   # InnoDB Encryption

   innodb_encrypt_tables = ON

   innodb_encrypt_temporary_tables = ON

   innodb_encrypt_log = ON

   innodb_encryption_threads = 4

   innodb_encryption_rotate_key_age = 0

   log_error=/var/log/mysql/mariadb.log

4. Save the configuration file.

5. Restart the MariaDB service using the systemctl restart mariadb command.

6. Log in to MariaDB to start your encryption.

7. Use the following queries to encrypt the tables: 

   • Create the encrypted table: 

     CREATE TABLE table1 (i int) ENGINE=InnoDB;

   • Create the encrypted table by providing the encryption key ID: 

     CREATE TABLE table2 (i int) ENGINE=InnoDB ENCRYPTION_KEY_ID=2;

   • Insert the data: 

     INSERT INTO your_table_name (column1, column2, column3)

     VALUES ('value1', 'value2', 'value3');

   • Retrieve data: 

     SELECT * FROM your_table_name;

   • Get encryption details about the tables: 

     SELECT * FROM information_schema.innodb_tablespaces_encryption G;