MySQL InnoDB Tablespace Encryption

MySQL Tablespace Encryption is relatively simple to set up. You have to be using MySQL 5.7, innodb_file_per_table

• https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html 

The MySQL documentation covers everything you need to set this up:

• https://dev.mysql.com/doc/refman/5.7/en/keyring.html
• https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-encryption
• http://mysqlserverteam.com/mysql-enterprise-transparent-data-encryption-tde-provides-at-rest-encryption-for-physical-innodb-tablespace-data-files/

But my blog post would be very short if I just shared a link. So....

Once upgraded and on MySQL 5.7 you need to have a valid location for your key file.  I have a MySQL datadir as /var/lib/mysql/data  this allows default locations for people to find it if needed but also allows a simple mounted location for more disks if needed as well. This also avoids having to do additional edits to selinux and etc since it already allows /var/lib/mysql.

chmod 750  /var/lib/mysql/keyring*
chown mysql:mysql  /var/lib/mysql/keyring

touch /var/lib/mysql/keyring
Edit the mysql .cnf file :

 ls  /usr/lib64/mysql/plugin/keyring_file.so
/usr/lib64/mysql/plugin/keyring_file.so

[mysqld]
# Keyring Plugin Installation
early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring



After MySQL startup check your plugins:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    ->        FROM INFORMATION_SCHEMA.PLUGINS
    ->        WHERE PLUGIN_NAME LIKE 'keyring%';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        | 
| keyring_udf  | ACTIVE        |
+--------------+---------------+


INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so';
CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so';


mysql>  use test;
mysql>  SELECT keyring_key_generate('MyKey1', 'AES', 32);

mysql>  CREATE TABLE `enc_test` (
  `enc_test_id` varchar(255) NOT NULL,
  PRIMARY KEY (`enc_test_id`)
) ENGINE=InnoDB ;

mysql>  alter table test.enc_test encryption='Y';

mysql>  show create table enc_test \G
*************************** 1. row ***************************
       Table: enc_test
Create Table: CREATE TABLE `enc_test` (
  `enc_test_id` varchar(255) NOT NULL,
  PRIMARY KEY (`enc_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'
1 row in set (0.00 sec)



OK great you have a table.
What about backups.

Well from my current experience innobackupex does not work, but you can still use xtrabackup.

xtrabackup --defaults-file=/home/anothermysqldba/.my.cnf   --port=3306 --socket=/tmp/mysql.sock  --backup  --keyring-file-data=/var/lib/mysql/keyring --server_id=1 --target-dir=/backups/database/ ; xtrabackup --prepare --keyring-file-data=/var/lib/mysql/keyring  --target-dir=/backups/database/