Data has to be protected, this is quite clear. We protect the data by implementing firewalls, defining user privileges, roles, setting up an encryption of the data at rest and in transit. What is important to keep in mind is that the backups also have to be protected. If you create a logical backup of all of your data, this data is human readable. Physical backups store data in the binary format but it can also be accessed using proper tools and techniques. The question remains – how can I protect my data stored in the form of backups? The answer to this question is encryption.
By encrypting the data we are preventing anyone who does not have a proper encryption key or a passphrase from accessing the data, ensuring its security. Let’s take a look at how we can encrypt MySQL backups.
Encrypting backup created using mysqldump
The most common tool used to encrypt the backup is, arguably, openssl. It is also the tool that we would like to propose. The process to encrypt the data is fairly simple – we are going to pipe the backup through openssl for encryption:
root@vagrant:~# mysqldump --single-transaction --events --triggers --routines --all-databases | gzip | /usr/bin/openssl enc -aes-256-cbc -pbkdf2 -iter 100000 -k somekeyphrase > mysqldump.gz.enc
This command creates a compressed and encrypted backup file that can be further piped to netcat for remote storage, stored in S3 or any other external location.
If you would like to decrypt the file, you can do it using the following command:
root@vagrant:~# cat mysqldump.gz.enc | /usr/bin/openssl enc -d -aes-256-cbc -pbkdf2 -iter 100000 -k somekeyphrase | gunzip > backup.sql
Encrypting backup created by XtraBackup
With XtraBackup we have two options to pick from. We can simply use the same method as we did for mysqldump – pipe the output through openssl. The command to perform such backup is:
root@vagrant:~# xtrabackup --backup --stream=xbstream --target-dir=/. | gzip | /usr/bin/openssl enc -aes-256-cbc -pbkdf2 -iter 100000 -k somekeyphrase > xtrabackup.gz.enc xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=2000 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --databases-exclude=lost+found --user=backupuser --password=* --backup=1 --stream=xbstream --target-dir=/. xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda) 220213 00:04:13 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'backupuser' (using password: YES). 220213 00:04:13 version_check Connected to MySQL server 220213 00:04:13 version_check Executing a version check against the server... 220213 00:04:13 version_check Done. 220213 00:04:13 Connecting to MySQL server host: localhost, user: backupuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-16.1
If you would like to decrypt such a backup, you can do it using the following command. Again, we are using pipe to decrypt and decompress and send the data to xbstream binary to convert it from the stream format into separate files:
root@vagrant:~# cat /root/xtrabackup.gz.enc | /usr/bin/openssl enc -d -aes-256-cbc -pbkdf2 -iter 100000 -k somekeyphrase | gunzip | xbstream -x -C /backup/
The second option is to use an internal backup encryption mechanism that is built into XtraBackup. First, we should create a proper encryption key. It can be done using openssl binary:
root@vagrant:~# openssl rand -base64 24 jHanyC51wZMTYnAma1RtzoPe+fkcHi/Q
Then we can use it to create an encrypted backup:
root@vagrant:~# xtrabackup --backup --target-dir=/backup/1 --encrypt=AES256 --encrypt-key="jHanyC51wZMTYnAma1RtzoPe+fkcHi/Q" xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=2000 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --databases-exclude=lost+found --user=backupuser --password=* --backup=1 --target-dir=/backup/1 --encrypt=AES256 --encrypt-key=* xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda) 220213 00:17:00 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'backupuser' (using password: YES). 220213 00:17:00 version_check Connected to MySQL server 220213 00:17:00 version_check Executing a version check against the server... 220213 00:17:00 version_check Done. 220213 00:17:00 Connecting to MySQL server host: localhost, user: backupuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-16.1
As an output you will see *.xbcrypt files stored in the backup directory.
If we would like to decrypt such backup, ideally it would be to copy it to a separate location and then run:
root@vagrant:/backup/torestore# xtrabackup --decrypt=AES256 --encrypt-key="jHanyC51wZMTYnAma1RtzoPe+fkcHi/Q" --target-dir=/backup/torestore/
Then, once the backup is decrypted, you can proceed with standard restore. To clean up the encrypted files you can just remove all *.xbcrypt files. For recent versions of XtraBackup it is also possible to use –remove-original flag which will remove all encrypted files once the decryption process completes successfully.
As you can see, encrypting the backup is not a very complex process. Please keep in mind that we have only scratched the surface. Keyphrases should be securely handled and stored. Data might have to be transferred between different servers. Still, the encryption part is the most important – we really want the data to be protected properly.
