Using cronjobs to mysql dump and scp backup to another box
I need to run cronjobs to backup some of my databases daily and scp them over to another box.
I'm debating with my brain if I should just use one filename and have it overwrite the file every day or use multiple names (like a date) and manually delete them when I get a chance.
How would I do this? I know how to back up the databases using the shell, but I don't know how to instruct the command what the database password is or what the pass of the other box is.
Also, I would like a daily email informing me that the backup process was successful, if possible.
Can anyone help me out?
Hi Daiver. With respect to the naming convention of the dumpfile, perhaps you could use the weekday as part of the filename. This way you'll keep a week's worth of backups, but not have to manually delete files as they will be over-written after a week.
As for the general process. I would write a script to determine the backup filename, make the backup file and then scp to the remote host, then call this script from cron.
The script might look something like this:
In a production environment, it would probably make sense to check that the backup was successful (i.e. mysql and scp didn't return an error, and perhaps that the size of the backup file is not too small etc.), and perhaps send an email alert on failure. If there's one thing more horrible than not having a backup when you need one, it's having a backup which you can't restore because the backup process wasn't working properly...
I've done a similar setup at work. Here is how it can be done:
On MySQL host create a cronjob that makes a backup of your desired tables. Check out
for possible options. The one your want for password is ... --password="your pass" or short -p"pass"
Pass the output of mysqldump to a file which you can late transfer with scp. (pack it into an archive for smaller files)
We are creating a directory with the name of the date to hold the databases. We also create one .sql file for each database. But you can also create a file for each table in a database. Just check out the options of mysqldump. It's a great tool by the way.
So now that we have our sqldumps we can take care of copying it over to the other box.
Easiest way for this is first to have an scp login which does not query for a password.
Check out :
to acomplish this.
After automatic ssh login is possible we only have to use scp to shove the files over to the other box.
Considering the mail when it comes to failure just use the $? shell var that tells you the return code of the last programm.
I can give you some extract from our scripts when I'm at work on Monday. If still required then.
Hi guys. Thanks for your prompt replies.
Matthew's method sounds very easy to do if I can figure out how to do a script. Do I just copy and edit that in a file and call it from cron? I especially liked his idea of naming the file after the weekday to have 7 backups at all times. Would you know the necessary commands for the email part? Also, exactly how public will the passwords be? If I lock the file so only one user, which nobody has access to, can read or execute it, I can live with that.
Zhjim's method also sounds very good, however, I'm skeptical about leaving a user that does not require a password in a box. I don't have the expertise to secure that user so that if anyone gains access to it, he won't be able to do anything else but delete those backups. I'm worried, though, that the intruder might be able to tamper with them, making these backups useless. I've never been successful with working with keys. I'm sort of a newbie, but I guess that if I really try hard, I can get it to work. However, I thought that the key was an accessory to the regular user/pass method, sort of like added security. Also, I don't wish to backup by table, but just backup the whole thing. I'm dealing mostly with WordPress and vBulletin databases, the biggest one being around 30 MB. Eventually, I will need to pack the backup from that specific big database to save bandwidth.
If the script file is only readable by the one user, and access to that user is properly controlled, it's probably not an issue. Be aware that some corporate security policies may not allow having unencrypted passwords in program files (especially if they have to comply with that Sarbanes Oxley regulatory bill which has been brought in to some sectors recently).
There's several ways to do email from a script. The one I use most often is with the "mailx" program. Note that you have to have your machine configured to forward email. You can always test it from the command line. Here's the syntax:
If you have a longer message, you might want to use the heredoc style quoting:
|All times are GMT -5. The time now is 02:15 AM.|