[intensecool]

Originally Posted by sross

Sounds cool. I currently run a script called automysqlbackup.sh (http://sourceforge.net/projects/automysqlbackup/), my db is 570meg gzip. I then rsync my site to a local ubuntu box which in turn brings down the dumped db. I extract the db's from time to time and they extract without error. I also have used the dbs to build dev servers. I have not run into any problems so far. Do you think this is ok? I am also wondering about stopping http, even if you do this are there still not sessions attached to the mysql db? Does stopping http nuke all connections to the db? I also run lightspeed webserver so does my command to stop that before my backup differ? Thanks!

To me ,
The method is cool, i have used that one lots of times for backups.
But there is a difference with your DB of 570MB & 5GB.

Mine are over 6.5 GB now & when you try to dump that big server loads shoots up high , i have seen loads of over 50 at times.
This is why we need solution to backup without shooting that load which of course messes up DB , lags the server and hangs your website for a longer time period , while my method safely takes the backup, then does an scp that backup to remote server and turns your server back on.
There are several methods to dump small DB's but for big ones you need something like that one.

Originally Posted by Angel-Wings

Or - more easy - install MySQL on your local comp and setup replica. Then power it on, wait until it's in sync, lock the tables (e.g. stop the slave), do the backup and start the slave again.

Result is you won't need a root access for SSH (rather dangerous to allow this), your SSH keys still use a passphrase (required to have) and your backups will be clean as well.

And nope - your local MySQL don't need to run all the time

Think you can not do it on automation , you need to manually do it everytime.
For my method, its all automated.