This morning i had to figure out a way to add encryption to our MySQL database backups so they can be safely stored on the server.
We have a backup in place at the minute which uses mysqldump to backup the database and save the resultant SQL file. Now that worked really nicely but it wasn’t encrypted on the server so if some bad hacker type found it then they would have access to lots of sensitive information. So i had to add some encryption to the SQL dump.
It turns out that OpenSSL was the answer and much to my delight this came installed on our server as standard. From there it was a matter of using the existing SQL dump file to create the encrypted .enc file (which can be viewed in notepad++ among others).
There are many different types of ciphers that can be used with OpenSSL, I chose the Advanced Encryption Standard.
The script also had to remove any backups that were over a week old, i achieved this by using PHPs trusty scandir()function to read the dump folder and then filemtime() to check the modification date of each backup, removing it if the date was over 7 days old.
Check out my solution below, I think it’s pretty neat and succinct.
//configuration
$secret = 'a_password_of_your_choice';
$folder = getenv("DOCUMENT_ROOT").'/db_dumps/'; // change the folder name to suit
$d= date('dmy');
$halt = '5';
//Your database details
$dbusername = 'your_db_username';
$dbpassword = 'your_db_password';
$dbhost = 'your_db_host';
//the names of the files
$sql= $folder ."db_backup".$d.".sql";
$encrypted= $folder ."db_backup".$d.".enc";
//Dump the SQL data
$command = "mysqldump -u $dbusername -p $dbpassword --host= $dbhost --opt -A > $sql";
$command .= " ; sleep $halt ; ";
//Encrypt the file with OpenSSL and add a password to it
$command .= "openssl enc -aes-256-cbc -salt -in $sql -out $encrypted -pass pass:$secret";
$command .= " ; sleep $halt ; ";
$command .= "sleep $halt ; ";
//remove any dumps older than 7 days in the folder
$seven_days_ago = date("Ymd",strtotime("-7 day"));
$files = scandir($folder);
foreach($files as $f){
$modified = date("Ymd", filemtime($f));
if($modified < $seven_days_ago){$command .= 'rm -f ' . "$folder$f";}
}
//remove the sql dump file and just leave the encrypted file
$command .= "rm -f $sql";
//execute it
$output = shell_exec($command);
echo"get the file here ";
So You’ve encrypted it… can you decrypt it?
Obviously encrypting a backup is great and means that any sensitive data is impenetrable by evil wrong do-ers but it’s not much use if you need to restore the backup. So lets decrypt it and save it as a Gzip file.
The first thing to do is pass the secret password to the script, you’d do this by passing it as a variable in the URL when you call the page(http://www.mysite.com/decrypt.php?date=01072010&secret=a_password_of_your_choice) The date of the dump you want to access also gets passed this way.
//folder location change to suit
$folder= getenv("DOCUMENT_ROOT").'/db_dumps/';
$halt='5';
//retrieve the data from the URL
$d= $_GET['date '];
$p= $_GET['secret'];
//file locations and names
$encrypted= $folder."db_backup".$d.".enc";
$sql= $folder."db_backup_u".$d.".sql";
//decrypt the .enc file into a readable sql file
$command="openssl aes-256-cbc -d -in $encrypted-out $sql -k $p";
//Gzip the decrypted sql file
$command .= " ;gzip -f -q $sql";
$command .= " ; sleep $halt; ";
//execute it
$output = shell_exec($command);
echo"get the file here ";
What Now
Set the encrypt.php script to run as a cron.
Send an email when each cron is run.
check each file exists before echoing the link to the encrypted and decrypted file
