Copying back to list...

Bernhard Beck wrote:

Alex,

sounds like you are running MySQL in a production environment. There are basically three options (with regard to MySQL):

1) mysqldump (as already noted) - However, keep in mind this will place a table lock while dumping (especially if you are using MyISAM tables), which might be a problem in a busy database with large tables. On top of that mysqldump uses large amounts of disk space, because it dumps the actual SQL commands needed to recreate the database. Restoring from that is a pita. 2) mysqlhotcopy - Attempts to copy the database files (not dump the data), while holding a lock on the table. Somewhat addresses the disk space problem.

3) a separate MySQL slave server - That's what I'm currently pursuing.

Set up a separate server (doesn't need to be fancy, but fast enough to serve updates and inserts with little delay), and configure it as slave to your main database server (see the MySQL docs for how to do that). When backup time comes, stop MySQL on the slave, back up the file systems, start the MySQL slave again. MySQL will automatically re-synchronize the slave to the master. Stopping and starting MySQL should be possible using a wrapper around sendbackup, but I haven't tried that yet..

This way your master never has degraded performance or even down because of the backup, at the cost of an additional (fairly cheap) server.

If you are on Solaris you might want to look into file system snapshots as noted on this list recenly.

HTH,
Bernhard

Alex Thurlow wrote:

I need to start backing up MySQL tables on one of my machines, but I've heard that to you run the risk of table corruption if you just copy the mysql directory while MySQL is running. Stopping the database is not an option, and the only solution I've come up with is having another script do a mysql dump and then backing up that. It seems like a waste of time and resources to basically do 2 backups though. I was just wondering what anyone here does for MySQL backups on their own systems?

Alex Thurlow