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
