MySQL Encrypted Database Backup for Mysqldump and Xtrabackup

What does Database Encryption and Decryption mean?

Database encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.

Database decryption is converting the meaningless cipher text into the original information using keys generated by the encryption algorithms.

Database encryption can be provided at the file or column level.


Here am going to explain how to encrypt your mysql databases with mysqldump and xtrabackup.first we will see for mysqldump

MySQLdump Encryption & Decryption:

Mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database.

To encrypt your database here I have used ccrypt.ccrypt is a utility for the secure encryption and decryption of files and streams.

I have used Fedora 20 server to do this process.

First install ccrypt

#yum install ccrypt

am already have an mysql server in this machine so i will just show you how to take encrypted backup with ccrypt.

Create a hidden keyfile.

#vi /etc/key/.backupkey

in this file add any content like below,

ex : Ukl8GiJ4Q#uy@iP

Now give permission for this file,

#chmod 600 /etc/key/.backupkey

Now we can encrypt our backup,

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey > backup_c.sql

This backup.sql will be now an encrypted backup when you open it, it will be a non readable format.

Decryption:

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Now you can see the real content in this file.
 

Compressed Mysqldump Encrypted backup:

Encryption:

#mysqldump -u root -ppa$$123 --databases country world | ccrypt -k /etc/key/.backupkey | gzip -c > backup_c.sql.gz

Decryption:

#gunzip  backup_c.sql.gz > backup_c.sql

#cat backup_c.sql | ccrypt -d -k /etc/key/.backupkey > backup_d.sql 

Xtrabackup Encryption & Decryption:
 
Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

For xtrabackup you need to install latest Percona Xtrabackup(Version should be more than 2.1.4) and openssl for encryption.

Xtrabackup installation steps in this below link,
http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

Now we can encrypt our backup with openssl for Xtrabackup.

Before doing that we need to generate openssl key. 

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

 

with the above key we are going to encrypt with Xtrabackup

Encryption: 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup
 

 after executed the above command you can see some line like below,

[01] Encrypting ./xxxx/xxxx.ibd to /backup/2014-12-27_04-49-28/xxxx/yyyy.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./yyyy/aaaa.ibd to /backup/2014-12-27_04-49-28/dddd/rrrr.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./rrr/ssss.ibd to /backup/2014-12-27_04-49-28/yysss/ddasffd.ibd.xbcrypt
[01]        ...done
[01] Encrypting ./gggds/ssssss.ibd to /backup/2014-12-27_04-49-28/dsfsd/fdsfds.ibd.xbcrypt
.
.
.

and at the end,

innobackupex: Backup created in directory '/backup/2014-12-27_04-49-28'
141227 04:51:51  innobackupex: Connection to database server closed
141227 04:51:51  innobackupex: completed OK!
   Now it has taken an encrypted backup.

Xtrabackup encryption with keyfile,

#openssl enc -aes-256-cbc -pass pass:Prabhu123 -P -md sha1

Replace your password in the bold place,when you executed the above command  you will get the output, 

salt=76AC44528ED1441B
key=C6CA8F2AC3A824653438F7B4E2493892BCC01238E1FF4B5651588AFB6D4DA111
iv =FEC2DE284641AC8A0636D07BEEC5A514

Now copy the iv result in a txt file,

#cat /etc/key/xtrabackupkey.txt

FEC2DE284641AC8A0636D07BEEC5A514

Now do the xtrabackup with key file, 

#innobackupex --user=root --password=pa$$123 --export  --encrypt=AES256 --encrypt-key-file=/etc/key/xtrabackupkey.txt  /backup

Decryption:

For decryption required xtrabackup 2.1.4 version.

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

It will extract the xtrabackup encrypted folder to decrypted folder in same location.

Compressed Xtrabackup Encrypted backup:

Encryption:

# innobackupex --user=root --password=pa$$123 --export --compress --encrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup

Decryption:

First we need to decompress the file, see how to decompress in the below link,

http://knowmysql.blogspot.in/2013/08/percona-xtrabackup-tool.html

then decrypt by the below command with same encrypt key,

#innobackupex  --decrypt=AES256 --encrypt-key="FEC2DE284641AC8A0636D07BEEC5A514"  /backup/2014-12-27_05-49-08/

Now all the databases are decrypted in the same directory.