List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:January 24 2009 4:26am
Subject:Re: How do you backup HUGE tables?
View as plain text  
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 <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
> 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

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