Cronjob mysql scp backup

software development

#1

i’d like to schedule a recurring (daily) backup of all my files and mysql data & schema to an external server via scp. does anyone have any experience with a cronjob that would do this? i’m particularly interested in the parts that would export the data & schema from multiple DBs and that would do an automatic login to the remote server via scp (since doing this manual requires a password to be entered each time).

thanks,
gabe


#2

You’d need to setup a passwordless ssh key on one side or the other (so that the scp connection can take place without a password), or else add the password to the script (the former is probably preferable).

Basically, you’d want to do a mysqldump, make a tarball of the directories / files you want, and then scp the tarball to the other server.

There is documentation on creating ssh keys online, but basically:

server1% ssh-keygen -d
Generating public/private dsa key pair.
Enter file in which to save the key (/home/user/.ssh/id_dsa): [enter]
Enter passphrase (empty for no passphrase): [enter]
Enter same passphrase again: [enter]

server1% cat .ssh/id_dsa.pub | ssh server2 ‘cat >> .ssh/authorized_keys2 && chmod -R 0700 .ssh’’

you should now be able to ssh from server1 to server2 without a password.


#3

Using Expect.pm might work too:


http://sourceforge.net/docman/display_doc.php?docid=9977&group_id=6894
http://www.cpan.org/modules/by-module/Expect/

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#4

thanks very much for the input and suggestions, will & jeff! i just now saw your suggestions since i didn’t receive email replies as i usually do (?). but i will look into these methods at some point soon and report back on what i find.

thanks again!

-gabe