Hi, I do just this at the moment - I have a cron job that runs MySQL dump, gzips the output, and will then ftp the important files to a machine that get's backed-up to a tape drive. I also time the dump, and it currently takes just over 3 minutes which is quite acceptable for what I'm doing. I'm thinking about piping the output of mysqldump straight through gzip and then ftp'd away to ease the disk access too, but that maybe later.
I would still like a best-practices guide though, so that if everything does go wrong I'm sure that I've got everything I need to reconstruct the system as swiftly as possible. I've done some dry runs, but still feel that this isn't the same as learning from that gleaned by others that may have actually been faced with disaster in the past! Thanks, Mike > -----Original Message----- > From: David Brodbeck [mailto:[EMAIL PROTECTED] > Sent: 11 February 2004 19:27 > To: 'Michael McTernan'; Michael Collins > Cc: [EMAIL PROTECTED] > Subject: RE: best-practices backups > > > > > -----Original Message----- > > > From: Michael Collins [mailto:[EMAIL PROTECTED] > > > > Is there any "best-practices" wisdom on what is the most preferable > > > method of backing up moderately (~10-20,000 record) MySQL 4 > > > databases? A mysql dump to store records as text, the > > format provided > > > by the BACKUP sql command, or some other method? > > I think it depends on how long a backup window you can tolerate. > > On our databases, I use mysqldump to dump to a text file. The backup is > piped through gzip to reduce the size on disk. This has the advantage of > being portable between architectures, robust, and human-readable. > > I also run with update logging turned on, and turn over the logfiles after > each backup. This way I can restore the database to any point in time by > restoring the next earliest backup, then running as much of the update log > as necessary to get to the desired point. I use a script with the 'find' > command to weed out old backups and update logs older than a > certain number > of weeks. All of this is run nightly by cron entries. > > The disadvantage of this method is that mysqldump can take a while to dump > large databases, and the tables are locked during the backup process. If > this is a problem, you should probably investigate mysqlhotcopy. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
