Download Latest SQL Backup Every Day
Posted on October 16, 2013
!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.
Step 1: Create a shell script to use scp to download your latest backup
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.
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:
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
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