Tablespace encryption uses a two tier encryption key architecture, consisting of
- a master encryption key and
- a tablespace keys.
When a tablespace is encrypted, a tablespace key is encrypted and stored in the tablespace header.
The decrypted version of a tablespace key never changes, but the master encryption key can be changed as required. This action is referred to as master key rotation.
A secure and robust encryption key management solution is critical for security and for compliance with various security standards
Tablespace encryption supports the Advanced Encryption Standard (AES) block-based encryption algorithm.
Prerequisites
1) keyring_plugin
A keyring plugin must be installed and configured.
See [how-to-load-keyring-plugin](# How to load the plugin keyring_file)
To verify the plugin is installed run
mysql> select * from mysql.plugin;
+--------------+-----------------+
| name | dl |
+--------------+-----------------+
| keyring_file | keyring_file.so |
+--------------+-----------------+
1 row in set (0.00 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+-----------------+---------+
| keyring_file | ACTIVE | KEYRING | keyring_file.so | GPL |
+----------------------------+----------+--------------------+-----------------+---------+
mysql> show variables like '%keyring%';
+--------------------+--------------------------------+
| Variable_name | Value |
+--------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
| keyring_operations | ON |
+--------------------+--------------------------------+
2) file-per-table Encryption is only supported for file-per-table tablespaces. To create a table in a file-per-table tablespace, ensure that innodb_file_per_table is enabled (the default) before executing a CREATE TABLE statement.
3) Backup master key production When encrypting production data, ensure that you take steps to prevent loss of the master encryption key. If the master encryption key is lost, data stored in encrypted tablespace files is unrecoverable. If you use the keyring_file or keyring_encrypted_file plugin, create a backup of the keyring data file immediately after creating the first encrypted tablespace, before master key rotation, and after master key rotation
Enabling and Disabling File-Per-Table Tablespace Encryption
To enable encryption for a new file-per-table tablespace, specify the ENCRYPTION option in a CREATE TABLE statement.
mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
To enable encryption for an existing file-per-table tablespace, specify the ENCRYPTION option in an ALTER TABLE statement.
mysql> ALTER TABLE t1 ENCRYPTION='Y';
To disable encryption for file-per-table tablespace, set ENCRYPTION='N' using ALTER TABLE.
mysql> ALTER TABLE t1 ENCRYPTION='N';InnoDB Tablespace Encryption and Master Key Rotation
The master encryption key should be rotated periodically and whenever you suspect that the key has been compromised.
Master key rotation is an atomic, instance-level operation. Each time the master encryption key is rotated, all tablespace keys in the MySQL instance are re-encrypted and saved back to their respective tablespace headers.
Rotating the master encryption key requires the SUPER privilege.
ALTER INSTANCE ROTATE INNODB MASTER KEY;Exporting Encrypted Tablespaces
When an encrypted tablespace is exported, InnoDB generates a transfer key that is used to encrypt the tablespace key. The encrypted tablespace key and transfer key are stored in a tablespace_name.cfp file.
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
-> WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| en | t1 | ENCRYPTION="Y" |
+--------------+------------+----------------+
1 row in set (0.01 sec)
mysql> SELECT SPACE, NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='en/t1';
+-------+-------+------------+
| SPACE | NAME | SPACE_TYPE |
+-------+-------+------------+
| 26 | en/t1 | Single |
+-------+-------+------------+
1 row in set (0.00 sec)Identifying Encrypted Tablespaces
Encryption option is recorede in the create_options column of IS.tables
InnoDB Tablespace Encryption and Replication
Some constrains about The ALTER INSTANCE ROTATE INNODB MASTER KEY statement
- It is only supported in replication env if master and slave support tablespace encryption.
- It is written to the binary log for replication on slaves.
- It fails, it is not logged to the binary log and is not replicated on slaves.
- It fails if the keyring plugin is installed on the master but not on the slave.
If the keyring_file or keyring_encrypted_file plugin is installed on both the master and a slave but the slave does not have a keyring data file, the replicated ALTER INSTANCE ROTATE INNODB MASTER KEY statement creates the keyring data file on the slave, assuming the keyring file data is not cached in memory. ALTER INSTANCE ROTATE INNODB MASTER KEY uses keyring file data that is cached in memory, if available.
Managing the keyring file
To change the location of the keyring file
mysql -e "show variables like '%keyring%'"
+--------------------+------------------------------------+
| Variable_name | Value |
+--------------------+------------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring-seb |
| keyring_operations | ON |
+--------------------+------------------------------------+
service mysql stop
mv /var/lib/mysql-keyring/keyring /var/lib/mysql-keyring/keyring-seb
vi /etc/mysql/my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring-seb
If the master key is not found, the error throws this error
mysql> select * from en.t1;
ERROR 3185 (HY000): Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
The you need to restart the server and points to the right location of the master key file
How to load the plugin keyring_file
Load manually the plugin also causes “permanent” plugin registration: The plugin is listed in the mysql.plugin
install plugin keyring_file soname 'keyring_file.so';
Specify the plugin at the start time
mysqld --early-plugin-load="keyring_file"
Add the plugin-load to the my.cnf file In this case, the plugin is not registered in mysql.plugin. Restarting the server without the --plugin-load option causes the plugin not to be loaded at startup.
[mysqld]
plugin-load=myplugin=somepluglib.soInnoDB Tablespace Encryption Usage Notes
- When altering an existing tablespace with the ENCRYPTION option, remind that only COPY algorithm can be used, ( INPLACE algorithm is not supported)
- If the server exits or is stopped during normal operation, it is recommended to restart the server using the same encryption settings that were configured previously.
- The first master encryption key is generated when the first new or existing tablespace is encrypted.
- COMPRESSION is performed before ENCRYPTION
- Uninstalling the keyring_file or keyring_encrypted_file plugin does not remove an existing keyring data file.
- It is recommended that you not place a keyring data file under the same directory as tablespace data files.
- Modifying the keyring_file_data or keyring_encrypted_file_data setting at runtime or when restarting the server can cause previously encrypted tablespaces to become inaccessible, resulting in lost data.
InnoDB Tablespace Encryption Limitations
- Advanced Encryption Standard (AES) is the only supported encryption algorithm.
- Encryption is only supported for a file-per-table tablespaces. Encryption is not supported for other tablespace types.
- You cannot move or copy a table from an encrypted file-per-table tablespace to a tablespace type that does not support encryption.
- Tablespace encryption only applies to data in the tablespace. Data is not encrypted in the redo log, undo log, or binary log
- It is not permitted to change the storage engine of a table that resides or previously resided in an encrypted tablespace.
Other notes
How to install mysql-server5.7 on Trusty (14.04) https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-14-04