MySQL Tablespace Encryption is relatively simple to set up. You have to be using MySQL 5.7, innodb_file_per_table
The MySQL documentation covers everything you need to set this up:
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.
After MySQL startup check your plugins:
OK great you have a table.
What about backups.
Well from my current experience innobackupex does not work, but you can still use xtrabackup.
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
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)
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/
