Important note: Dropbox API v1 is deprecated and will be disabled on June 28, 2017.
I’ll update this post as soon as I have time.

I needed a quick and dirty cheap backup solution for ever growing directory of MySQL backups.

Some were already backed up on Dropbox. So, I decided to throw together a PHP script which could upload those sql files without having to rely on Dropbox client. Then use cron to launch the script after daily MySQL dump. Few web searches later I found DropboxUploader on GitHub but I didn’t like its approach. Official API seems more elegant as it uses OAuth 2 authentication.

Requirements

• At least PHP 5.3 (I’m currently on 5.4.22);
• php_zip extension for easy .zip file handling;
• Dropbox PHP SDK (173kb file @v1.1.3);
• Dropbox API keys. To get them, register an app on Dropbox App Console;

First of all, take a look at introduction on using Dropbox API with PHP, then download Dropbox SDK for PHP.

Register an app on Dropbox App Console to get API key and secret. Paste values into config.json.

{
"key": "INSERT_APP_KEY_HERE",
"secret": "INSERT_SECRET_HERE"
}

Include Dropbox API autolaoder and define dbx alias for \Dropbox namespace:

# Include the Dropbox SDK libraries
require_once __DIR__."/dropbox-sdk/lib/Dropbox/autoload.php";
use \Dropbox as dbx;

I didn’t bother with the authentication part because I will use the “app” only for this script. I just generated access token in App Console and hardcoded it in my script.

$accessToken = 'NotARealTokenXQAAAAAAAAABmKadnX7wFdbUyIllvKphLHSXPqBNotARealToken';

createZip function

To make things easier, I wrapped the compression code in a function.

/**
 * Add array of $files to $output_file archive and deletes the
 * files after successful archiving
 * @param $files array Array of files to zip
 * @param $output_file string Archive path
 * @return string|boolean Path of zip file or FALSE if anything failed
 */
function createZip($files, $output_file){

	$zip = new ZipArchive();

	if ($zip->open($output_file, ZipArchive::CREATE)!==TRUE) {
		exit("cannot open '$output_file'".PHP_EOL);
	}
	$to_delete = array();
	foreach($files as $file){
		if(!file_exists($file) || !is_readable($file)){
			continue;
		}
		if($zip->addFile($file, basename($file))){
			$to_delete[] = $file;
		}
	}
	$status = $zip->status;
	$success = $zip->close();

	// Delete files after ZipArchive::close() because ZipArchive locks files
	if($success)
	foreach($to_delete as $file){
		@unlink($file);
	}

	return $status == 0 && $success ? $output_file : FALSE;
}

Load Dropbox client and set access token

$appInfo = dbx\AppInfo::loadFromJsonFile(__DIR__."/config.json");
$dbxClient = new dbx\Client($accessToken, "MySQLbumps-BACKUP/1.0");

And the main part

Zipping and uploading

// define some required paths
define('TEMP_DIR', sys_get_temp_dir());
// Local backup location. Change path as needed
define('BACKUPS_DIR', '/var/backup/mysql/');

// Find latest file and upload so that there is at least one fresh backup
$latest = glob(BACKUPS_DIR.'*_'.date('Y-m-d').'*.sql');
$latest_file = reset($latest);

if($latest_file && file_exists($latest_file)){
	echo "Found latest backup: '".basename($latest_file)."'".PHP_EOL;
	$temp_zip = TEMP_DIR.'latest_backup.zip';
	// delete old zip file if it exists otherwise the file will be added to that zip
	if(file_exists($temp_zip)){
		@unlink($temp_zip);
	}
	$upload_file = createZip(array($latest_file), $temp_zip);
	$f = fopen($temp_zip, "rb");
	$result = $dbxClient->uploadFile('/MySQL backups/latest_backup.zip', dbx\WriteMode::force(), $f);
	fclose($f);
	if($result)
		echo "Uploaded ".basename($temp_zip).PHP_EOL;
}

// Check if there are any files from past month
$date_part = date('Y-m', strtotime('last month'));
$search = BACKUPS_DIR.'*_'.$date_part.'*.sql';
$files = glob($search);
if(empty($files)){
	echo "Nothing to do: no files match '$search'".PHP_EOL;
	exit(0);
}

$zip_file = 'Backup_'.$date_part.'.zip';
echo("Creating $zip_file file for MySQL dumps from last month".PHP_EOL);
$upload_file = createZip($files, BACKUPS_DIR.$zip_file);
if(!$upload_file || !file_exists($upload_file)){
	$msg = 'Could not create zip archive';
	error_log($msg);
	exit($msg);
}

echo("Uploading $zip_file to Dropbox\n");
$f = fopen($upload_file, "rb");
$result = $dbxClient->uploadFile('/MySQL backups/'.$zip_file, dbx\WriteMode::force(), $f);
fclose($f);

I have set the script to run right after nightly MySQL dump. Now I can sleep in peace knowing that I’ll have all backups from previous months and one from last night if anything goes wrong.

Have any suggestions? Add them in comments sections.