The new EU data protection legislation, GDPR, mandates that every reasonable effort and technology is used to protect personal data. It even goes as far as specifying encryption. The open-source database MariaDB (a drop-in, compatible replacement for MySQL) has supported “encryption at rest” since version 10.1. Use MariaDB encryption to satisify the GDPR recommendation of using encryption to protect your personal data.

Most websites and applications would need significant work to employ data encryption. Websites particularly may not have control over the underlying software responsible for data storage, especially if they’re built on a CMS like WordPress.

Let’s have a look at what it can do and how you can set it up. You won’t have to make a single change to your website or application!

What is MariaDB encryption at rest?

All database servers physically store the data in the tables in files on the server’s file system. If you were to examine these files, you’d be able to retrieve the data from them if you understood enough about the file format.

“Encryption at rest” is the practice of encrypting the data before it is written to these files, and decrypting it when it is read. If an attacker was to get hold of these files, the data would be unreadable.

Encryption at rest is supported in most major database servers and is completely transparent to the applications accessing it, making it an easy way to protect your users’ data.

What isn’t MariaDB encryption at rest?

Encryption at rest only protects the data when it is physically stored on disk (i.e. it is “at rest.”)

If an attacker can login to your MariaDB database, they can read the data in plain text, just like your application.

If your database and application reside on different servers, data will still traverse the network in plain text, as it will have already been decrypted when it was read off the disk.

Encryption at rest is an additional protection and is not a replacement for firewalls, strong passwords, user permissions and SSL encryption between the client and server.

Configuring MariaDB encryption

Enabling encryption at rest is as simple as adding the following options into the config file and restarting the server. MariaDB will take care of encrypting the existing data held in your data files.

If you are using MariaDB on a Debian or Ubuntu system using the official repositories, I recommend adding this to a new file under /etc/mysql/conf.d with the extension .cnf. For example: /etc/mysql/conf.d/encryption.cnf

Alternatively, add these lines (minus the [mysqld] line) to an existing MariaDB config file within the “mysqld” section.

[mysqld]
plugin_load_add=file_key_management
file_key_management = ON
file_key_management_encryption_algorithm=aes_cbc
file_key_management_filename = /etc/mysql/mariadb_encryption.key
file_key_management_filekey = 
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
aria_encrypt_tables = ON
encrypt_tmp_disk_tables = ON
innodb_encryption_threads = 8

After copying & pasting the above config, do not restart the service yet, as we need to review what this configuration means.

Creating the encryption key

MariaDB will encrypt your data using a “key.” You can use the OpenSSL toolkit to generate a unique key:

openssl rand -hex 16 > /etc/mysql/mariadb_encryption.txt

The above command will copy it into a text file. We now need to add an identifier to this key so MariaDB can see it.

Open up the file we just created in the nano text editor:

nano /etc/mysql/mariadb_encryption.txt

Prepend the line with “1;” so it looks like the following:

1;3e27544cbb53d144c5ed10af54f8e033

Encrypting the encryption file

You’ll notice that the file referenced in our MariaDB config above ends with “.key” but the file we created ends with “.txt”. The reason for this is that we don’t want the plain-text file hanging around on our system. It contains the password for your encrypted data.

To protect it from prying eyes, we will encrypt it – also using the OpenSSL toolkit. Firstly, we need to create a unique password. You can use the Password Generator on Solid Tools for Developers to create this.

Replace “” with the password you created, and run the following command:

openssl enc -aes-256-cbc -md sha1 -k  -in /etc/mysql/mariadb_encryption.txt \
-out /etc/mysql/mariadb_encryption.key

You will also need to add your password to the line beginning with file_key_management_filekey in the config file you added in the “configuring MariaDB encryption” section.

Without this key, the data can not be read, so ensure you have a backup of these files and the passphrase and store it safely!

Restart MariaDB

Once all the files and passwords are in place, restart MariaDB.

A systemctl status -n 200 mysql command should show references to encryption when it starts back up:

[Note] InnoDB: Encrypting redo log: 2*50331648 bytes; LSN=13013960246
[Note] InnoDB: Starting to delete and rewrite log files.
[Note] InnoDB: Setting log file ./ib_logfile101 size to 50331648 bytes
[Note] InnoDB: Setting log file ./ib_logfile1 size to 50331648 bytes
[Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
[Note] InnoDB: New log files created, LSN=13013960246
...
[Note] InnoDB: Creating #1 encryption thread id 140509293037312 total threads 8.
[Note] InnoDB: Creating #2 encryption thread id 140509284644608 total threads 8.
[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
[Note] InnoDB: Creating #3 encryption thread id 140509276251904 total threads 8.
[Note] InnoDB: Creating #4 encryption thread id 140509267859200 total threads 8.
[Note] InnoDB: Creating #5 encryption thread id 140508848453376 total threads 8.
[Note] InnoDB: Creating #6 encryption thread id 140508840060672 total threads 8.
[Note] InnoDB: Creating #7 encryption thread id 140508831667968 total threads 8.
[Note] InnoDB: Creating #8 encryption thread id 140508823275264 total threads 8.

You can now login and run a simple SELECT query against a database, which should return the results as normal.

Securing the encryption key

By now you should be up and running with an encrypted database, yet we still have one problem.

If an attacker were to gain access to your server and encrypted data files, and your key was also there for the taking, this whole exercise would be pretty pointless. Your key should not be accessible from the same machine as your data files.

Yet MariaDB needs your key to run, so how do you achieve this separation of security?

The answer lies in the fact that MariaDB only needs your key when it starts up. Once the service is running, the key can be deleted.

There are currently 2 options for storing and managing your encryption key within MariaDB: file-system or Amazon Web Services (AWS.)

I don’t want to introduce a dependency on another provider (AWS) so I opted for a file-based key. I store it on the Memstore cloud storage platform away from my server, in a physically separate data-centre.

My server executes the /etc/rc.local script during its boot-up process. I added some commands to this script to download the encryption key from my Memstore account using rclone, launch MariaDB and delete the key once it has started up.

My /etc/rc.local script looks like this:

# Fetch the encryption file for MariaDB, restart service, then remove file
/usr/bin/rclone -L copy memstore:backups/mariadb_encryption.key /etc/mysql/
/bin/systemctl restart mysql
sleep 10
rm /etc/mysql/mariadb_encryption.key
touch /tmp/mariadb.started
exit 0

Ensure this script runs on startup by executing the command systemctl enable rc.local.