In this first of a series of blog posts, we’ll look at MySQL encryption at rest.
At Percona, we work with a number of clients that require strong security measures for PCI, HIPPA and PHI compliance, where data managed by MySQL needs to be encrypted “at rest.” As with all things open source, there several options for meeting the MySQL encryption at rest requirement. In this three-part series, we cover several popular options of encrypting data and present the various pros and cons to each solution. You may want to evaluate which parts of these tutorials work best for your situation before using them in production.
Part one of this series is implementing disk-level encryption using crypt+LUKS.
In MySQL 5.7, InnoDB has built-in encryption features. This solution has some cons, however. Specifically, InnoDB tablespace encryption doesn’t cover undo logs, redo logs or the main ibdata1 tablespace. Additionally, binary-logs and slow-query-logs are not covered under InnoDB encryption.
Using crypt+LUKS, we can encrypt everything (data + logs) under one umbrella – provided that all files reside on the same disk. If you separate the various logs on to different partitions, you will have to repeat the tutorial below for each partition.
LUKS Tutorial
The Linux Unified Key Setup (LUKS) is the current standard for disk encryption. In the examples below, the block device /dev/sda4 on CentOS 7 is encrypted using a generated key, and then mounted as the default MySQL data directory at /var/lib/mysql.
WARNING! Loss of the key means complete loss of data! Be sure to have a backup of the key.
Install the necessary utilities:
# yum install cryptsetup
Creating, Formatting and Mounting an Encrypted Disk
The cryptsetup command initializes the volume and sets an initial key/passphrase. Please note that the key is not recoverable, so do not forget it. Take the time now to decide where you will securely store a copy of this key. LastPass Secure Notes are a good option, as they allow file attachments. This enhances our backup later on.
Create a passphrase for encryption. Choose something with high entropy (i.e., lots of randomness). Here are two options (pick one):
# openssl rand -base64 32
# date | md5 | rev | head -c 24 | md5 | tail -c 32
Next, we need to initialize and format our partition for use with LUKS. Any mounted points using this block device must be unmounted beforehand.
WARNING! This command will delete ALL DATA ON THE DEVICE! BE SURE TO COMPLETE ANY BACKUPS BEFORE YOU RUN THIS!
# cryptsetup -c aes-xts-plain -v luksFormat /dev/sda4
You will be prompted for a passphrase. Provide the phrase you generated above. After you provide a passphrase, you now need to “open” the encrypted disk and provide a device mapper name (i.e., an alias). It can be anything, but for our purposes, we will call it “mysqldata”:
# cryptsetup luksOpen /dev/sda4 mysqldata
You will be prompted for the passphrase you used above. On success, you should see the device show up:
# ls /dev/mapper/
lrwxrwxrwx 1 root root 7 Jun 2 11:50 mysqldata -> ../dm-0
You can now format this encrypted block device and create a filesystem:
# mkfs.ext4 /dev/mapper/mysqldata
Now you can mount the encrypted block device you just formatted:
# mount /dev/mapper/mysqldata /var/lib/mysql
Unfortunately you cannot add this to /etc/fstab to automount on a server reboot, since the key is needed to “open” the device. Please keep this in mind that if your server ever reboots MySQL will not start since the data directory is unavailable until opened and mounted (we will look at how to make this work using scripts in Part Two of this series).
Creating a Backup of Encryption Information
The header of a LUKS block device contains information regarding the current encryption key(s). Should this ever get damaged, or if you need to recover because you forgot the new passphrase, you can restore this header information:
# cryptsetup luksHeaderBackup --header-backup-file ${HOSTNAME}_`date +%Y%m%d`_header.dat /dev/sda4
Go ahead and make a SHA1 of this file now to verify that it doesn’t get corrupted later on in storage:
# sha1sum ${HOSTNAME}_`date +%Y%m%d`_header.dat
GZip the header file. Store the SHA1 and the .gz file in a secure location (for example, attach it to the secure note created above). Now you have a backup of the key you used and a backup of the header which uses that key.
Unmounting and Closing a Disk
If you know you will be storing a disk, or just want to make sure the contents are not visible (i.e., mounted), you can unmount and “close” the encrypted device:
# umount /var/lib/mysql/
# cryptsetup luksClose mysqldata
In order to mount this device again, you must “open” it and provide one of the keys.
Rotating Keys (Adding / Removing Keys)
Various compliance and enforcement rules dictate how often you need to rotate keys. You cannot rotate or change a key directly. LUKS supports up to eight keys per device. You must first add a new key to any slot (other than the slot currently occupying the key you are trying to remove), and then remove the older key.
Take a look at the existing header information:
# cryptsetup luksDump /dev/sda4
LUKS header information for /dev/sda4
Version: 1
Cipher name: aes
Cipher mode: cbc-essiv:sha256
Hash spec: sha1
Payload offset: 4096
MK bits: 256
MK digest: 81 37 51 6c d5 c8 32 f1 7a 2d 47 7c 83 62 70 d9 f7 ce 5a 6e
MK salt: ae 4b e8 09 c8 7a 5d 89 b0 f0 da 85 7e ce 7b 7f
47 c7 ed 51 c1 71 bb b5 77 18 0d 9d e2 95 98 bf
MK iterations: 44500
UUID: 92ed3e8e-a9ac-4e59-afc3-39cc7c63e7f6
Key Slot 0: ENABLED
Iterations: 181059
Salt: 9c a9 f6 12 d2 a4 2a 3d a4 08 b2 32 b0 b4 20 3b
69 13 8d 36 99 47 42 9c d5 41 35 8c b3 d0 ff 0e
Key material offset: 8
AF stripes: 4000
Key Slot 1: DISABLED
Key Slot 2: DISABLED
Key Slot 3: DISABLED
Key Slot 4: DISABLED
Key Slot 5: DISABLED
Key Slot 6: DISABLED
Key Slot 7: DISABLED
Here we can see a key is currently occupying “Key Slot 0”. We can add a key to any DISABLED key slot. Let’s use slot #1:
# cryptsetup luksAddKey --key-slot 1 -v /dev/sda4
Enter any passphrase:
Key slot 0 unlocked.
Enter new passphrase for key slot:
Verify passphrase:
Command successful.
LUKS asks for “any” passphrase to authenticate us. Had there been keys in other slots, we could have used any one of them. As only one is currently saved, we have to use it. We can then add a new passphrase for slot 1.
Now that we have saved the new key in slot 1, we can remove the key in slot 0.
# cryptsetup luksKillSlot /dev/sda4 0
Enter any remaining LUKS passphrase:
No key available with this passphrase.
In the example above, the existing passphrase stored in slot 0 was used. This is not allowed. You cannot provide the passphrase for the same slot you are attempting to remove.
Repeat this command and provide the passphrase for slot 1, which was added above. We are now able to remove the passphrase stored in slot 0:
# cryptsetup luksKillSlot /dev/sda4 0
Enter any remaining LUKS passphrase:
# cryptsetup luksDump /dev/sda4
LUKS header information for /dev/sda4
Version: 1
Cipher name: aes
Cipher mode: cbc-essiv:sha256
Hash spec: sha1
Payload offset: 4096
MK bits: 256
MK digest: 81 37 51 6c d5 c8 32 f1 7a 2d 47 7c 83 62 70 d9 f7 ce 5a 6e
MK salt: ae 4b e8 09 c8 7a 5d 89 b0 f0 da 85 7e ce 7b 7f
47 c7 ed 51 c1 71 bb b5 77 18 0d 9d e2 95 98 bf
MK iterations: 44500
UUID: 92ed3e8e-a9ac-4e59-afc3-39cc7c63e7f6
Key Slot 0: DISABLED
Key Slot 1: ENABLED
Iterations: 229712
Salt: 5d 71 b2 3a 58 d7 f8 6a 36 4f 32 d1 23 1a df df
cd 2b 68 ee 18 f7 90 cf 58 32 37 b9 02 e1 42 d6
Key material offset: 264
AF stripes: 4000
Key Slot 2: DISABLED
Key Slot 3: DISABLED
Key Slot 4: DISABLED
Key Slot 5: DISABLED
Key Slot 6: DISABLED
Key Slot 7: DISABLED
After you change the passphrase, it’s a good idea to repeat the header dump steps we performed above and store the new passphrase in your vault.
Conclusion
Congratulations, you have now learned how to encrypt and mount a partition using LUKS! You can now use this mounted device just like any other. You can also restore a backup and start MySQL.
In Part Two, we will cover using InnoDB tablespace encryption.
via Planet MySQL
MySQL Encryption at Rest – Part 1 (LUKS)