How to Encrypt Your MySQL & MariaDB Backups
We usually take care of things we value, whether it is an expensive smartphone or the company’s servers. Data is one of the most important assets of the organisation, and although we do not see it, it has to be carefully protected. We implement data at rest encryption to encrypt database files or whole volumes which contain our data. We implement data in transit encryption using SSL to make sure no one can sniff and collect data sent across networks. Backups are no different. No matter if this is a full backup or incremental, it will store at least a part of your data. As such, backups have to be encrypted too. In this blog post, we will look into some options you may have when it comes to encrypting backups. First though, let’s look at how you can encrypt your backups and what could be use cases for those methods.
How to encrypt your backup?
Encrypt local file
First of all, you can easily encrypt existing files. Let’s imagine that you have a backup process storing all your backups on a backup server. At some point you decided it’s the high time to implement offsite backup storage for disaster recovery. You can use S3 or similar infrastructure from other cloud providers for that. Of course, you don’t want to upload unencrypted backups anywhere outside of your trusted network, therefore it is critical that you implement backup encryption one way or the other. A very simple method, not requiring any changes in your existing backup scripts would be to create a script which will take a backup file, encrypt it and upload it to S3. One of the methods you can use to encrypt the data is to use openssl:
openssl enc -aes-256-cbc -salt -in backup_file.tar.gz -out backup_file.tar.gz.enc -k yoursecretpassword
This will create a new, encrypted file, ‘backup_file.tar.gz.enc’ in the current directory. You can always decrypt it later by running:
openssl aes-256-cbc -d -in backup_file.tar.gz.enc -out backup_file.tar.gz -k yoursecretpassword
This method is very simple, but it has some drawbacks. The biggest one is the disk space requirements. When encrypting like we described above, you have to keep both unencrypted and encrypted file and in the result you require a disk space twice the size of the backup file. Of course, depending on your requirements, this might be something you want – keeping non-encrypted files locally will improve recovery speed – after all decrypting the data will also take some time.
Encrypt backup on the fly
To avoid the need of storing both encrypted and unencrypted data, you may want to implement the encryption at the earlier stage of the backup process. We can do that through pipes. Pipes are, in short, a way of sending the data from one command to another. This makes it possible to create a chain of commands that processes data. You can generate the data, then compress it and encrypt. An example of such chain might be:
mysqldump --all-databases --single-transaction --triggers --routines | gzip | openssl enc -aes-256-cbc -k mypass > backup.xb.enc
You can also use this method with xtrabackup or mariabackup. In fact, this is the example from MariaDB documentation:
mariabackup --user=root --backup --stream=xbstream | openssl enc -aes-256-cbc -k mypass > backup.xb.enc
If you want, you can even try to upload data as the part of the process:
mysqldump --all-databases --single-transaction --triggers --routines | gzip | openssl enc -aes-256-cbc -k mysecretpassword | tee -a mysqldump.gz.enc | nc 10.0.0.102 9991
As a result, you will see a local file ‘mysqldump.gz.enc’ and copy of the data will be piped to a program which will do something about it. We used ‘nc’, which streamed data to another host on which following was executed:
nc -l 9991 > backup.gz.enc
In this example we used mysqldump and nc but you can use xtrabackup or mariabackup and some tool which will upload the stream to Amazon S3, Google Cloud Storage or some other cloud provider. Any program or script which accepts data on stdin can be used instead of ‘nc’.
Use embedded encryption
In some of the cases, a backup tool has embedded support for encryption. An example here is xtrabackup, which can internally encrypt the file. Unfortunately, mariabackup, even though it is a fork of xtrabackup, does not support this feature.
Before we can use it, we have to create a key which will be used to encrypt the data. It can be done by running the following command:
[email protected]:~# openssl rand -base64 24 HnliYiaRo7NUvc1dbtBMvt4rt1Fhunjb
Xtrabackup can accept the key in plain text format (using –encrypt-key option) or it can read it from file (using –encrypt-key-file option). The latter is safer as passing passwords and keys as plain text to commands result in storing them in the bash history. You can also see it clearly on the list of running processes, which is quite bad:
root 1130 0.0 0.6 65508 4988 ? Ss 00:46 0:00 /usr/sbin/sshd -D root 13826 0.0 0.8 93100 6648 ? Ss 01:26 0:00 _ sshd: [email protected] root 25363 0.0 0.8 92796 6700 ? Ss 08:54 0:00 _ sshd: vagrant [priv] vagrant 25393 0.0 0.6 93072 4936 ? S 08:54 0:01 | _ sshd: [email protected]/1 vagrant 25394 0.0 0.4 21196 3488 pts/1 Ss 08:54 0:00 | _ -bash root 25402 0.0 0.4 52700 3568 pts/1 S 08:54 0:00 | _ sudo su - root 25403 0.0 0.4 52284 3264 pts/1 S 08:54 0:00 | _ su - root 25404 0.0 0.4 21196 3536 pts/1 S 08:54 0:00 | _ -su root 26686 6.0 4.0 570008 30980 pts/1 Sl+ 09:48 0:00 | _ innobackupex --encrypt=AES256 --encrypt-key=TzIZ7g+WzLt0PXWf8WDPf/sjIt7UzCKw /backup/
Ideally, you will use the key stored in a file but then there’s a small gotcha you have to be aware of.
[email protected]:~# openssl rand -base64 24 > encrypt.key [email protected]:~# innobackupex --encrypt=AES256 --encrypt-key-file=/root/encrypt.key /backup/ . . . xtrabackup: using O_DIRECT InnoDB: Number of pools: 1 encryption: unable to set libgcrypt cipher key - User defined source 1 : Invalid key length encrypt: failed to create worker threads. Error: failed to initialize datasink.
You may wonder what the problem is. It’ll become clear when we will open encrypt.key file in a hexadecimal editor like hexedit:
00000000 6D 6B 2B 4B 66 69 55 4E 5A 49 48 77 39 42 36 72 68 70 39 79 6A 56 44 72 47 61 79 45 6F 75 6D 70 0A mk+KfiUNZIHw9B6rhp9yjVDrGayEoump.
You can now notice the last character encoded using ‘0A’. This is basically the line feed character, but it is taken under consideration while evaluating the encryption key. Once we remove it, we can finally run the backup.
[email protected]:~# innobackupex --encrypt=AES256 --encrypt-key-file=/root/encrypt.key /backup/ xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=185M --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_doublewrite=1 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=1 xtrabackup: recognized client arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=185M --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_doublewrite=1 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=1 --databases-exclude=lost+found --ssl-mode=DISABLED encryption: using gcrypt 1.6.5 181116 10:11:25 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 181116 10:11:25 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). 181116 10:11:25 version_check Connected to MySQL server 181116 10:11:25 version_check Executing a version check against the server... 181116 10:11:25 version_check Done. 181116 10:11:25 Connecting to MySQL server host: localhost, user: backupuser, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 5.7.23-23-57 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /var/lib/mysql xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:100M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 67108864 xtrabackup: using O_DIRECT InnoDB: Number of pools: 1 181116 10:11:25 >> log scanned up to (2597648) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 19 for mysql/server_cost, old maximum was 0 181116 10:11:25  Encrypting ./ibdata1 to /backup/2018-11-16_10-11-25/ibdata1.xbcrypt 181116 10:11:26 >> log scanned up to (2597648) 181116 10:11:27 >> log scanned up to (2597648) 181116 10:11:28  ...done 181116 10:11:28  Encrypting ./mysql/server_cost.ibd to /backup/2018-11-16_10-11-25/mysql/server_cost.ibd.xbcrypt 181116 10:11:28  ...done 181116 10:11:28  Encrypting ./mysql/help_category.ibd to /backup/2018-11-16_10-11-25/mysql/help_category.ibd.xbcrypt 181116 10:11:28  ...done 181116 10:11:28  Encrypting ./mysql/slave_master_info.ibd to /backup/2018-11-16_10-11-25/mysql/slave_master_info.ibd.xbcrypt 181116 10:11:28  ...done
As a result we will end up with a backup directory full of encrypted files:
[email protected]:~# ls -alh /backup/2018-11-16_10-11-25/ total 101M drwxr-x--- 5 root root 4.0K Nov 16 10:11 . drwxr-xr-x 16 root root 4.0K Nov 16 10:11 .. -rw-r----- 1 root root 580 Nov 16 10:11 backup-my.cnf.xbcrypt -rw-r----- 1 root root 515 Nov 16 10:11 ib_buffer_pool.xbcrypt -rw-r----- 1 root root 101M Nov 16 10:11 ibdata1.xbcrypt drwxr-x--- 2 root root 4.0K Nov 16 10:11 mysql drwxr-x--- 2 root root 12K Nov 16 10:11 performance_schema drwxr-x--- 2 root root 12K Nov 16 10:11 sys -rw-r----- 1 root root 113 Nov 16 10:11 xtrabackup_checkpoints -rw-r----- 1 root root 525 Nov 16 10:11 xtrabackup_info.xbcrypt -rw-r----- 1 root root 2.7K Nov 16 10:11 xtrabackup_logfile.xbcrypt
Xtrabackup has some other variables which can be used to tune encryption performance:
- –encrypt-threads allows for parallelization of the encryption process
- –encrypt-chunk-size defines a working buffer for encryption process.
Should you need to decrypt the files, you can use innobackupex with –decrypt option for that:
[email protected]:~# innobackupex --decrypt=AES256 --encrypt-key-file=/root/encrypt.key /backup/2018-11-16_10-11-25/
As xtrabackup does not clean encrypted files, you may want to remove them using following one-liner:
for i in `find /backup/2018-11-16_10-11-25/ -iname "*.xbcrypt"`; do rm $i ; done
Backup encryption in ClusterControl
With ClusterControl encrypted backups are just one click away. All backup methods (mysqldump, xtrabackup or mariabackup) support encryption. You can both create a backup ad hoc or you can prepare a schedule for your backups.
In our example we picked a full xtrabackup, we will store it on the controller instance.
On the next page we enabled the encryption. As stated, ClusterControl will automatically create an encryption key for us. This is it, when you click at the “Create Backup” button a process will be started.
New backup is visible on the backup list. It is marked as encrypted (the lock icon).
We hope that this blog post gives you some insights into how to make sure your backups are properly encrypted.
Subscribe to get our best and freshest content