InnoDB tablespace encryption uses a two tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDBtable is encrypted, a tablespace key is encrypted and stored in the tablespace header. When an application or authenticated user wants to access encrypted tablespace data, InnoDB uses a master encryption key to decrypt the tablespace key.
The InnoDB tablespace encryption feature relies on a keyring plugin for master encryption key management.
Configuration:
The keyring file plugin has to be loaded before the innoDB engine starts, so instead of installing the plugin we have to use early-plugin-load parameter in my.cnf
Create the directory and make required changes in my.cnf
vi /etc/my.cnf
[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/path/to/file/keyringfile
Make sure the path exists and mysql is the owner
The file keyring_file.so should be available in the directory pointed by the parameter
plugin_dir.
Location defined by
keyring_file_data will be used to store the master encryption key data in a keyring file, which is required only while starting the database.
service mysql restart
To check the plugin status:
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'keyring%';
Encrypting the Keyring
InnoDB tablespace encryption need the keyring only at startup of the mysql instance. So key is kept encrypted and is decrypted only for starting up the mysql instances.
We can use gpg encryption to accomplish this this.
Command to encrypt:
gpg2 -o keyring.gpg -c --cipher-algo AES256 keyring
where keyring is the actual keyring and keyring.gpg is the encrypted file. Passphrase given for encrypting the key ring has to be kept in the password vault.
Command to decrypt:
gpg2 -o keyring -d keyring.gpg
where keyring is the actual keyring and keyring.gpg is the encrypted file. Passphrase would given from the password vault.
======================================================================
How does encryption work:
mysql> create table secret (name varchar(30));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into secret values ('secret name');
Query OK, 1 row affected (0.00 sec)
[root@ip-10-10-45-19 oploc]# strings secret.ibd
infimum
supremum
secret name
mysql> alter table secret encryption='Y';
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
[root@ip-10-10-45-19 oploc]# strings secret.ibd
5f4889a6-65bc-11e4-9f98-129030d4e0d6
pLBJ
Xpxk
QmC"E~U
M _8
b3iix
<r=r|
w]l\
<l|9
CzOr
_K7o
|<|R
>snm
|JT<gU
=&3Z
ZHNj
To select the encrypted tables:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
Xtrabackup:
Backup: Normal backup commands (provided the xtrabackup version > =2.4.4)
Ex: innobackupex --slave-info --user=uuuuu --password=xxxx --socket=/mysql/mysqld.sock --compress --stream=xbstream --compress-threads=4 --parallel=4 ./ > /Backup/fullb.xbstream.qpress
Prepare: have to use xtrabackup --prepare, not innobackupex –apply-log
Ex: xtrabackup --prepare --use-memory=20G --target-dir=/mysql/restore --keyring-file-data=/path/to/file/keyring
Restore: Normal move-back / copy-back
Ex: innobackupex --move-back =/mysql/restore
Before starting the Database, the keyring file from the source has to be copied to the destination’s keyring file location ; and if before encrypting any new table in the destination server, master key has to be rotated and backup of the keyring file has to be taken
REF:
No comments:
Post a Comment