Re: Backing up MySQL tables
2003-04-02 15:44:39
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
|
|
|