«

»

Jul 05 2012

Print this Post

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/

72 comments

3 pings

Skip to comment form

  1. Acland

    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.

  2. Acland

    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.

  3. Acland

    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. 🙁

  4. Eric Silva

    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

  5. acland

    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

  6. acland

    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.

  7. Eric Silva

    @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!

  8. kavlito

    Currently getting this error and backups fail:

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

  9. kavlito

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

  10. Acland

    @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.

  11. kavlito

    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.

  12. Acland

    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.

  13. kavlito

    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!

  14. kavlito

    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.

  15. Eric Silva

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

  16. kavlito

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

    Thanks.

  17. acland

    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.

  18. acland

    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

  19. kavlito

    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!

  20. ali

    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

  21. John

    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?

  22. kavlito

    Hi All,

    DropBoxUploader has not been working again since 7/23.

  23. bino65

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

  24. Acland Brierty

    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.

  25. Jerome

    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.

Fetch more comments

Leave a Reply