virtualMachinemysql

Encrypt a MySQL database table

NOTE: Table encryption support is only available for InnoDB tables stored as individual files (the innodb_file_per_table option, enabled by default).

Follow the steps below to configure table encryption support:

  • Edit the /opt/bitnami/mysql/conf/my.cnf configuration file.

  • Add the following lines to the configuration file, within the [mysqld] section, to activate the keyring_file plugin:

      early-plugin-load=keyring_file.so
      keyring_file_data=/opt/bitnami/mysql/data/keyring
    

    NOTE: The keyring file will be automatically created in the above location when the first table is encrypted. Keep a backup of this file as the data stored in the encrypted tables cannot be recovered without it.

  • Restart the MySQL server:

      $ sudo /opt/bitnami/ctlscript.sh restart mysql
    
  • Confirm that the keyring_file plugin is active by running the query below in the MySQL client:

      SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
    

You should now be able to create an encrypted table by adding the ENCRYPTED=‘Y’ clause to any CREATE TABLE command. Here is an example:

CREATE TABLE mytable (id INT, value VARCHAR(255)) ENCRYPTION='Y'

Tables which are not already encrypted can be encrypted by using an ALTER TABLE command, such as the one below:

ALTER TABLE mytable ENCRYPTION='Y'
Last modification February 9, 2023