Jul 05 2012

Backup MySQL Database to Dropbox

I use MySQL on a regular basis for development, but also use it for production websites as well. Most website hosts provide access to the cron utility for scheduling jobs. I came across a post the other day that showed how to backup MySQL using the ‘mysqldump’ utility and a PHP script. That solution used the Mail PEAR package to e-mail the database backup file to a specified e-mail address. I thought it would be better to store the backup in my Dropbox account instead of using e-mail.

A quick query on Google pointed me to the DropboxUploader utility on Github. I downloaded the utility and with some minor modifications, was able to create the following PHP script that creates a backup SQL file from the specified database, compressed it into a GZipped tarball, and copies it to a specified folder in my Dropbox account.

 ".$sqlFile;
//echo $createBackup;
$createZip = "tar cvzf $backupFile $sqlFile";
//echo $createZip;
exec($createBackup);
exec($createZip);

try {
    // Upload database backup to Dropbox
    $uploader = new DropboxUploader($dropbox_user, $dropbox_password);
    $uploader->upload($backupFile, $dropbox_dest,  $backupFilename);
} catch(Exception $e) {
    die($e->getMessage());
}

// Delete the temporary files
unlink($sqlFile);
unlink($backupFile);

?>

You can download the required DropboxUploader.php here.

Update (Aug 23, 2014): Several updates have been made to the original DropboxUploader script. I have forked the repo and been making changes based on community feedback. While pull requests have been issued against the original, you can always get the most current working version from my fork here.

To finish off the exercise, you can FTP the DropboxUploader.php and your new PHP script to your webserver, and then create a cron job to invoke the PHP script on a schedule of your preference (e.g., daily or weekly).

Permanent link to this article: http://ericsilva.org/2012/07/05/backup-mysql-database-to-dropbox/

76 comments

4 pings

