backing up a remote database

neuron · Advocate Joined: 28 May 2002 Posts: 2371

I wanna automate backing up a remote database and transporting it to my home system.

what would be the easiest way of doing it?
the absolute best way of doing it would be my server ssh'ing in and executing a backup and scp script or something, not quite sure how I'd do it though.

the reason I wanna do it is that I don't 100% trust the remote server's security, so it'd be very preferable to keep the passwords needed on my box.

is there any way to for example do:

ssh <script>
script connects and enters password automatically (yeah, I know, big no no, but better on my side that on his).
runs mysql database backup (again, putting in password from my script on my computer)
then transports it over?

or I could ftp or mail it I suppose. gpg and mail it from his box to an account on my box may not be such a bad idea. I'd still want a crontabbed script on my box to auto take it out of mailbox and save it somewhere else though (on an encrypted drive).

Lozzer · Posted: Tue Oct 14, 2003 10:12 pm Post subject:

Look up public key based authentication in the ssh documents. Basically you generate a key pair on you machine, and copy the public key to the database server, put it in the correct place with the correct permissions, and then you can login (from your machine) without entering a password. That just leaves you to write the script that does (without error checking!)

neuron · Advocate Joined: 28 May 2002 Posts: 2371

I don't have root on the remote machine though

kashani · Posted: Tue Oct 14, 2003 10:22 pm Post subject:

I'd do a local cron on the db server to dump a copy of the db. Then tar.gz the output and scp it over to your server using sshkeys into a role account without a shell. Should provide a reasonable amount of security.

You _should_ be able to grab the file from the command line, but I haven't figured out how to make the following work from a script.

ssh -A server scp yy-mm-dd.tar.gz account@new-server:

Maybe using an identity file with no passphrase. Sort of brings us back to the same scenerio I first mentioned depending on how openssh passes keys around.

Or you could always sftp it down. Hopefully some of this will lead you into the proper direction.

kashani
_________________
Will personally fix your server in exchange for motorcycle related shop tools in good shape.

neuron · Advocate Joined: 28 May 2002 Posts: 2371

thanks, I'll do some testing

Lozzer · Posted: Wed Oct 15, 2003 10:08 pm Post subject: Depends on configuration

Depending on how the remote machine has ssh configured, you may not need to be root to do this. The authentication keys go in