Automating MYSQL Backups to Dropbox in Ubuntu – 2 simple steps

You can easily automate daily backups of your MYSQL database and other files, to your Dropbox account in 2 simple steps.

The scare happened a few weeks ago. I did a regular Ubuntu OS update and a few MYSQL packages also upgraded. Rebooted the machine and viola! MYSQL wont start and I’d no backup. (Stupid yet true!). With great difficulty and lots of help from pros, I managed to restore the database with minimal data loss. I was extremely lucky but a risk not worth taking. So here’s a very easy method.

Step 1: Install Dropbox

You can install Dropbox (headless) using a single command. (If you don’t have a dropbox account, sign up here)

32-bit: cd ~ && wget -O – “https://www.dropbox.com/download?plat=lnx.x86” | tar xzf –
64-bit: cd ~ && wget -O – “https://www.dropbox.com/download?plat=lnx.x86_64” | tar xzf –

Next, run the Dropbox daemon from the newly created .dropbox-dist folder using

~/.dropbox-dist/dropboxd

If you’re running Dropbox on your server for the first time, you’ll be asked to copy and paste a link in a working browser to create a new account or add your server to an existing account. Once you do, your Dropbox folder will be created in your home directory.

Step 2: Create the MYSQL Dump & add a Cronjob

It is quite easy to create a backup of your MYSQL database. I created mine as followsL

$ mysqldump -u’username’ -p’password’ database_name > database_name.sql

This will create a .sql file in the Dropbox folder, which will automatically get synced with your dropbox account. THAT’S IT!

To automate $ crontab -e

Add the line: 0 6 * * * mysqldump -u’username’ -p’password’ database_name > database_name.sql

Now every morning at 6 am, the cron job will run, creating a backup file of my database and get synced with dropbox.

Note: This will replace the file everyday and you will only have the most recent backup.

If you want a daily backup append the file name as

mysqldump -u’username’ -p’password’ database_name > database_name-$(date “+%d-%m-%Y”).sql

Other cool things you can do

So now that MYSQL backup is taken care of, I also wanted to back up some code and other files. I simply created symlinks and Dropbox works like a charm

$ ln -s /home/code ~/Dropbox

$ln -s /var/www/html ~/Dropbox

Again, this command will create the most recent copy of the files in dropbox. If you want to automate a daily backup, you can add another cronjob like above. Example below:

0 6 * * * zip -r ~/Dropbox/html.zip /var/www/html