Another suggestion I saw someone make that seems reasonable is to use a break away mirror for backups. I think they lock the tables for a minute break the mirror and unlock the tables. Then they backup the broken mirror at their leisure.
Dave On Tue, Dec 18, 2001 at 06:00:49PM -0500, Dave Greco wrote: > Looking over the documentation for MySQL, there doesn't seem to be a > consensus on the best way to perform backups of MySQL databases. So far, I > have come up with the following ideas: > > 1. Just copy the files in the data directory to wherever I want them backed > up. This doesn't work too well because the files are in use as I copy them. > > 2. Shutdown the database server, copy the actual files in the data > directory. I can't use this method because I can't have the database down > for the amount of time it would take to copy this data. At least, I can't > have the database server down like this on a daily basis. > > 3. Perform a database dump (using mysqldump) of the data to a file. The > problem with this is that the data is very big in that format, and the > backup takes a long time. > > 4. Perform a hot copy of the databases (using mysqlhotcopy). This also > seems to take a while, but at least the database server does not have to > shutdown. The trouble with this is that it locks the table from writes when > it does the copy. Some of my tables take a while to copy (they are hundreds > of MBs in size), and are written to quite often, so this option (while the > best so far) also has its shortcomings. > > 5. Use MySQL replication to create another database server with the > identical data. Then shutdown the database server on that machine and run a > backup (like in #2). This way will not require shutting down the real > backup server at all, so therefore the backup can take as long as I want. > The downside to this approach is that the data being backed up is not > necessarily the most up-to-date. If the backup takes 2 hours, the databases > near the end of the backup can have some significant changes by then. > However, this disadvantage is diminished because when the backup is done, I > restart the slave server and the data replicates back down again. So, we > have online database backups on the actual slave server and then we do > daily (probably could be every couple of hours) tape backups of the data on > the slave. > > Does anyone see a problem with idea #5? Does anyone have any better ideas > for performing backups? > > ____________________________________ > Dave Greco > [EMAIL PROTECTED] > Network Administrator > Gleim Publications, Inc. > (800) 87-GLEIM x312 > http://www.gleim.com > ____________________________________ > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
