Backup MySQL Database to Dropbox

Question

In the past I have received a lot of help from the SO community, so once I figured this out, I thought here's my opportunity to give back a little. Hopefully it helps someone.

The issue I was faced with was having my core site built on WordPress, with another database for an e-commerce section of the site, I wanted to backup the entire site (all files, both databases, etc.) to Dropbox on a daily basis.

After a lengthy search, I couldn't find anything that did exactly what I was looking for.

Disclaimer: You don't need to be running WordPress or an e-commerce site for this to work. It will work on any MySQL database(s) and requires PHP.

I came across the WordPress Backup to Dropbox plugin, which got me about 90% there. The plugin allowed me to back up all the files on the site plus it does a WordPress database backup at a frequency you schedule.

The problem is that the plugin only does a backup of the WordPress database, but not my e-commerce database.

I 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, but I wanted it to backup and delete the backup at different times - the tutorial backed up and deleted all at the same time.

Solution 1

The solution I came up with is not specific to WordPress or an e-commerce site. Anyone who has a MySQL database and can run PHP should be able to benefit from this. Perhaps with a few tweaks to my answer, but still they should be able to accomplish the end result.

To store a backup of the e-commerce database, I created a folder in my site's root directory (/temp - call it whatever you want). Then I had to actually create the database backup. Open up a text editor and create a file called backup_dropbox.php.

backup_dropbox.php

<?php
// location of your /temp directory relative to this file. In my case this file is in the same directory.
$tempDir = "";
// username for e-commerce MySQL DB
$user = "ecom_user";
// password for e-commerce MySQL DB
$password = "ecomDBpa$$word";
// e-commerce DB name to backup
$dbName = "ecom_db_name";
// e-commerce DB hostname
$dbHost = "localhost";
// e-commerce backup file prefix
$dbPrefix = "db_ecom";

// create backup sql file
$sqlFile = $tempDir.$dbPrefix.".sql";
$createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName." > ".$sqlFile;
exec($createBackup);

//to backup multiple databases, copy all of the above code for each DB, rename the variables to something unique, and set their values to whatever is appropriate for the different databases.
?>

Now this script should create a backup of the database "ecom_db_name" whenever it is run. To get it to run on a scheduled interval (I want it to run just a couple minutes before my WordPress backup starts to run at 7am). You can either use WP-Cron (if your site gets enough traffic to reliably trigger it to run at the right time) or schedule a cron job.

I am no expert on cron jobs and these types of commands, so there may be a better way. I have used this on two different sites and run them two different ways. Play around with what works best for you.

The first way is on a directory that is not password protected, the second is for a password protected directory. (Replace username and Password with your username and password, and obviously set example.com/temp/backup_dropbox.php to wherever the file resides on your server).

Cron Job to run backup_dropbox.php 5 minutes before WP backup

55 6 * * * php /home/webhostusername/public_html/temp/backup_dropbox.php

OR

55 6 * * * wget -q -O /dev/null http://username:[email protected]/temp/backup_dropbox.php

Now the cron job is set up to run backup_dropbox.php and create my database backup every day at 6:55am. The WordPress to Dropbox backup that starts at 7am usually takes about 5-6 minutes, but could take a little longer.

I want to delete my .sql backup files after they have successfully been backed up to Dropbox so its not sitting out there forever for someone to somehow open/download the database file.

Fire up the text editor again, and create another file called clr_bkup.php.

clr_bkup.php

<?
$tmpDir = "";
//delete the database backup file

unlink($tmpDir.'db_ecom.sql');
// if you had multiple DB backup files to remove just copy the line above for each backup, and replace 'db_ecom.sql' with your DB backup file name

?>

Since the WordPress backup takes a few minutes to finish up, I want to run a cron job to execute clr_bkup.php at 10 past 7, which should give it enough time. Again, the first cron job below is for an unprotected directory, and the second for a password protected directory.

Cron Job to run clr_bkup.php 10 minutes after WP backup starts

10 7 * * * php /home/webhostusername/public_html/temp/clr_bkup.php

OR

10 7 * * * wget -q -O /dev/null http://username:[email protected]/temp/clr_bkup.php

Sequence of events

To help wrap your head around what's going on, here's the timeline:

6:55am: Cron Job is scheduled to run backup_dropbox.php, which creates a backup file of my database.

7:00am: WordPress Backup to Dropbox runs, and backs up all files that have changed since the last backup, which includes my 5 minute old, newly created database backup.

7:10am: By now the Dropbox backup has finished up, so the Cron Job is scheduled to run clr_bkup.php, which removes the backup file from the server.

Variables, Notes, and Misc. Info

Timing

The first thing that hung me up was getting the timing right. For simplicity, I used the times in the example above as if everything was happening in the same time zone. In reality, my web host's server is in the US West Coast, while my WordPress timezone is set to the US East Coast (a 3 hour difference). My actual cron jobs are set to run 3 hours earlier (server time) than what is displayed above. This will be different for everyone. The best bet is to know the time difference up front.

Run Backup with a Time Check

In the directory that is not password protected, I wanted to keep the backup_dropbox.php script from running at any other time of the day than 6:55am (by visiting it in a browser at 10am for example). I included a time check at the beginning of the backup_dropbox.php file, which basically checks to see if it isn't 6:55am, then don't let it execute the rest of the code. I modified backup_dropbox.php to:

<?php
$now = time();
$hm = date('h:i', $now);
if ($hm != '06:55') {
    echo "error message";
} else {
// DB BACKUP code from above goes here
}
?>

I suppose you could also add this to the clr_bkup.php file to only let it delete the backup files at 7:10am, but I didn't really see the need since the only time clr_bkup.php will do anything is between 6:55-7:10am anyhow. Up to you though if you decide to go that route.

Not on WordPress?

There are a number of free and paid services that will backup your website either to Dropbox or another similar service like Google Drive, Amazon S3, Box, etc., or some will store the files on their servers for a fee.

Backup Machine, Codeguard, Dropmysite, Backup Box, or Mover to name a few.

Want Redundant Offsite Backups?

There are plenty of services that will allow you to automatically create remote redundant backups on any of the cloud storage sites listed above.

For example if you backup your site to Dropbox, you can use a service called If This Then That (IFTTT) to automatically add files uploaded to a particular Dropbox folder to Google Drive. That way should Dropbox ever have an issue with their servers, you'll also have a Google Drive backup. Backup Box listed above could also do something like this.

Hope this helps

There may be a better way of doing all of this. I was in a pinch and needed to figure something out that works reliably, which this does. If there are any improvements that can be made, please share in the comments.

Solution 2

I think this post explain a solution wich can help you: http://ericsilva.org/2012/07/05/backup-mysql-database-to-dropbox/

Comments

[ScottD]

I actually linked to that same post in the question above. Without the code from that post though I don't think I would have come up with the solution I was looking for. Thanks!

[Fabien Snauwaert]

It would make a lot of sense to compress the files. Here's how: $sqlFile = $tempDir.$dbPrefix.".sql.gz"; $createBackup = "mysqldump -h ".$dbHost." -u ".$user." --password='".$password."' ".$dbName."| gzip > ".$sqlFile; (Note: I don't think this would work on Windows.)