Encrypt a MySQL database table
NOTE: We are in the process of modifying the file structure and configuration for many Bitnami stacks. On account of these changes, the file paths stated in this guide may change depending on whether your Bitnami stack uses native Linux system packages (Approach A), or if it is a self-contained installation (Approach B). To identify your Bitnami installation type and what approach to follow, run the command below:
$ test ! -f "/opt/bitnami/common/bin/openssl" && echo "Approach A: Using system packages." || echo "Approach B: Self-contained installation."
The output of the command indicates which approach (A or B) is used by the installation, and will allow you to identify the paths, configuration and commands to use in this guide. Refer to the FAQ for more information on these changes.
NOTE: We are in the process of modifying the configuration for many Bitnami stacks. On account of these changes, the file paths and commands stated in this guide may change depending on whether your Bitnami stack uses MySQL or MariaDB. To identify which database server is used in your stack, run the command below:
$ test -d /opt/bitnami/mariadb && echo "MariaDB" || echo "MySQL"
The output of the command indicates which database server (MySQL or MariaDB) is used by the installation, and will allow you to identify which guides to follow in our documentation for common database-related operations.
NOTE: Table encryption support is only available for InnoDB tables stored as individual files (the innodb_file_per_table option, enabled by default).
Perform the steps below on each node of the cluster to configure table encryption support:
Edit the configuration file, which depending on your installation type will be in one of the following locations:
- For Bitnami installations following Approach A (using Linux system packages): /opt/bitnami/mysql/conf/my.cnf
- For Bitnami installations following Approach B (self-contained installations): /opt/bitnami/mysql/my.cnf
Add the following lines to the configuration file, within the [mysqld] section, to activate the keyring_file plugin:
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 service bitnami 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'
So long as all the cluster nodes are configured to support table encryption, changes to encrypted tables on the master node will replicate as normal to the slave nodes. The keyring file will also be automatically created on the slave nodes if it does not exist.