Uploading MySQL backups to Dropbox

Uploading fles to dropbox

Objectives

  • Perform a backup of a mysql database and send it to a dropbox.

This is by no means a perennial solution to store data offsite, but it comes in handy as a quick and dirty solution to have files available on a shareable space.

Prerequisites

Using Dropbox-Uploader

We’re going to use Andrea Fabrizi’s awesome dropbox-uploader bash script to perform our file transfer. We download it and make sure it’s set as an executable.

  $ wget "https://raw.githubusercontent.com/andreafabrizi/Dropbox-Uploader/master/dropbox_uploader.sh"
  $ chmod +x dropbox_uploader.sh

The first time the script is run, an interactive wizard will run through configuration options which will be stored in a .dropbox_uploader file in your home directory.

$ ./dropbox_uploader.sh

This is the first time you run this script.

 1) Open the following URL in your Browser, and log in using your account:
   https://www.dropbox.com/developers/apps
 2) Click on "Create App", then select "Dropbox API app"
 3) Select "Files and datastores"
 4) Now go on with the configuration, choosing the app permissions
   and access restrictions to your DropBox folder
 5) Enter the "App Name" that you prefer
   (e.g. MyUploader15083804813002)

 Now, click on the "Create App" button.

 When your new App is successfully created, please type the
   App Key, App Secret and the Permission type shown
   in the confirmation page:

 # App key:

When this is done, we can run the dropbox_uploader script and not have to worry about authentication.

Our script

We’re going to create a script that makes the MySQL dump and uses dropbox_uploader to transfer the file. The script contains a MySQL password so we’re tightening up access to it.

$ touch db_backup.sh
$ chmod 700 db_backup.sh
$ vi db_backup.sh

Our database backups will use the format yyyy-mm-dd[_filename_suffix].sql.gz, and the script will accept one parameter which will allow us to add a suffix to our filename if needed (Gist).

#!/bin/bash
# Creates a dump of a database and stores it in our Dropbox folder

#Grabbing today's date by executing the /bin/date command
FILENAME=`/bin/date +%Y_%m_%d`

#If a filename suffix was passed as a parameter, we add it to our filename
if [ -n "$1" ]; then
  FILENAME+="_$1"
fi

#Storing the dump temporarily in the /tmp folder
mysqldump -u MyUser -pMyPassword MyDatabase | gzip -9 > /tmp/$FILENAME.sql.gz

#Using dropbox_uploader for the upload
/home/user/dropbox_uploader.sh upload /tmp/$FILENAME.sql.gz /DropboxFolderIfNeeded/$FILENAME.sql.gz

#Deleting the tmp file when we're done.
rm /tmp/$FILENAME.sql.gz
Don’t forget that the MySQL password has to be appended to the -p switch without any spaces.

Now if we execute our script:

$ ./dropbox-uploader.sh "mySuffix"
  > Uploading "/tmp/2015_05_09_mySuffix.sql.gz" to "/DropboxFolderIfNeeded/2015_05_09_mySuffix.sql.gz"... DONE

The dropbox_uploader developer went the extra mile and provided a neat dropShell.sh that allows us to use Unix type commands (ls, cd, cat) on our dropbox files.

$ wget "https://raw.githubusercontent.com/andreafabrizi/Dropbox-Uploader/master/dropShell.sh"
$ chmod +x dropShell.sh
$ ./dropShell.sh
DropShell v0.2
The Interactive Dropbox SHELL
Andrea Fabrizi - andrea.fabrizi@gmail.com

Type help for the list of the available commands.

user@Dropbox:/$

Further reading

  • John Nilson

    Good solution, thanks. I use when I’m working with Linux, but when I need to make a scheduled backups working on Windows I’m using MySQLBackupFTP (http://mysqlbackupftp.com/). This solution is the best for Windows.