Without encryption of data at rest, system role with access to file system can view data even without proper database permissions. A proper authentication can be used to protect data used by an application but data sitting in file system has been area of risk.

With encryption at rest, it has been possible to protect data in transit when moving over the network. Data at Rest Encryption is a requirement for HIPAA, PCI regulations.

Data Files at Risk

When insert queries run to store data in tables, the data will be stored in respective dataDir ie /usr/local/var/mysql. A system user that has filesystem access can use strings or xxd commands to view content in data files, bin logs or redo logs.

One way to avoid this risk either not to store sensitive data in database or encrypt sensitive data columns or encrypt the file system itself.

Other way is to implement Data encryption at rest.

Implement Data Encryption at rest, MySQL

First step to create a key using openssl command

openssl enc -aes-256-cbc -md sha1 -k <enc password or passphase> -in keys.txt -out mysql.enc

Second, edit /etc/my.cnf and provide encryption configurations

plugin-load-add=file_key_management.so
file-key-management-filename=/var/lib/mysql/keys.txt
innodb-encrypt-tables
innodb-encrypt-log
encrypt-binlog

plugin-load-add option will load the encryption plugin on mysql start.
file-key-management-filename option to provide reference to encryption key file.
innodb-encrypt-tables option will encrypt tables by default.
innodb-encrypt-log option will encrypt redo logs by default.
encrypt-binlog option will encrypt binlogs.

Third, restart mysql

service mysql restart

Fourth, enable the same options on the replication slaves

Fifth, verify tables are encrypted using below mysql query

select * from information_schema.INNODB_TABLESPACES_ENCRYPTION where ENCRYPTION_SCHEME=1;

innodb-encrypt-tables modes

Three ways to encrypt tables

1. innodb-encrypt-tables option in my.cnf will encrypt all tables unless table creation statement provided “encrypted=no”
   
2. innodb-encrypt-tables option in my.cnf will encrypt all tables and will not allow table creation with “encrypted=no”
   
3. innodb-encrypt-tables option is not provided in my.cnf but the plugin presents, it can explicitely encrypt a table by including “encrypted=yes” in table creation statement.
   

Performance Overhead

~5% to ~10% performance overhead observed in previous MySQL deployments with database enforced encryption mode.

Database-level encryption and selective tables encryption can be a better option than the filesystem-level encryption.