Enable encryption at rest

One of our recent projects required us to ensure that any data is encrypted at rest.

Luckily, MySQL supports this (if you turn it on).

To enable it, add the following to your my.cnf file:

early_plugin_load = keyring_file.so
keyring_file_data = /var/lib/mysql/keyring_file
default_table_encryption = ON

If you want to encrypt every table, you can run the following to find all those unencrypted tables:

SELECT table_schema, table_name, create_options FROM information_schema.TABLES
WHERE create_options NOT LIKE '%encryption%' AND table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema') AND ENGINE NOT IN ('BLACKHOLE')

And if you want a script to encrypt all these, just run the output from the following:

SELECT CONCAT("ALTER TABLE ", table_schema, ".", table_name, " ENCRYPTION='Y';")
FROM information_schema.TABLES
WHERE create_options NOT LIKE '%encryption%' AND table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema') AND ENGINE NOT IN ('BLACKHOLE');

Want to get in touch? mail@adsar.co.uk