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:

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:

  • The --encrypt option specifies the encryption algorithm. Currently, supported algorithms include AES128, AES192 and AES256;
  • The --encrypt-key option 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);
  • The --encrypt-key-file option specifies the file where the encryption key can be read from.
  • The --decrypt option 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 --parallel option.

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

The 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

The --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:

Summary

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.

Contributor
Comments to: MySQL Backup Encryption & Compression

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.