How To Use PHP And The Dropbox API To Automatically Back Up MySQL Databases To Dropbox

A Simple Drop Box Solution for Backing Up MySQL Databases

UPDATE!!! As of June 2017 DropBox has deprecated the API used by this tutorial for uploading files to DropBox. You can view our updated tutorial here: PHP – Automatically Backup MySQL Databases to DropBox.

Historically we would back up some our key MySQL DB files direct onto the FTP server above the public folder in a secure backup section. The problem with this method is that it will eat up your server storage fast if you have an app or site you do daily or even hourly backups on, and the DB has significant size to it.

One option is to store your backups off-server on a third-party cloud files container. We’ve used Rackspace Cloud Files before for some use cases, but for this particular instance we wanted some way to back up a MySQL database to Dropbox using PHP only. Please note that much of these methods are a combination of code examples posted on http://axellence.lv/2014/07/zip-files-php-upload-dropbox/ and portions from the deprecated non-API approach documented for DropboxUploader.php found here: http://ericsilva.org/2012/07/05/backup-mysql-database-to-dropbox/ so I give credit where it’s due to these helpful posts.

However, both fell short for our particular use case, and thus we have a combo of what worked best to make a short, simple and workable Dropbox API solution for backing up our MySQL databases.

Backing Up MySQL To Dropbox With PHP

If you are in a shared hosting environment for a client and don’t have SSH access, you might need to run a PHP only solution. You also might just prefer PHP (I do as it is the language I use the most for web dev) and want to run things in a code you know. So how do we do it and what do we need?

You Will Need To Do The Following:

Download the Dropbox PHP SDK found here. Put it in the same directory as your PHP script we are going to make below and name the SDK folder “dropbox-sdk” without the versioning on it from the download.

Under your own Dropbox Account (assuming already that you have one) you need to create an App. This is easy to do in Dropbox, login to your account and go here: https://www.dropbox.com/developers/apps

You will see the app panel; click on ‘create a new app’ like below:

Next you need to select the kind of app as “Dropbox API App” and for security I would put “Yes” to limit the app as having access only to our backup folder. Then name your app and move on to the next step.

Finally, go to the actual App settings and get your API Key, API Secret and then also get a auth token generated for your account where you plan to upload the files to. Getting the auth token from your panel allows us to avoid running the authentication through the code and greatly simplifies the process. We are just hard coding an auth token into our app script for your user account.

Now to the Code Needed for the Backup

Your next step is to prepare 2 files for your server: config.json and our script below which we can call dropbox_sqlbackup.php for now. You will also need to copy the Dropbox PHP SDK into the folder on your server (not your public folder! Keep it secure and above that level!) so that you have in your backup script folder (call it whatever you want) the config file, the PHP SDK library and our PHP backup script below. That’s all you’ll need to make it work.

Config.json

All you need to put in here is your key and secret:

{
"key": "YOUR_KEY_FROM_DROPBOX_APP_PANEL",
"secret": "YOUR_SECRET_FROM_DROPBOX_APP_PANEL"
}

Save it as config.json and move on to the PHP file.

dropbox_sqlbackup.php

Here we start by calling the SDK library and by inputting our access token that we got from the App panel;

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

//our access token from the Dropbox App Panel
$accessToken = 'YOURACCESS TOKEN';

Next, we are concerned with running the actual MySQL dump to prep the file for being moved into Dropbox. We included some hashed-out error notices you can turn on during testing to see how things are working:

//run the MySQL dump and zip;

$errors = "";
//echo("Starting CRM DB Backup.\n");

// location of your temp directory
//the below is an example, change it to your correct server temp dir;

$tmpDir = "/home/php/temp_dir/";
// username for MySQL
$user = "YOURUSER";
// password for MySQL
$password = "YOURPASSWORD";
// database name to backup
$dbName = "YOURDB";
// hostname or IP where database resides
$dbHost = "999.999.999.999";
// the zip file emailed to you will have this prefixed
$prefix = "crm_db_";

// Create the database backup file
$sqlFile = $tmpDir.$prefix.date('Y_m_d_h:i:s').".sql";
$backupFilename = $prefix.date('Y_m_d_h:i:s').".tgz";
$backupFile = $tmpDir.$backupFilename;

$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --> ".$sqlFile;
//echo $createBackup;
//$createZip = "tar cvzf $backupFile $sqlFile";
$createZip = "tar -cvzPf $backupFile $sqlFile";
//echo $createZip;

try {
	//echo("Creating Backup: " . $createBackup . "\n");
	exec($createBackup);
	//echo("Successfully Created Backup.\n");
	
	//echo("Creating ZIP: " . $createZip . "\n");
	exec($createZip);
	//echo("Successfully Created ZIP.\n");
} catch (Exception $e) {
    $errors .= "Failed to create CRM DB Backup or ZIP: " . $e->getMessage() . "\n";
}

