Chances are that your data is one of the most important assets you hold. As such, it should be taken care of – it should be regularly backed up. Backups should be thoroughly tested to ensure that they are able to be recovered. In some cases you might also need to encrypt and / or compress your backups – we are looking at these options in this post.
MySQL Backup Encryption
There are a couple of ways to encrypt backups in MySQL. You can:
- Encrypt the file itself;
- Use an encryption feature provided by MySQL Enterprise Backup;
- Encrypt the backups using Percona XtraBackup;
- Encrypt the backups using ClusterControl.
Encrypting the File
One of the methods you can use to encrypt the file is OpenSSL:
$ openssl enc -aes-256-cbc -salt -in backup.tar.gz -out backup.tar.gz.enc -k password
Replace password with the password with which your backup should be encrypted. The encrypted file will be named backup.tar.gz.enc and OpenSSL would encrypt the file using the AES-256.
To decrypt the file, use:
$ openssl aes-256-cbc -d -in backup.tar.gz.enc -out backup.tar.gz -k password
Replace password with the password used previously.
Encryption using MySQL Enterprise Backup
To use encryption with MySQL Enterprise Backup, generate a key derived from your password that you want to encrypt the backup with:
$ echo -n “Password” | shasum -a 256
Password will be displayed underneath. The command to encrypt your backup should look similar to this:
$ mysqlbackup --backup-image=/images/image.enc --encrypt --key=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX --backup-dir=/var/backups backup-to-image
Replace XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX with your key. You can also supply mysqlbackup with a key file by using the –key-file option:
$ mysqlbackup --backup-image=/images/image.enc --encrypt --key-file=/meb/key --backup-dir=/var/backups backup-to-image
To decrypt a backup, use the –decrypt option:
$ mysqlbackup --backup-image=/images/image.enc --decrypt --key-file=/meb/key --backup-dir=/var/backups extract
Further information on how MySQL Enterprise Backup works can be found on our blog.
Encryption using Percona XtraBackup
MySQL backups can also be encrypted using Percona XtraBackup. In order to do so, specify the encryption algorithm using the –encrypt option and tell the tool whether you want to encrypt the backup with a key or with a keyfile:
--encryptoption specifies the encryption algorithm. Currently, supported algorithms include AES128, AES192 and AES256;
--encrypt-keyoption specifies the encryption key. Do note that the key supplied to this command can be read as it would be displayed as part of the process information (for example when the command ps aux would be run);
--encrypt-key-fileoption specifies the file where the encryption key can be read from.
--decryptoption can be used to decrypt the backup in question.
To encrypt the backups with an encryption key:
$ xtrabackup --backup --target-dir=/backups --encrypt=AES256 --encrypt-key="KEY"
To encrypt the backups with a key file:
$ xtrabackup --backup --target-dir=/backups/ --encrypt=AES256 --encrypt-key-file=/files/keyfile
To decrypt the backups use the –decrypt option:
$ xtrabackup --decrypt=AES256 --encrypt-key="KEY" --target-dir=/backups/ --remove-original
The –remove-original option removes the files once they have been decrypted. To decrypt multiple files simultaneously, include the
Encryption using ClusterControl
You can also encrypt your MySQL backups using ClusterControl – just enable encryption before creating the backup:
MySQL Backup Compression
There also are ways to compress your MySQL backup. Compression can be achieved by using mysqldump or MySQL Enterprise Backup.
If you want to compress your backup using mysqldump:
$ mysqldump -u user -p database | gzip > backup_compressed.sql.gz
In order to decompress your backup, use gunzip:
$ gunzip < backup_compressed.sql.gz | mysql -u root -p database
You can also use zcat:
$ zcat backup_compressed.sql.gz | mysql -u root -p database
database specifies the name of your database. The database should be created before importing. Also keep in mind that you should not be specifying the password in the command itself – only specify the
-p parameter and you will be prompted for the password.
MySQL Backup Compression with MySQL Enterprise Backup
To compress backups using MySQL Enterprise Backup, use:
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf --compress --compress-level=5 --backup-image=compressed.img backup-to-image
--compress-level option specifies the level of compression which can range from 0 to 9. 1 – the fastest compression, 9 – the slowest compression. 0 disables compression.
To restore the backup taken with MySQL Enterprise Backup, specify the –uncompress option:
$ mysqlbackup --defaults-file=/etc/mysql/my.cnf -uroot --backup-image=compressed.img --backup-dir=/tmp/backupdir --datadir=/data/datadir --uncompress copy-back-and-apply-log
Compression using ClusterControl
Compress your MySQL backups using ClusterControl by enabling the “Use Compression” option during the job creation:
MySQL offers a couple of ways to encrypt and compress your backups – encryption can be accomplished by encrypting the file itself, by using an encryption feature provided by MySQL Enterprise Backup, by encrypting your backups using Percona XtraBackup, or ClusterControl and your MySQL compression goals can be accomplished by using mysqldump, MySQL Enterprise Backup, or ClusterControl. Whatever tool you decide to use, know its advantages and disadvantages well and choose the tool most suitable for your use case.