MySQL backup to Rackspace Cloud files, quick & dirty

After having deep thought about security, bad hardware and shit I decided to make some database backup for some of my clients. They wouldn’t provide any spare backup space so i decided to use my account on rackspace cloud. The file space was cheap enough so i do not charge my client for it (even if I would i could not). I wont write about how to restore the database. In case a restore is needed, the situation will be too complex to restore with a click of button. It took about 1 hour to complete the task. The plan is to use php-cli, crontab and cloud files.

Activate your rackspace cloud files account

As I remember, I had to click some button to activate my files account on rscf. So you go to your admin on rscf and click some button, then he shows you your api key. The key will be needed to send files to your rscf account.

Get the php-cloudfiles API

The url is this. So you download it. Later on i will show and maybe describe my directory structure.

Directory structure

I have put all my files in to /var/www/cron, so I know where to find them. I have a standart ubuntu 10.04 installation. I usually install the mini-cd version and then build it up to my needs. So you should have at least mysql, php-cli, php-curl to make it work.

I have put the cloudfiles api in to the folder lib/cloudfiles. Needed is also the folder share which has some files in it. So 3 php files and a share folder with some shit in it.

I have created a temp folder with write access to the user which hast the cron job. The files created for the backup will be saved there.

At least the db-backup.php, which will be later on executed with the cron.

The php code

//db-backup.php
try {
require_once('lib/cloudfiles/cloudfiles.php');
$tempFolder = '/var/www/cron/temp/'; // the temp folder
$dbName = 'shity_unstable_db'; // the database name which we backup
$username = 'username'; // username for the database
$password = 'password'; // password for the database
$rscfUsername = 'username-for-rscf'; // the username for your rackspace account
$rscfApiKey = 'verylongmeaninglesskeycodeapishit'; // the api key for your account
 
// this is where we write the db to a file using mysqldump
exec("/usr/bin/mysqldump -u{$username} -p{$password} {$dbName} &gt; {$tempFolder}{$dbName}.sql");
 
// now we gzip it so we pay less € in my case.
exec("/bin/gzip {$tempFolder}{$dbName}.sql");
 
// check if the file is created
if(file_exists("{$tempFolder}{$dbName}.sql.gz")) {
 
$auth = new CF_Authentication($rscfUsername, $rscfApiKey);
$auth->ssl_use_cabundle();
$auth->authenticate();
$conn = new CF_Connection($auth);
$conn->ssl_use_cabundle();
 
// I allready created a container with this name otherwise user create_container
$container = $conn->get_container('db-backup');
 
// make a unique name with date in it so you know when it was created
$objectName = $dbName.'_'.date('Y-m-d_H:i').'.sql.gz';
$db = $container->create_object($objectName);
$db->load_from_filename("{$tempFolder}{$dbName}.sql.gz"); // send to rackspace
 
unlink("{$tempFolder}{$dbName}.sql.gz"); // delete the file, not needed anymore
 
} else {
// if no file send an email so we know something is wrong
mail('badass@admin.si', 'No file', 'No file buddy, you suck!!!');
}
 
} catch (Exception $e) {
// in case of an exception, might be rackspace is gone wild.
mail('badass@admin.si', 'Exception', print_r($e, true));
}

I thing the file explains it self.

The cron job

05 */6 * * * /usr/bin/php /var/www/cron/db-backup.php > /dev/null

So this is the cronjob, It will make backups every 6 hours. Change it as you wish.

FINAL

We are done. Now we have an unreliable backup system. In case I have to restore the db from the backup i will use

mysql -uusername -ppassword db_name > sql.sql

and remove all the data for the last N hours (maybe not). I dont have a better plan yet (maybe I have).

Here are some logos and links.