I’m a big fan of encrypting my data (be that at-rest or in-tranzit) and not because I have some secrets I need to protect but rather I care about my privacy and simply don’t want anybody to find anything about me or the people I care about without me being willing to share that information (thinking like this kept me and my family away from phishing scams and other bad actors that make a living off our private information – this is for example why at home we use pi-hole – look it up 🙂 )
One such method of protecting yourself is encryption at-rest for ones MySQL databases (you can find many applications and services that provide encryption at-rest, I’m simply focusing on MySQL – MariaDB).
This will prevent a third party from accessing your mysql data after you’re done using that old notebook you used for work or personal projects or that cheap VPS you bought for running your site and will also prevent someone with access to your files from being able to open them elsewhere without having the relevant key and password. You might think this is not needed BUT there have been cases where disks were not wiped before being reused which allowed some ppl to access other ppls data 🙂 .
Remember one thing -> security is about making life as hard as possible for someone with bad intentions to reach whatever it is that you feel is worth protecting. In this context by encrypting your database at-rest you’re making things harder for someone that has the intention of stealing some or all of your data that you store in some database.
Here’s how you can easily do it:
mkdir -p /etc/my.cnf.d/rest
cd /etc/my.cnf.d/rest
generate enc key:
openssl rand -hex 32 > /etc/my.cnf.d/rest/keyfile
open the key file and add ‘1;’ in front of the key
store passwd for enc key in some file:
echo -n ‘somerandompassword’ > /etc/my.cnf.d/rest/keyfile.passwd
password protect the enc key:
openssl enc -aes-256-cbc -md sha1 -pass file:/etc/my.cnf.d/rest/keyfile.passwd -in /etc/my.cnf.d/rest/keyfile -out /etc/my.cnf.d/rest/keyfile.enc
check:
openssl aes-256-cbc -d -md sha1 -pass file:/etc/my.cnf.d/rest/keyfile.passwd -in /etc/my.cnf.d/rest/keyfile.enc
Add following to /etc/my.cnf.d/mariadb-server.conf in [mysqld]
section:
# at-rest encryption
plugin_load_add = file_key_management
file_key_management_filename = /etc/my.cnf.d/rest/keyfile.enc
file_key_management_filekey = FILE:/etc/my.cnf.d/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC
innodb_encrypt_tables = ON
innodb_encrypt_temporary_tables = ON
innodb_encrypt_log = ON
innodb_encryption_threads = 4
innodb_encryption_rotate_key_age = 1
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt-binlog = 1
aria_encrypt_tables = ON
service mariadb restart
remove clear text key from disk:
rm -f /etc/mysql/encryption/keyfile
And your done!
Remeber! This is not a perfect solution, no single security measure is perfect BUT the more security measures you have in place the harder it will be for someone to steal your data (be that sensitive or not)
Enjoy!