List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:January 23 2009 9:18pm
Subject:How do you backup HUGE tables?
View as plain text  
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


Thread
How do you backup HUGE tables?Daevid Vincent23 Jan
  • Re: How do you backup HUGE tables?Baron Schwartz23 Jan
    • Re: How do you backup HUGE tables?Claudio Nanni24 Jan
  • Re: How do you backup HUGE tables?Jim Lyons24 Jan
  • Re: How do you backup HUGE tables?Aaron Blew24 Jan
Re: How do you backup HUGE tables?ceo24 Jan