Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

How to automate a remote backup UNIX
This past weekend, I took advantage of OS X's UNIX core to automate the database backup for this site. Until recently, I backed up the data by connecting to the ISP, launching the mySQL database program, exporting the data, then using one of a number of transfer programs (Fetch, RBrowser, etc) to bring the file back to my home machine.

Although this worked, it was quite tedious, and I would often forget to do the backup. Then another geeklog-powered site had a major disaster, and lost all of their articles (, if you'd like to stop by and help them rebuild, it'd be greatly appreciated!). This was the kick I needed to find a better way to do my backups.

Read the rest of this article if you'd like a little insight into how I used some basic UNIX programs to handle this repetitive mundane task. This is fairly basic stuff for UNIX wizards, and I'm sure I could be doing this in a more advanced way, but it does demonstrate how the UNIX core of OS X can be put to good use.

My automated backup solution relies on four core OS X features - SSH (secure shell), SCP (secure copy), cron (task scheduler), and shell scripts. The task I needed to accomplish was to connect to my ISP, generate a data dump from the database, download the resulting file, name it something appropriate, and repeat this task regularly. Here's what I did:
  1. I wrote a very simple shell script (named "dobackup") on the ISP which exports the database to a SQL dump file. This shell script is literally two lines long:
    #! /bin/sh
    /usr/local/mysql/bin/mysqldump --add-drop-table dbfilename > dbackup.sql
    To make it executable, I gave the file execute permissions with chmod.

  2. Before I could automate the process, I had to enable SSH to connect to my ISP without asking for a password, since I wanted to run this job unattended. This basically required running sshkeygen on my local machine, and then copying the resulting public key file to an SSH directory on the ISP. The "man" pages for ssh explain this in much greater detail, if you're interested.

  3. I also wanted to have a sequential series of backups available, which meant that I would ideally like to rename the file as I downloaded it from the ISP, instead of simply overwriting it each time. So I needed to figure out how to use variables in shell scripts, something I'd not done before.

  4. My final script on the Mac OS X box looks like this:
    #! /bin/sh
    newtime=`date +%m-%d-%y_%I%M%p`
    ssh -l uname ./dobackup
    scp /path/to/local/storage/hintsbkup_$newtime
    ssh -l uname rm mybackup.sql
    This file was saved (called "getmybackup") and chmoded to be executable. Here's what each line does.

    #! /bin/sh - Tells the system this is a shell script.

    newtime=`date +%m-%d-%y_%I%M%p` - Creates a variable (newtime) and assigns it the result of the shell "date" command. This took the longest to figure out - anything that appears in backquotes will be interpreted by the shell, and the result of the command will be used as the value for the variable. All the '%' characters just format the date (type man strftime at the command prompt for more on date formats). Basically, those % statements make it look like "02-05-2001_1215pm". This is the value that is given to "newtime."

    ssh -l uname ./dobackup - The SSH command connects me to the host, and the last word in the command tells the host to execute the two-line shell script (from step 1) that creates the backup file.

    scp /path/to/bkfiles/hintsbkup_$newtime - This is the UNIX secure copy command, which basically takes the file I created on the host (dbackup) and copies it locally, but to a new name. You can see the "$newtime" variable listed after the filename; this appends the previously stored string to the filename prior to saving. So my saved file would be named "hintsbkup_02-05-2001_1215pm".

    ssh -l uname rm mybackup.sql - This just uses SSH again to delete the file on the host.

  5. The final step was to make the system run the job automatically, and this involved scheduling a cron task. I created a "crontab" file for my OS X user that would call the shell script in step four twice a day. Once I installed the crontab, everything worked as expected. NOTE: See the related hint on setting up cron tasks for more detail on how to use cron.

    With this relatively simple process, I was able to automate a task that's quite important to this site's long-term health, and teach myself a bit more about the UNIX system while doing so. Overall, I'm impressed with the capabilities that UNIX is bringing to the Mac OS environment!
  • Currently 3.00 / 5
  You rated: 2 / 5 (4 votes cast)

How to automate a remote backup | 5 comments | Create New Account
Click here to return to the 'How to automate a remote backup' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Authored by: swank on Jan 23, '02 08:12:39AM

great tutorial!

I did everything you said, but it didn't work (auto-login)

can you give some more detail on sshkeygen?

I read the ssh man pages, but it didn't say much about it...

[ Reply to This | # ]
Authored by: serversurfer on Jan 23, '02 10:15:59PM
I have no idea what problem you are having, so here's a guess. If, when you created your RSA keypair, you chose to password-protect it, you will be unable to use it to login via a cron-script because the script will hang while waiting for the passphrase. (You can use top to see if the getmybackup script is still hanging around, not doing anything.) Try running getmybackup in the terminal and see if it asks for a passphrase. The solution is to generate a keypair with no passphrase and use those instead. Hope this helps.

[ Reply to This | # ]
Authored by: thesiren on Jun 11, '02 10:20:30PM

i got the ssh-keygen to almost work. it says that my local "~/.ssh/identity" file is not protected enough. i chmod'ed it to 600, but it just asks for the password as if i didn't have the rsa key call in my shell script.

[ Reply to This | # ]
How to automate a remote backup
Authored by: sinemac on May 19, '04 02:26:47PM

This is exactly what I was looking for!!! Thanks!

However, I do have one problem that I don't know how to get around. I'm getting a "permission denied" error... I think probably because the username/password combination I use to access the server via ssh is different than the username/password combination I use for the mysql database.

If that is the problem, how do I modify either one of the script documents to add the mysql username/password?


[ Reply to This | # ]
How to automate a remote backup
Authored by: sierratarn on Aug 27, '05 04:42:40AM

[code]The "rm" step is strictly not necessary. Since dobackup uses the > to redirect output instead of >> the file is completely overwritten each time.[/code]

The advantage of not using the 'rm' line (just remove the line from the script) is that a database backup is left on the database machine.

A few other thoughts:
- A similar script would be great for backing up the files associated with a website. Though they don't change as often it's a great practice and not too much to automate.
- If you are using password controlled databases you may need to add "-u USER and -pPASSWORD" to the dobackup script. This isn't too bad if you also make it so nobody else can read the script ("chmod go-r dobackup"). This way you can protect the password from other users (but not Root).

[ Reply to This | # ]