[tlug] Remote MySQL backup and download

[Dave M G <dave@example.com>]

TLUG,

I have a bunch of web sites set up on various web hosting services (some 
are clients, some are mine), and each of them has a MySQL database as 
part of their structure.

What I want to do is set up a batch script that will log in to each 
server and download a copy of the database, just to keep everything 
backed up. Right now I'm backing up each database manually after each 
time I make changes, but since there are other users making continual 
changes, I want to do something more regular, all encompassing, and 
automatic.

I've worked with MySQL from the command line on my own machine, and done 
database dumps and stuff, so that part I think I can handle.

But I'm fuzzy on how I should be making the remote connection. Do I 
initiate an SSH connection first? Do I log in directly from within 
MySQL? Do I need to do a dump on the remote machine and then FTP the 
resulting .SQL file back to mine?

Of course, I hit the Google trail before coming here, but I'm finding it 
hard to form the right questions to get clear answers.

So I'm just looking for a little nudge in the right direction. If 
someone can tell me in general what the best approach is, I think I can 
find and edit the right code for my shell script on my own.

So... do I use SSH or something else to log in and then start a MySQL 
session? I don't quite understand how I would be executing commands from 
my batch shell in another, secondary, SSH command shell...

Any advice would be much appreciated.

Thanks.

--
Dave M G
http://tlug.jp/mediawiki/index.php?title=User:Dave_M_G