Code Sample: PHP Backup MySQL and files to Dropbox 17

Most web hosts have the ability via their control panel to offer an entire site backup , and this is the minimal functionality you  should consider. Generally there’s a site backup link within the control panel that create a tar ball (.zip file)  of the entire site and its contents. then you can email,or simply download the entire backup from there.  Its a great method to migrate your site from one serer to another (assuming compatible  server software). The big issue with these backup solutions is the most of them are manual. Few offer automated backups and fewer still will automate your backups to any another server or service. Enter using Dropbox as the offline service , coupled with a MySQL file backup script.

Why DropBox.. when you can SFTP?

You can of course backup your site offsite to any other internet connected service.  And this is likely the recommended  for corporate or commercial sites.  Since most commercial sites likely will (or should) have multiple locations simply setup a SFTP server or other secured server and push backups periodically and automatically to that location.

For personal or non-commercial sites, where cost is a concern and the site is relatively small, a solution like Dropbox (with its 2GB storage limit), should suffice. Below are the basics steps.

Step 1. Backup the Database(s) and Site Files to one large zip

The first step is running a script that will backup the database and all the corresponding site files into a large tar or zip file. You could use the  built-in control panel script, but it may be a hassles since you will need to script the entire control panel login script, and then simulate actions to invoke the backup, its doable, but for me its simply easier to launch a customized basic file/MySQL script.

The script I use is a mash-up of various PHP file and database scripts. The first portion is to get a list of all the files under a specific folder and place all those in a zip.

$backupName = "backup_$type-".$time_stamp.'.zip';
$createZip = new createZip;

$backup_results.= "Saving into $backupName ... <br>";

$backup_results.= "Starting at folder : ". $configBackup[0]." ... <br>";

