> mysqldump is viable for large backups. We use a combination of > replication, logical volume copying and mysqldump. We stop the > replicated database, create a logical copy (Almost instant), restart the > database. We start a second instance of MySQL and do a mysqldump from > that instance. This avoids any issues with table/row locking ect ect > ect. At the end we delete the logical copy. Rinse and repeat. We have a > mix of InnoDB and MyIsam tables. The only issue here is that loading the > SQL back in from the dump takes a long time. If you have a lot of > indexes it will take even longer. We have found this solution to be very > reliable and it is "free". This solution could be implemented without > the replication and would be far faster they copying the files or > dumping directly. > > I would be wary of copying innodb files. If for some reason you have to > restore the database to a different directory or different machine you > results may vary. Plus the backups will be much larger because all the > index information is included int he copy. The dumps do not include the > populated indexes so they can be smaller and more portable. dump files > gzip down to very reasonable files sizes.
I like this solution. Although nothing in life is free. The price of a replication server versus buying innobackup. A toss-up... So to make it totally free, but fast, I could stop my DB, make a lvc, start the db, then do the same as above using a 2nd db instance? That's an interesting scenario. Btw, what is a logical volume copy? Its not a physical copy? Sounds like links/aliases. But the actual data is locked in time for the copy? This solution also maximizes uptime for your replication server, which I like. The idea of it getting too far behind isn't great. So in a perfect world, would you use this replication solution with mysqldumb or the innodb backup solution, if money were no object? _______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