Skip to comment form

    • Acland on Wednesday, August 20, 2014 at 10:41 pm

    Let me try to paste my solution in parts – keep in mind, this sounds complciated but only took me around 10 minutes max to implement (after I spent hours and hours trying to work it out):

    PART 1:

    IN short, we’re going to create a dropbox app and use the sdk and generate an access token then we’ll use this in Eric’s existing script. We won’t need dropboxUploader.php anymore.

    First of all, you need to go to https://www.dropbox.com/developers/apps/create and create a Dropbox API APP then chose -> Files and Data Stores – > NO ->All File Types – > NAME (give it a simple name like xyz-test) We’re going to need this NAME later on.

    Create the APP then scroll down a bit to OAuth 2 and GENERATE ACCESS TOKEN – copy that token and paste it somewhere so we can grab it later. wouldn’t hurt to paste the name of your app too.

    Ok, we’re just about there. Now we need to download the dropbox sdk library and copy part of it into a folder on your server. Go to https://www.dropbox.com/developers/core/sdks/php download the library. Unzip it – all we want is the Dropbox folder(and contents) that’s found in the lib folder.

    I created a new directory (same directory level as Eric’s Script) on my server called ‘dropbox-sdk’ and placed the ‘Dropbox’ folder (and contents of course) from the sdk download into it.

    So far so good. You now have a new directory called drobox-sdk with a folder and contents called ‘Dropbox’ inside.

    • Acland on Wednesday, August 20, 2014 at 10:44 pm

    Part 2:
    So far so good. You now have a new directory called drobox-sdk with a folder and contents called ‘Dropbox’ inside.

    Now, let’s change Eric’s sql backup script to work with our new app and files.

    Open Eric’s file and escape out line 30 – like this: //require(‘DropboxUploader.php’);
    we won’t be using the uploader but it’s handy to have it in case there’s a fix later on.

    Now add these two lines:
    require_once “dropbox-sdk/Dropbox/autoload.php”;
    use \Dropbox as dbx;

    This obviously links to the sdk file we downloaded and will use to get the rest of the script working.

    Next, we will keep Eric’s script to generate all the temp files, backup files etc. and add new code to upload to dropbox.

    Let’s escape out everthing from line 64: /* try( //Upload database … ALL THE WAY TO LINE 71 … die($e->getMessage());} */
    we’re not using dropboxUploader so commenting it out is a good idea and besides, if there’s a fix we can simply add it back in if necessary but uncommenting it.

    • Acland on Wednesday, August 20, 2014 at 10:46 pm

    I can’t upload part 3 sorry – there’s code in there that mod-security picks up so please download my solution posted in the rtf file earlier. 🙁

    • Eric Silva on Thursday, August 21, 2014 at 9:18 am
      Author

    Alternatively, I found a way to modify the DropboxUploader.php script to get it to work. You can download an updated script here: https://raw.githubusercontent.com/ericjsilva/DropboxUploader/master/DropboxUploader.php

    • acland on Thursday, August 21, 2014 at 4:18 pm

    Very cool – I saw that solution but couldn’t get it to work – so much simpler than all the steps necessary in my solution. Thanks @Eric Silva

    • acland on Thursday, August 21, 2014 at 5:06 pm

    Thanks @EricSilva for the new dropboxUploader.php script – it works a treat. I did find that the creation dates on my backed up files were all the same so for those of you interested I added this code to set the creation dates to the backup files:

    on line 124 (right under this line: ‘ $subjectUid = $this->’ etc) —– ADD this line:
    $mtime = time();

    then on line 132 (underneath this line: ‘_subject_uid’ => $subjectUid,) ADD this line:
    ‘mtime’ => $mtime,

    make sure that the ‘ ‘ marks I’ve posted above are apostrophes and not speech marks – especially if you copy and paste.

    That should get the creation dates working.

    • Eric Silva on Saturday, August 23, 2014 at 8:54 am
      Author

    @acland thanks. I tested and committed your change to the master file. You can find the updated version here: https://github.com/ericjsilva/DropboxUploader. #opensourceFTW!

    • kavlito on Sunday, November 9, 2014 at 1:28 pm

    Currently getting this error and backups fail:

    Cannot extract ‘t’! (form action is ‘https://dl-web.dropbox.com/upload’)

    • kavlito on Monday, November 10, 2014 at 9:10 am

    Script is not deleting temp files. Directory /.cagefs/tmp was full of working files taking all my sites over their disk limit.

    • Acland on Tuesday, November 11, 2014 at 5:10 am

    @kavlito regarding the ‘cannot extract t’ error – I had the same problem and then realised that dropbox had recently forced me to change my password (after their hack) and I forgot to update the password in the sqltodropbox.php file. Once I updated the password, the script works just fine and I’m back in business.

    • kavlito on Tuesday, November 11, 2014 at 7:51 am

    Thank you Acland. However, I had already changed my passwords with Dropbox and updated the scripts with the new passwords. It is after that I started receiving this error message. I am meticulous when changing passwords with documenting in Evernote and copying and pasting the passwords into scripts, so I am confident they are correct.

    • Acland on Tuesday, November 11, 2014 at 4:24 pm

    Hmmm – interesting. Is it worthwhile getting a fresh copy of the script and starting from scratch in case you’ve accidentally deleted something? As I said, I was having the same problem and once I fixed the password everything seemed to work fine – and is working fine now too.

    • kavlito on Thursday, November 13, 2014 at 8:41 am

    Hi again. Already did the fresh copy thing. Everything is fine with all the sites. The only thing that changed was the passwords. Thanks.

    BULLETIN: THIS JUST IN!

    I woke up today to find that everything is working on all sites today! According to my sites, from 11/6 to 11/12, no backups were done due to the message in my previous post on 11/9. That must indicate a problem on Dropbox’s system, that is obviously now corrected!

    Now, the only issue I have is the script is not deleting temp files as indicated in my post on 11/10.

    Thanks (to all) for your time and efforts! It really is appreciated. And, I’m learning along the way!

    • kavlito on Thursday, January 8, 2015 at 9:44 am

    Happy 2015 everybody!

    I’ve been using DropBoxUploader successfully on many different sites such as ZenCart, PrestaShop and AppGini. However, utilizing the same code files from those sites, I’ve been unsuccessful in getting it populate backup data for WebTrees and Feng Office. They’re both MySQL db’s and when I run this script with them (on the same server as the latter 3 that work), with their appropriate config files, all I get is empty data files in DropBox. No errors. Just empty data files.

    Anybody else have similar situations?

    Thanks.

    • Eric Silva on Thursday, January 8, 2015 at 9:48 am
      Author

    Have you verified the mysqldump command is producing a non-empty file?

    • kavlito on Sunday, March 8, 2015 at 2:39 pm

    Dropbox Uploader has been down since 2/27. I already checked for updated code, but still the same.

    Thanks.

    • acland on Sunday, March 8, 2015 at 5:43 pm

    Hi Kavlito – I found a fix – go to line 251 of dropboxuploader.php and change:
    if (!preg_match(‘#, “TOKEN”: “([A-Za-z0-9_-]+)”, #’, $html, $matches))
    to:
    if (!preg_match(‘, js_csrf=([A-Za-z0-9_-]+), ‘, $html, $matches))

    not my work – but some googling found the fix and it works for me.

    • acland on Sunday, March 8, 2015 at 5:49 pm

    Also, there’s a new version of dropboxuploader.php too and it has the fix – here’s a link to the raw file at githup:
    https://raw.githubusercontent.com/jakajancar/DropboxUploader/master/DropboxUploader.php

    • kavlito on Sunday, March 8, 2015 at 8:04 pm

    Eggcellent! All good now! I checked the link from Eric’s post on 8/21 – that was the same as the code I have now. The link that you provided, is that the official link to check for future updates?

    Thanks again!

    • ali on Wednesday, April 8, 2015 at 10:47 am

    hi thanks first . second we run this script its create a empty sql file .
    then we run mysqldump file manullay from server its create a sql file fine whats problem why script create empty file

    • John on Tuesday, May 5, 2015 at 9:15 pm

    Thanks for this works fine!

    Server Cron job send this notice after successful completion:
    tar: Removing leading `/’ from member names

    Any idea what the meaning of this is?

    • kavlito on Tuesday, July 28, 2015 at 8:52 am

    Hi All,

    DropBoxUploader has not been working again since 7/23.

    • bino65 on Tuesday, September 8, 2015 at 8:56 am

    hi all, also with me drobpoxuploader non working again since 7/23.
    We print error “Login unsuccessful.”.

    • Acland Brierty on Friday, October 2, 2015 at 9:20 pm

    I have a working solution using the core of eric’s script but I had to abandon the dropboxuploader script and do it through the dropbox php core api – it’s surprisingly easier than you think but you have to jump through a few hoops first. Ready to begin?
    1: you need to create a dropbox developer account (easy) and create an app (very easy): https://www.dropbox.com/developers

    Give the app an easy name and give it access to everything. The app doesn’t do anything but it gives us access to three key pieces of info we need to enable our uploads to resume/work again.

    We need the App Key, the App Secret and Generate access token.

    basically we had to create the app in order to get those three key bits of info.

    2: Now, we need to download the core php libraries from here:
    https://www.dropbox.com/developers-v1/core/sdks/php
    Go ahead and extract that then open the folder, go to the ‘lib’ folder and copy the folder DROPBOX and upload that to your webserver. In my files I created a folder called dropbox-sdk and put it in there. So my path would be /dropox-sdk/Dropbox.

    We have to add one more file ourselves to that Dropbox folder. Create a file called app-info.json and simply enter this:

    {
    “key”: “INSERT_APP_KEY_HERE”,
    “secret”: “INSERT_SECRET_HERE”
    }

    And save it.

    You’re basically done with this part of the sdk and we don’t have to touch it again.

    3:I’ve gone ahead and edited Eric’s original file and implemented the changes you need to make this work and you can download it here: https://dl.dropboxusercontent.com/u/3430616/SQLtoDropbox.php

    In short, we’re calling the new sdk to get access to dropbox, then Eric’s script generates the files and zip as before, then we use the new sdk to upload the file.

    I hope this helps. It sounds like a headache but it really is quite straightforward. Download my file and you’ll see with some simple copying and pasting you can get your old script to work in a matter of minutes.

    • Jerome on Saturday, October 3, 2015 at 12:35 am

    I had the same issues and siwtched to the free version of this excellent application: http://mysqlbackupftp.com/
    that works really fine – but it’s run from a local PC only.

    • kavlito on Saturday, September 24, 2016 at 8:31 am

    Hi, I’m guessing support for this has been dropped? It hasn’t work for well over 6 months.

    • acland on Saturday, September 24, 2016 at 3:30 pm

    See my post above for a solution using part of this script and the dropbox api. It works fine using that method.

    • Acland on Saturday, November 18, 2017 at 12:33 am

    Finally I have a new working version of this script that works with the v2 of the api – we are going to use eric’s code to generate the file but not the droploader.php. Step 1; follow my instructions above to set up a developer account and create an app with dropbox. You are going to need the access token.

    Step 2: Delete line 30 of Eric’s example page code ( you don’t require the dropbox.php file) and delete lines 64 to 70 of his script – we’re not using dropboxuploader at all. at line 64 paste in the code below – delete (including the ) and replace with your access token:

    $fp = fopen($backupFile, ‘rb’);
    $size = filesize($backupFile);
    $cheaders = array(‘Authorization: Bearer ‘,
    ‘Content-Type: application/octet-stream’,
    ‘Dropbox-API-Arg: {“path”:”/’.$dropbox_dest.’/’.$backupFilename.'”, “mode”:”overwrite”}’);
    $ch = curl_init(‘https://content.dropboxapi.com/2/files/upload’);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $cheaders);
    curl_setopt($ch, CURLOPT_PUT, true);
    curl_setopt($ch, CURLOPT_CUSTOMREQUEST, ‘POST’);
    curl_setopt($ch, CURLOPT_INFILE, $fp);
    curl_setopt($ch, CURLOPT_INFILESIZE, $size);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);
    fclose($fp);

    That’s it. I have set the mode to overwrite as I have a cron job that runs a few times a day getting my sql file and backing it up to dropbox – so I just overwrite the original.

    Make sure you have the last part of his code:
    // Delete the temporary files
    73
    unlink($sqlFile);
    74
    unlink($backupFile);

    That’s about it. It works straight out of the box for me.

    • Acland on Saturday, November 18, 2017 at 12:46 am

    reading back it sounded confusing so here is a link to the file I created that uses Eric’s original script.

    https://www.dropbox.com/s/ofwi8b9tmr02786/dropbox_backup.php?dl=1

    All you need to do is create a dropbox developer account which is easy and create an app and get the access token. It’s very straight forward.

Load more

  1. […] 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 […]

Leave a Reply