robinadr

Tagged MySQL

From your database to your Dropbox

I’ve spent some time putting together a solution for automated database backups (from MariaDB in this case) to a remote data storage. Since my web host only does backups for my site itself, I was left to find a solution on my own. My goal was to find something that was simple, efficient, and most importantly of all, automated.

Here’s a brief overview of what my backup solution consists of:

  • A bash script is run weekly through cron
  • This script first creates a gzipped database dump using mysqldump
  • The dump is then uploaded to Dropbox[^1] using Dropbox Uploader
  • Once it’s uploaded to Dropbox, the local copy is deleted

The bash Script

~~~ .prettyprint

!/bin/bash

_file=”backup-$(date +”%Y-%m-%d”).sql.gz”

cd ~ mysqldump –user=username –password=password –host=hostname
–databases database1 database2 | gzip >$_file ./dropbox_uploader.sh -q upload $_file “/DB Backups/$_file” rm $_file



You can [download a copy of this script here](http://robinadr.com/uploads/2013/10/db-backup.sh_.zip). Most of it should be self-explanatory. Here's where you might want to change something:

* You can [change the date format](http://www.cyberciti.biz/faq/linux-unix-formatting-dates-for-display/)
* I have it set to upload to the DB Backups folder in the root of my Dropbox; you can set this to whatever you want
* Put **your own database info** in to the `mysqldump` command

## Setting Dropbox Uploader Up

`dropbox_uploader.sh` is a `bash` script that provides an interface to Dropbox. You can [download it from the Github page](https://github.com/andreafabrizi/Dropbox-Uploader), and follow the instructions to set it up. Place this file in the **same directory as the script above** unless you change the reference in the script.

I ended up giving the Uploader script full access to my Dropbox, as I wasn't sure how to give it application-specific storage and yet still access a folder in the root of my Dropbox.

<img src="http://robinadr.com/uploads/2013/10/db-backup-notification.jpg" alt="Backup notification" width="356" height="121" class="aligncenter size-full wp-image-813" />

Once all of this is set up, the script will automatically upload a backup of your database to your Dropbox at whatever interval you set.

[^1]: This is a referral link; if you sign up with it we both get an extra 500 MB for free.

So, I’m quite a reader of Particletree. They’ve got good articles, and it’s a solid read every time a new article pops up in my feed reader. Except when I try to comment, it took about 30 seconds to tell me that, no, I could not comment, because I “didn’t have JavaScript enabled.”

Two things:

  • Maybe make it so it actually does an efficient check? I am definitely running with JavaScript enabled.
  • Maybe give me a link to prove that I’m not a robot, because, you know, those “spam blockers” can fail.

Either way, I tried going through, twice, and both times I got false-flagged without an option to “prove myself.” The article in question was about backing up MySQL databases with PHP, and I just wanted to say that you might want to run the parameters through escapeshellarg(), and that if the host blocked PHP’s ability to run shell commands, that whole escapade wouldn’t work.

In that case, a completely-PHP routine would be needed, but I for one would rather take the easy way out.