if (isset($configBackup) && is_array($configBackup) && count($configBackup)>0 && $files_bak==true)
{
// Lets backup any files or folders if any
foreach ($configBackup as $dir)
{
$basename = basename($dir);
// dir basename
if (is_file($dir))
{
$backup_results.= "File $basename ...<br>";
$fileContents = file_get_contents($dir);
$createZip->addFile($fileContents,$basename);
$backup_results.="Zipping file $basename ...<br>";
}
else
{
$createZip->addDirectory($basename."/");
$files = directoryToArray($dir,true);
$files = array_reverse($files);
$backup_results.= "Adding folder $basename/ ...<br>";
foreach ($files as $file)
{
$zipPath = explode($dir,$file);
$zipPath = $zipPath[1];

// skip any if required
$skip = false;
foreach ($configSkip as $skipObject)
{
if (strpos($file,$skipObject) === 0)
{
$skip = true;
$backup_results.= "Skipping folder $file ...<br>";
break;
}
}

if ($skip) {
continue;
}

if (is_dir($file))
{

$createZip->addDirectory($basename."/".$zipPath);
}
else
{
$fileContents = file_get_contents($file);
$createZip->addFile($fileContents,$basename."/".$zipPath);

$success_files=true;
}
}
}

}

The next portion is to Backup the MySQL database :

if (isset($configBackupDB) && is_array($configBackupDB) && count($configBackupDB)>0 && $database_bak==true)
{

foreach ($configBackupDB as $db)
{
$backup = new MySQL_Backup();
$backup->server = $db['server'];
$backup->username = $db['username'];
$backup->password = $db['password'];
$backup->database = $db['database'];
$backup->tables = $db['tables'];

$backup->backup_dir = $configBackupDir;

$backup_results.= "Begining Database SQL Dump for database: ". $backup->database." on Server :". $backup->server ."<br>";

$sqldump = $backup->Execute(MSB_STRING,"",false);

$createZip->addFile($sqldump,$db['database'].'-sqldump.sql');
$success_db=true;

$backup_results.= "Adding SQL to Zip File <br>";

}

}

The final steps is to combine the two files into one consolidated zip. Optionally you can choose to password protect or encrypt the zip file at this step for added security.

if ( ($success_db || $success_files ) && is_writable($configBackupDir) )
{
$fileName = $configBackupDir.$backupName;
$fd = fopen ($fileName, "wb");
$out = fwrite ($fd, $createZip -> getZippedfile());
fclose ($fd);
$backup_results.="Success! Finishing writing zip File $fileName to folder:$configBackupDirfs <hr>";
}
else
$backup_results.= "Failed: > Did not write $fileName site files:$success_files db:$success_db Check permissions on $configBackupDir<hr>";

Step 2. Establish a Dropbox account and setup API keys

Before copying files offline of course, you need to establish a Dropbox account. Once the account is created and initialized  be sure to go over to the Dropbox developer area to setup your $app_key and $app_secret hash ,which is what is needed to access  your sorage profile. By default Dropbox will put files in the Apps sub-folder of your dropbox account.

Step 3. Automatically backup the site to dropbox.

Once all that is setup, I make use of this DropPHP – A simple Dropbox client.  DropBox -API friendly script written by Fabian Schlieper. Once I include that connecting library, its just a matter of invoking the upload , by running  the following code:

require_once("DropboxClient.php");
// App key information stored in config.php file
// you have to create an app at https://www.dropbox.com/developers/apps and enter details below:
$dropbox = new DropboxClient(array(
'app_key' => $dbox_app_key,
'app_secret' => $dbox_app_secret,
'app_full_access' => false,
),'en');

function DropBox_Upload($files)
{
global $dropbox;
handle_dropbox_auth($dropbox); // see below

foreach ($files as $key => $this_file)
{
//echo "Trying to upload ".$this_file ;
if ( file_exists($this_file) )
{
try {
$upload_name =$this_file;
$result="<pre>";
$result.= "\r\n\r\n<b>Uploading $upload_name:</b>\r\n";
$meta = $dropbox->UploadFile($this_file); //upload it!
$result.=print_r($meta,true);
$result.= "\r\n done!";
$result.="</pre>";

$result.= '<span style="color: green">File successfully uploaded to your Dropbox!</span>';
} catch(Exception $e) {
$result.='<span style="color: red">Error: ' . htmlspecialchars($e->getMessage()) . '</span>';
}
}
} //end foreach

//write a simple text file with latest status
$backup_folder= dirname(__FILE__).'/backup/'."dropbox_results.txt"; ;
$myFile = $backup_folder;
$fh = fopen($myFile, 'w') or die("can't open file");
fwrite($fh, $result);
fclose($fh);

return $result;

} //end function

// ================================================================================
// store_token, load_token, delete_token are SAMPLE functions! please replace with your own!
function store_token($token, $name)
{
file_put_contents("tokens/$name.token", serialize($token));
}

function load_token($name)
{
if(!file_exists("tokens/$name.token")) return null;
return @unserialize(@file_get_contents("tokens/$name.token"));
}

function delete_token($name)
{
@unlink("tokens/$name.token");
}
// ================================================================================

function handle_dropbox_auth($dropbox)
{
// first try to load existing access token
$access_token = load_token("access");
if(!empty($access_token)) {
$dropbox->SetAccessToken($access_token);
}
elseif(!empty($_GET['auth_callback'])) // are we coming from dropbox's auth page?
{
// then load our previosly created request token
$request_token = load_token($_GET['oauth_token']);
if(empty($request_token)) die('Request token not found!');

// get & store access token, the request token is not needed anymore
$access_token = $dropbox->GetAccessToken($request_token);
store_token($access_token, "access");
delete_token($_GET['oauth_token']);
}

// checks if access token is required
if(!$dropbox->IsAuthorized())
{
// redirect user to dropbox auth page
$return_url = "http://".$_SERVER['HTTP_HOST'].$_SERVER['SCRIPT_NAME']."?auth_callback=1";
$auth_url = $dropbox->BuildAuthorizeUrl($return_url);
$request_token = $dropbox->GetRequestToken();
store_token($request_token, $request_token['t']);
die("Authentication required. <a href='$auth_url'>Click here.</a>");
}
}

The other advantage of this approach is I can capture the returned values from the Dropbox API and have it alert me via an email if something fails. Below is an example of a successful transfer PHP  JSON object.

(
[revision] => 4
[rev] => 40d2769c1
[thumb_exists] =>
[bytes] => 23174756
[modified] => Thu, 17 Jan 2013 06:25:44 +0000
[client_mtime] => Thu, 17 Jan 2013 06:25:44 +0000
[path] => /bckup_full-08-Jan-2013.zip
[is_dir] =>
[icon] => page_white_compressed
[root] => app_folder
[mime_type] => application/zip
[size] => 22.1 MB
)

Automate it! Run it in a Cron session:

Once all the above steps are setup , tested and confirmed working, simply create a simple cron script that launches the backup php file via a [wiki]wget [/wiki]command or other shell command and let it do its magic.

That’s it hope that provides some inspiration to backup your site and work. If your running WordPress you can get existing plugins that do all these steps for you look for BackWPup for example. Also while I mentioned Dropbox you can also push your backups to Google Drive,  Microsoft cloud storage Amazon EC2 or a any host of other free or commercial cloud storage solutions.

Found this helpful? consider a tip for a tip…