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
On Fri, Jan 23, 2009 at 3:18 PM, Daevid Vincent <daevid@stripped> 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
> 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
> 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
> mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables.
> 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.
> [not loving that it's a commercial tool]
> 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?
> Some other backup solutions maybe:
> http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works
> so well, no need to improve it?)
Web developer / Database administrator