Encrypting MySQL dump

For one of my MySQL databases, I needed to encrypt the MySQL dump files that I used to take for backup of that database. The reason was that the database contained some sensitive information and I did not want to give the MySQL dump files in text format to the backup team.

After some search, I found a nice utility named ccrypt (http://ccrypt.sourceforge.net). This utility can be used to encrypt and decrypt files streams. To install ccrypt you can download it from ccrypt site. As I am using Ubuntu (12.04), so in this example I will use apt-get to install it.

There are many options of ccrypt, you can read the man page of ccrypt for details.

To create our encrypted backup, first I will create a keyfile, which will contain the key phrase(or key phrase). Encryption and decryption depends on this keyword. Longer and stronger keywords provide better security than short and simple ones. By default, the user who runs ccrypt is prompted to enter the keyword. I will use the -k, --keyfile file option of ccrypt, which instructs ccrypt to read the keyword as first line from the specified file.

Create this keyfile as a hidden file and change the permission of the file, so that only the owner of this file can read and write it.

 
Type your key phrase in this file.

Save this file and change its permissions to 600.

Now I will edit the MySQL dump script to enable encryption using ccrypt.

BACKUP_DATE=`date +%k`_`date +%M`_`date +%F`
mysqldump cloud -u pranab -pCl0uD123 | ccrypt -k /root/.backup_keyfile | bzip2 -c > /home/backupadmin/MySQLBackup/bkp_mysql_$BACKUP_DATE.sql.bz2

Now lets examine the backup created using this script.

Decompress the file
#bzip2 -d bkp_mysql_10_35_2013-03-11.sql.bz2
Examine the content
#pico bkp_mysql_10_35_2013-03-11.sql

Its not readable. That what we wanted .

Now encryption part is over, we have to decrypt the backup so that it can be of some use.

# cat bkp_mysql_10_35_2013-03-11.sql | ccrypt -d -k .backup_keyfile > bkp_mysql.sql

The –d option tells ccrypt to decrypt a file or stream.

Lets examine the decrypted file, yes we can now read the content of the decrypted file.

That’s it, simple enough .