Download Latest SQL Backup Every Day

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:

remote_dir=/www/remote-user-name/sql_backup_directory
target_dir=~/local_sql_directory
destination=ssh_username@hostname
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.

Tips:

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.

MAILTO=youremailaddress@hotmail.com

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