«

»

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/

74 comments

4 pings

Skip to comment form

  1. acland

    Very cool script – you really saved my bacon with this – THANKS!

  2. Travis Chapman

    Hi, after trying to implement your script I received the following error when executing it:
    Parse error: syntax error, unexpected ‘{‘ in MYDIRECTORYTOFILE on line 62

    Any idea why I am getting this error? I can’t figure it out. Let me know

  3. Eric Silva

    @Travis It looks the script you have might have odd characters resulting in the parse error. Have you opened the file in an editor to see if there might be an odd line feed character or something else causing the problem? Also did you also download the required DropboxUploader.php script from the link above?

  4. yankeerudy

    Tried implementing this on a site running on Godaddy hosting (if that helps any) and when I execute it I get “File ‘/backup/tmp/db_2013_03_29.tgz’ does not exist or is not readable.” (I put your script and the DropboxUploader.php in /backup/ and set /backup/tmp/ as the $tmpDir.)

    Checked the perms on tmp folder are 755. Any ideas?

  5. Eric Silva

    @yankeerudy By putting the beginning slash in /backup/tmp you are indicating that there should be a backup/tmp folder in the root directory which I doubt is there. If you are running this from you ‘home’ directory on your GoDaddy account, try changing $tmpDir to ~/backup/tmp/. Let me know if this doesn’t work.

  6. yankeerudy

    Thanks for your quick reply, Eric. The solution you suggested didn’t work, nor did variations – i.e., ~/backup/tmp, backup/tmp, ./backup/tmp – but it did put me on the right track. As I was trying to run this manually from the browser, and since the script was running from the backup folder, when I changed $tmpDir to “tmp/” it worked like a charm. Thanks for this great implementation!

  7. mgangsta

    great guide man this shit is saving my ass every hour

    just for those if u find ur uploaded file empty

    just echo this and test the cmd

    55 – “mysqldump -u “.$user.” –password=”.$password.” “.$dbName.” > “.$sqlFile;

    for those who using special character in password

    just

    added single qoute at “.$password.” > ‘”.$password.”‘

    $createBackup = “mysqldump -u “.$user.” –password='”.$password.”‘ “.$dbName.” > “.$sqlFile;

    or

    simply add ur password within single qoutes

    $password = “‘password'”;

  8. vinod chauhan

    Hi,
    I am also trying to implement this,i have put correct my dropbox login detalis, and i am running script in browser: i shows following error: Login unsuccessful.

  9. Cpm (@mahooneys)

    Hi

    Where do I specify the database host? My hosting company don’t use localhost.

  10. Eric Silva

    @mahooneys The script in its current state assumes the database is on the same host. In your case you would need to modify the script to include the “-h ” parameter to the mysqldump command.

  11. Cpm (@mahooneys)

    Thanks Eric, my PHP skills are limited though, can you explain in a little more detail where I’d add this and where I’d specify the host, if it’s not too much trouble. I appreciate your response!

  12. Eric Silva

    @mahooneys No problem. I created an updated script especially for you. You can download it here: https://gist.github.com/ericjsilva/6250250. Look for the $dbHost variable. You can change ‘localhost’ to the hostname or IP where your MySQL database is. Hope it works out.

  13. Cpm (@mahooneys)

    Thanks very much, Eric.

    I replaced the old version, added the host variable, and it’s creating the file in my dropbox acount! However, the file it created is only a few lines of code, each line prefixed by code like “@OLD_” , and no actual databse data. Any idea what might be causing that? The database I’m trying to copy over is about 400 MB, if that’s of any relevance.

  14. Eric Silva

    Can you send me the file it created? eric at ericsilva dot org.

  15. Cpm (@mahooneys)

    No need. I had misspelled the database name. Everything’s working perfectly now. What a great little script. Thanks very much or your help! 🙂

  16. Eric Silva

    @mahooneys Glad it’s working for you.

  17. Marvin Gaye (@20marvin_gaye20)

    @Eric

    Having a problem using the script… I’ve downloaded DropboxUploader.php. When trying to run the script i got this error “Parse error: syntax error, unexpected ‘{‘ in MY/ROOT/DIRECTORY/dbBackup.php on line 74.

    Any help would be appreciated

  18. Eric Silva

    @20marvin_gaye20 Someone else had this same issue back in January. I don’t know what his issue was as he never responded back. Check the syntax of your dbBackup.php file. Perhaps you have a missing semicolon or other defect causing the syntax problem. I also just updated the code sample above with the latest code. Let me know if you still have difficultly.

  19. Marvin Gaye (@20marvin_gaye20)

    @Eric Thank you for your response. I’ve look the script line by line and haven’t see any defect causing my problem. I have paste the script that i’ve copied above. Your help will be much appreciated.

    require(‘DropboxUploader.php’);

    // location of your temp directory
    $tmpDir = “/tmp/”;

    // username for MySQL
    $user = “**********”;

    // password for MySQL
    $password = “***********”;

    // database name to backup
    $dbName = “**********”;

    // hostname or IP where database resides
    $dbHost = “**********.db.1and1.com”;

    // the zip file emailed to you will have this prefixed
    $prefix = “db_”;

    // username for Dropbox
    $dropbox_user = “**********@yahoo.com”;

    // password for Dropbox
    $dropbox_password = “**********”;

    // Destination folder in Dropbox (folder will be created if doesn’t yet exist)
    $dropbox_dest = “db_backups”;

    // Create the database backup file
    $sqlFile = $tmpDir.$prefix.date(‘Y_m_d’).”.sql”;
    $backupFilename = $prefix.date(‘Y_m_d’).”.tgz”;
    $backupFile = $tmpDir.$backupFilename;

    $createBackup = “mysqldump -h “.$dbHost.” -u “.$user.” –password='”.$password.”‘ “.$dbName.” > “.$sqlFile;

    //echo $createBackup;
    $createZip = “tar cvzf $backupFile $sqlFile”;

    //echo $createZip;
    exec($createBackup);
    exec($createZip);

    try

    $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);

    ?>

  20. Marvin Gaye (@20marvin_gaye20)

    @Eric Sorry I’ve deleted the curly brace in try-catch exception…

    require(‘DropboxUploader.php’);

    // location of your temp directory
    $tmpDir = “/tmp/”;

    // username for MySQL
    $user = “**********”;

    // password for MySQL
    $password = “***********”;

    // database name to backup
    $dbName = “**********”;

    // hostname or IP where database resides
    $dbHost = “**********.db.1and1.com”;

    // the zip file emailed to you will have this prefixed
    $prefix = “db_”;

    // username for Dropbox
    $dropbox_user = “**********@yahoo.com”;

    // password for Dropbox
    $dropbox_password = “**********”;

    // Destination folder in Dropbox (folder will be created if doesn’t yet exist)
    $dropbox_dest = “db_backups”;

    // Create the database backup file
    $sqlFile = $tmpDir.$prefix.date(‘Y_m_d’).”.sql”;
    $backupFilename = $prefix.date(‘Y_m_d’).”.tgz”;
    $backupFile = $tmpDir.$backupFilename;

    $createBackup = “mysqldump -h “.$dbHost.” -u “.$user.” –password=’”.$password.”‘ “.$dbName.” > “.$sqlFile;

    //echo $createBackup;
    $createZip = “tar cvzf $backupFile $sqlFile”;

    //echo $createZip;
    exec($createBackup);
    exec($createZip);

    try
    } // in this part the error occur……
    $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);

    ?>

  21. Eric Silva

    @20marvin_gaye20 Looks good. I am wondering if there is something odd with the character encoding. What environment are you developing in? DId you copy/paste the original code, or did you download the file? One thought would be to try and download a clean version from Gist perhaps: https://gist.github.com/ericjsilva/6250250

    Make the needed changes to the variables, and run it again.

  22. Marvin Gaye (@20marvin_gaye20)

    @eric I just copy/paste the code. Will download a clean version from Gist and will let you know the result as soon as possible. Thanks again eric.

  23. ScottD

    I can’t seem to get this to run the backup script. Every time it runs, I get an email saying: Could not open input file: home/httpd/vhosts/mysite.com/httpdocs/backups/mysql_backup_dropbox.php

  24. Asghar Jafri

    Thank you Eric. Here are some notes in case …
    1. I’m using Gator hosting and what they needed was a path for mysqldump (/usr/bin/mysqldump -h …). On my home server, c:\xamp\…

    2. The minimum rights needed for backup are : Create, lock tables, select, show view (If you have views and come to think of it, I haven’t tested for lock tables).

    3. Thanks Mgangsta – your advice on password helped. I’d like to point out that what worked for me was doublequotes within single quotes as I had a single quote inside my password. ie ‘ “pa’word” ‘ (putting in spaces between the 2 types of quotes for clarity only)

    4. Another note on Gator hosting. To run this script with cron, you need to give the path for php also. /usr/bin/php /home/mysitename/xx/backupprog.php (with extra spaces between the php and the program address for clarity only).

  25. Thaer

    Thank you 🙂 worked like a charm, customized abit to be able to pass vars for different DBs

  26. kavlito

    I get the following error when running the script:

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

  27. Eric Silva

    @kavlito this is a known sporadic issue in the DropboxUploader utility. See https://github.com/jakajancar/DropboxUploader/issues/7 for more issues.

  28. kavlito

    Thanks. After running it a 2nd time, it seems to have run because I did not get the error msg, but a blank white screen. Upon checking my Dropbox, there was a file there, however, upon extraction, my 70mb db file only extracts to 1kb file. No data.

  29. kavlito

    Got it all worked out! Thank you so much for this! Not only is this going to free up a lot of my time, but I’ll now be able to back up so much more often – across all of my sites. Again, thanks!

  30. Eric Silva

    @kavlito glad everything worked out.

  31. KAP

    Hi
    It all works, beside the sql file is empty…
    Any suggestion??

    Regards Karsten

  32. Eric Silva

    @Karsten have you tried manually running the ‘mysqldump’ command on your server to see what the response is?

  33. kavlito

    Hi Eric,

    Any info on why all dropbox backups ceased on 4/24? I’m using this on 10 different sites and they all stopped on 4/24.

    Thanks.

  34. Eric Silva

    @kavlito It looks like something changed with Dropbox. You can see more details here: https://github.com/jakajancar/DropboxUploader/issues/25

    I’ll see if I can update the DropboxUploader library to include a fix.

  35. acland

    I found a fix on github:
    comment out line 89 – just in case we need it again
    //$postData = array(‘plain’=>’yes’, ‘file’=>’@’.$source, ‘dest’=>$remoteDir, ‘t’=>$token);

    and add this new $postData line

    $postData = array(‘plain’ => ‘yes’,’file’ => ‘@’ . $source,’dest’ => $remoteDir,’t’ => $token,’_subject_uid’ => ‘XXXXXXX’, ‘mtime’ => ‘1348870752’);

    Now, here’s the key – log in to your dropbox account and hover over one of the links to your backup files and write down the SUBJECT_UID number (it should show up in the link url in the bottom of your browser) – put that number into the code where I have the XXXXXXX.

    If you don’t put YOUR subject_uid in there then the script will throw errors. The code examples on github have the author’s subject_uid in it and people are getting errors because they’re using that instead of their own uid.

  36. kavlito

    Thanks alot Eric. However, I get this error msg when inserting the new code:

    Parse error: syntax error, unexpected ‘@’, expecting ‘)’ in /home/iyponlin/public_html/productdev/DropboxUploader.php on line 131

    Line 131 being: ’file’ => ‘@’ . $source,

  37. Acland

    Not sure why you get the error. My code works fine BUT delete the mtime=>xxxxx bit. It makes all your back up files created on the same date. By deleting mtime your file creation dates will appear as normal

  38. kavlito

    Hi Acland,

    Looks like when I copied the code, all the single quotes were actually Begin & End Single Quotes. Once I changed them to apostrophes, the error went away.

    And thanks for the mtime removal advice. I took that out once I noticed the same date/time stamps.

    All is working again!

    Thanks a lot everybody! This is truly one of the best uses of dropbox.

  39. Sfs

    After applying the fix, my setup stopped working again on May 17, 2014. I’ve changed the file back to the original, but don’t know if it’ll work yet.

  40. acland

    @Sfs – I’m seeing the same thing – backups have now stopped working on the 17th – I will do some scouting to see what’s happening and if there’s any fixes.

  41. acland

    I have the solution now – you need to update the DropboxUploader.php file. You can get it here: https://github.com/jakajancar/DropboxUploader/blob/master/DropboxUploader.php

    I’ve just done it and everything is back to normal now.

    Hope this helps!

  42. Sfs

    @acland — You’re the best. Thanks very much.

  43. Alex

    Since the 15th of August 2014 I receive this warning: ‘Login unsuccessful’. It seems like Dropbox does not allow access anylonger. I haven’t changed anything in the code and it has worked on multiple accounts up and until the 15th, but not any more. I do have the latest (17) version of the DropboxUploader.php present.
    Anyone who can confirm this is a general issue?
    Alex

  44. Yankeerudy

    Yes, I can confirm that “login unsuccessful” message. It stopped working a few days ago.

  45. kavlito

    Out of curiousity, is this a legitimate use for DropBox? I ask because this is the 3rd time since I’ve been using it is put out of commission. As much as I love the convenience of this utility, the inconsistency in its uptime and lack of warning when something goes wrong makes me wonder if I should be using something else.

  46. Alex

    I think it is legitimate use but Dropbox is free in changing how the program and security works. I am not sure if they publish the changes to 3rd level developers but even then we are still subject to the availability and willingness of the developer who wrote these files in the first place…

  47. Acland

    Hi, I am by no means a php expert but have created a work around using Eric’s script but for some reason this forum blocks it when I paste the text in here. You can download the text file for my solution here:

    https://dl.dropboxusercontent.com/u/3430616/eric-solution.rtf

    In short, if you really need the sql backup service, this is the simplest and only solution I can find until they fix dropboxUploader. It sounds complicated but it’s not – you’ll download the dropbox sdk (links are in the file) create a dropbox app (instructions in file) generate an access token – then add around 8 lines of code to Eric’s script and things will be back to normal.

Fetch more comments

  1. Backup MySQL Database to Dropbox | BlogoSfera

    […] also found a MySQL backup to Dropbox tutorial (credit where it’s due), which some of the code below is based on. It is a great […]

  2. Backup MySQL Database to Dropbox | oizuled.com

    […] also found a MySQL backup to Dropbox tutorial (credit where it’s due), which some of the code below is based on. It is a great tutorial, […]

Leave a Reply