This blog post will discuss the issues and solutions for MySQL Data at Rest encryption.
Data at Rest Encryption is not only a good-to-have feature, but it is also a requirement for HIPAA, PCI and other regulations.
There are three major ways to solve data encryption at rest:
Full-disk encryption
Database-level (table) encryption
Application-level encryption, where data is encrypted before being inserted into the database
I consider full disk encryption to be the weakest method, as it only protects from someone physically removing the disks from the server. Application-level encryption, on the other hand, is the best: it is the most flexible method with almost no overhead, and it also solves data in-flight encryption. Unfortunately, it is not always possible to change the application code to support application-level encryption, so database-level encryption can be a valuable alternative. Sergei Golubchik, Chief Architect at MariaDB, outlined the pluses and minuses of database level encryption during his
Sergei Golubchik, Chief Architect at MariaDB, outlined the pluses and minuses of database level encryption during his session at Percona Live Amsterdam:
Pros
Full power of DBMS is available
Full power of DBMS is availableEasy to implement
Easy to implementOnly database can see the data
Only databases can see the dataPer-table encryption, per-table keys, performance
Per-table encryption, per-table keys, performanceCannot be done per-user
Cons
Cannot be done per-user
Does not protect against malicious root user
Data at Rest Encryption: Database-Level Options
Currently, there are two options for data at rest encryption at the database level:
MariaDB 10.1.3+ support encryption (using Google patch)
MySQL 5.7.11+ (and Percona Server 5.7.11) has InnoDB tablespace level encryption
MariaDB’s implementation is different from MySQL 5.7.11. MySQL 5.7.11 only encrypts InnoDB tablespace(s), while MariaDB has an option to encrypt undo/redo logs, binary logs/relay logs, etc. However, there are some limitations (especially together with Galera Cluster):
No key rotation in the open source plugin version (MySQL 5.7.11 has a key rotation)
mysqlbinlog does not work with encrypted binlogs (bug reported)
Percona XtraBackup does not work, so we are limited to RSYNC as SST method for Galera Cluster, which is a blocking method (one node will not be available for writes during the SST). The latest Percona XtraBackup works with MySQL 5.7.11 tablespace encryption
The following data is not encrypted (bug reported)
Galera gcache + Galera replication data
General log / slow query log
Database level encryption also has its weakness:
Root and MySQL users can read the keyring file, which defeats the purpose. However, it is possible to place a key on the mounted drive and unmount it when MySQL starts (that can be scripted). The downside of this is that if MySQL crashes, it will not be restarted automatically without human intervention.
Both MariaDB version and MySQL version only encrypt data when writing to disk – data is not encrypted in RAM, so a root user can potentially attach to MySQL with gdb/strace or other tools and read the server memory. In addition, with gdb it is possible to change the root user password structure and then use mysqldump to copy data. Another potential method is to kill MySQL and start it with skip-grant-tables. However, if the key is unmounted (i.e., on USB drive), MySQL will either not start or will not be able to read the encrypted tablespace.
MariaDB Encryption Example
To enable the full level encryption we can add the following options to my.cnf:[mysqld]
plugin-load-add=file_key_management.so
file_key_management_filekey = FILE:/mount/keys/mysql.key
file-key-management-filename = /mount/keys/mysql.enc
innodb-encrypt-tables = ON
innodb-encrypt-log = 1
innodb-encryption-threads=1
encrypt-tmp-disk-tables=1
encrypt-tmp-files=1
encrypt-binlog=1
file_key_management_encryption_algorithm = AES_CTRAfter starting MariaDB with those settings, it will start encrypting the database in the background. The file_key_management plugin is used; unfortunately, it does not support key rotation. The actual keys are encrypted with:# openssl enc -aes-256-cbc -md sha1 -k <key> -in keys.txt -out mysql.encThe encryption <key> is placed in /mount/keys/mysql.key.
After starting MySQL, we can unmount the “/mount/key” partition. In this case, the key will not be available and a potential hacker will not be able to restart MySQL with “–skip-grant-tables” option (without passwords). However, it also prevents normal restarts, especially SSTs (cluster full sync).
Additional notes:
Encryption will affect the compression ratio, especially for the physical backups (logical backups, i.e. mysqldump does not matter as the data retrieved is not encrypted). If your original compressed backup size was only 10% of the database size, it will not be the case for the encrypted tables.
Data is not encrypted in flight and will not be encrypted on the replication slaves unless you enable the same options on the slaves. The encryption is also local to the server, so when encryption was just enabled on a server some tables may not be encrypted yet (but will be eventually)
To check which tables are encrypted, use the Information Schema INNODB_TABLESPACES_ENCRYPTION table, which contains encryption information. To find all tables that are encrypted, use this query:
select * from information_schema.INNODB_TABLESPACES_ENCRYPTION where ENCRYPTION_SCHEME=1
MySQL 5.7 Encryption Example
To enable encryption, add the following option to my.cnf:[mysqld]
early-plugin-load=keyring_file.so
keyring_file_data=/mount/mysql-keyring/keyringAgain, after starting MySQL we can unmount the “/mount/mysql-keyring/” partition.
To start encrypting the tables, we will need to run alter table table_name encryption=’Y’ , as MySQL will not encrypt tables by default.
The latest Percona Xtrabackup also supports encryption, and can backup encrypted tables.
To find all encrypted tablespaces in MySQL/Percona Server 5.7.11, we can use information_schema.INNODB_SYS_TABLESPACES and the flag field. For example, to find normally encrypted tables, use the following query:mysql> select * from information_schema.INNODB_SYS_TABLESPACES where flag = 8225G
*************************** 1. row ***************************
SPACE: 4688
NAME: test/t1
FLAG: 8225
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 98304
ALLOCATED_SIZE: 98304
*************************** 2. row ***************************
SPACE: 4697
NAME: sbtest/sbtest1_enc
FLAG: 8225
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 255852544
ALLOCATED_SIZE: 255856640
2 rows in set (0.00 sec)You can also use this query instead: select * from information_schema.tables where CREATE_OPTIONS like ‘%ENCRYPTION="Y"%’;.
Performance overhead
This is a debatable topic, especially for the MariaDB implementation when everything is configured to be encrypted. During my tests I’ve seen ~10% of overhead for the standalone MySQL instance, and ~20% with Galera Cluster.
The MySQL 5.7/Percona Server 5.7 tablespace-level encryption shows an extremely low overhead, however, that needs to be tested in different conditions.
Conclusion
Even with all the above limitations, database-level encryption can be a better option than the filesystem-level encryption if the application can not be changed. However, it is a new feature (especially MySQL 5.7.11 version) and I expect a number of bugs here.
via Planet MySQL
MySQL Data at Rest Encryption