Oct 16, 2013 | sql

Download Latest SQL Backup Every Day

Warning: This post is over a year old. I don't always update old posts with new information, so some of this information may be out of date.

Today I got a question from a Karani user about our backups. We maintain backups on-server, and our host maintains backups off-site, so I felt comfortable assuring the user of the security of our backups. But inside I thought--wouldn't it be great if I could get a daily copy of the latest SQL backup? Turns out it's extremely easy.

StackOverflow to the rescue. This is focused around Unix-based systems, so, sorry Windows folks.

Step 1: Create a shell script to use scp to download your latest backup

Here's mine:

scp $destination:`ssh $destination ls -1td $remote_dir/\* | head -1` $target_dir

Of course, you'll want to update remote_dir, target_dir, and destination's values to be appropriate for your system.

Note: When I first created this file, the permissions weren't correct for cron to run it. I chmod'ed the file to 777 to test to make sure it works, but I still need to figure out what the absolute best chmod value would be.

Note 2: You'll need to have ssh key authorization already set up for this domain so scp & ssh can access it properly.

Step 2: Use cron to run that shell script every day

First, edit your crontab:

$ crontab -e

Then, paste your line:

0 2 * * * /path/to/shell/script.sh

The above line runs the script at 2am every day, but you can adjust the timing--learn more about cron timing.

Step 3: Profit

That's it! You now have a local cron job running once a day, copying your remotely generated files to your local computer.


Using the crontab editor

By default, the crontab editor uses vim. Trust me when I tell you that learning vim is absolutely worth it--check out the intro to vim that ships with vim by running $ vimtutor to learn the basics in 5-10 minutes. But if you need to just get by for now:

Type i to enter Insert mode, and now you can type. To save, type esc and then type :x and hit return. That's it!

Cron job for running SQL backup

Are you looking for a server-side cron job to set up daily SQL dumps? Here's mine:

0 1 * * * /usr/bin/mysqldump -h host-name-here -uusername-here -ppassword-here --databases database-name-here > /path/to/sql/dumps/$( date + \%Y-\%m-\%d).sql

That generates files with the date name as the file name, every night at 1am. You can adjust the timing, and be sure to put your own: route to mysqldump, MySQL host name, username, password, and database name. Notice that the user flags and password flags do not have spaces--so if your username is bob and your password is secret, those flags would be -ubob and -psecret .

Emailing the results

How do you debug problems? If you add the following line to the top of your cron job, it will email you any output from the script.


I haven't yet tested to see if this works locally, though, so I'll update this when I have.

Comments? I'm @stauffermatt on Twitter

Tags: sql


For quick links to fresh content, and for more thoughts that don't make it to the blog.