We have a very large, multi-terabyte database with individual tables that are over 100Gig. We have it on a Red Hat Linux system and we set up logical volumes, take LVM snapshots, then use rsync to move the data over. This works well and is a lot faster than dumping and certainly restore is faster.
On Fri, Jan 23, 2009 at 3: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 > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
