I know how you feel! I think your two best options are these: 1.) Use LVM snapshots per the MPB links you mentioned as a guide. Your incremental backup would be the binary logs that MySQL writes. You could copy any of this data off site by mounting the snapshots and using your remote copy mechanizm of choice. 2.) Create a slave and create your backups from it, again using the binary logs as the incremental backup. You could also use the snapshot technique to create the initial data set for this host if you're not able to take this host down for an extended period of time.
Claudio has an excellent point with innodb-file-per-table as well. Just make sure a single table will never grow to more than the maximum file size of your filesystem. Good luck, -Aaron On Fri, Jan 23, 2009 at 1:18 PM, Daevid Vincent <dae...@daevid.com> wrote: > We have some INNODB tables that are over 500,000,000 rows. These > obviously make for some mighty big file sizes: > > -rw-rw---- 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 > > This can take a good amount of time to copy even just the file, and a > mysqldump can take hours to export and import took 3 days (with the > usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! > > So, it's not really feasible or ideal to do this daily. We'd like to do > some kind of daily "diff" and weekly or monthly "full" backup. Also, as > any good backup strategy involves off site backups, it's not very > convenient or even feasible to transfer 80+ GB over our pipe ever day > (which according to iperf is getting around 11 MBytes/sec from our data > center to our office). > > Looking for ideas as to what others are doing to backup their very large > sets? > > We do have a master/slave setup. We're thinking of adding two more > slaves that are read-only and not accessed via the web at all. Just sits > there being a backup effectively. One being offsite in another building > and the logic that we'll trickle in maybe <100k per minute as the data > is inserted into the real M/S so that should be negligible on our > intra/internet. > > > ----------------------------------------------------------------------------------------------- > I've done some research here, but nothing stands out as "the winner"... > but I'm open to any of these ideas if you can make a strong case for > them. > > http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html > mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. > > http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html > InnoDB Hot Backup is an online backup tool you can use to backup your > InnoDB database while it is running. InnoDB Hot Backup does not require > you to shut down your database and it does not set any locks or disturb > your normal database processing. InnoDB Hot Backup is a non-free > (commercial) add-on tool with an annual license fee per computer on > which the MySQL server is run. > http://www.innodb.com/hot-backup/ > [not loving that it's a commercial tool] > > > http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html > > http://dev.mysql.com/doc/refman/5.1/en/backup.html > read the comments "You can also take very fast online or hot backups if > you have linux volume management or LVM" ... I knew there was a way to > use LVM for backups somehow. Maybe a solution for us? > http://www.mysqlperformanceblog.com/?s=backup > > http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ > > Some other backup solutions maybe: > http://www.debianhelp.co.uk/mysqlscript.htm > http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works > so well, no need to improve it?) > http://www.ozerov.de/bigdump.php > >