This final section is concerned with actually accessing the DropBOx App to push our MySQL file into our designated space in Dropbox. Again, there are some hashed-out error messages you can activate when testing, to see how things are working for your own install:

//now run the DBox app info and set the client;

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


//now the main handling of the zipped file upload;

 
//echo("Uploading $backupFilename to Dropbox\n");
try {
	$f = fopen($backupFile, "rb");
	$result = $dbxClient->uploadFile('/CRM_Backup/'.$backupFilename, dbx\WriteMode::force(), $f);
	fclose($f);
} catch (Exception $e) {
    $errors .= "Failed to upload CRM DB Backup to Dropbox: " . $e->getMessage() . "\n";
}

// Delete the temporary files
try {
	unlink($sqlFile);
	unlink($backupFile);
} catch (Exception $e) {
    $errors .= "Failed to unlink CRM DB Backup temporary file: " . $e->getMessage() . "\n";
}



//for testing or if you really want alerts all the time, enable the mail function

if (strlen($errors) > 0) {	
	die('Failed: ' . $errors);

	//mail("[email protected]", "ALERT! CRM DB Backup Failed!", $errors);
}
else {
	//mail("[email protected]", "CRM DB Backup succeeded!", "Success");
}

//for testing, will confirm backup
//echo("Finished CRM DB Backup.\n");

The Whole Dropbox MySQL Backup Code File Below

So that’s about it; I would then run this on a cron job from your server to run daily or weekly, however often you want to back up your entire MySQL database. However you interface with cron through your web host, I would encourage you to set up e-mail notifications as the below code will give you basic error messages if something goes wrong in the connection, upload, etc… and it will also give you a positive confirmation. Have fun and hopefully this saves someone else the half-day I spent hunting down and hacking away at the various examples out there to get the working script below! If you have any questions about this code, or could use our teams help in setting this up for your own database just let us know by contacting us today.

<?php # Include the Dropbox SDK libraries require_once __DIR__."/dropbox-sdk/lib/Dropbox/autoload.php"; use \Dropbox as dbx; //our access token from the Dropbox App Panel $accessToken = 'YOURACCESS TOKEN'; //run the MySQL dump and zip; $errors = ""; //echo("Starting CRM DB Backup.\n"); // location of your temp directory //the below is an example, change it to your correct server temp dir; $tmpDir = "/home/php/temp_dir/"; // username for MySQL $user = "YOURUSER"; // password for MySQL $password = "YOURPASSWORD"; // database name to backup $dbName = "YOURDB"; // hostname or IP where database resides $dbHost = "999.999.999.999"; // the zip file emailed to you will have this prefixed $prefix = "crm_db_"; // Create the database backup file $sqlFile = $tmpDir.$prefix.date('Y_m_d_h:i:s').".sql"; $backupFilename = $prefix.date('Y_m_d_h:i:s').".tgz"; $backupFile = $tmpDir.$backupFilename; $createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." --> ".$sqlFile;
//echo $createBackup;
//$createZip = "tar cvzf $backupFile $sqlFile";
$createZip = "tar -cvzPf $backupFile $sqlFile";
//echo $createZip;

try {
	//echo("Creating Backup: " . $createBackup . "\n");
	exec($createBackup);
	//echo("Successfully Created Backup.\n");
	
	//echo("Creating ZIP: " . $createZip . "\n");
	exec($createZip);
	//echo("Successfully Created ZIP.\n");
} catch (Exception $e) {
    $errors .= "Failed to create CRM DB Backup or ZIP: " . $e->getMessage() . "\n";
}

//now run the DBox app info and set the client;

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


//now the main handling of the zipped file upload;

 
//echo("Uploading $backupFilename to Dropbox\n");
try {
	$f = fopen($backupFile, "rb");
	$result = $dbxClient->uploadFile('/CRM_Backup/'.$backupFilename, dbx\WriteMode::force(), $f);
	fclose($f);
} catch (Exception $e) {
    $errors .= "Failed to upload CRM DB Backup to Dropbox: " . $e->getMessage() . "\n";
}

// Delete the temporary files
try {
	unlink($sqlFile);
	unlink($backupFile);
} catch (Exception $e) {
    $errors .= "Failed to unlink CRM DB Backup temporary file: " . $e->getMessage() . "\n";
}



//for testing or if you really want alerts all the time, enable the mail function

if (strlen($errors) > 0) {	
	die('Failed: ' . $errors);

	//mail("[email protected]", "ALERT! CRM DB Backup Failed!", $errors);
}
else {
	//mail("[email protected]", "CRM DB Backup succeeded!", "Success");
}

//for testing, will confirm backup
//echo("Finished CRM DB Backup.\n");