List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:July 31 2007 2:20am
Subject:Re: Best way to backup three live databases
View as plain text  
Hi,

The best two suggestions I can give you are LVM snapshots for binary 
backups, or using replication so you can back up the slave.  Since you 
have InnoDB tables, my personal opinion is the safest way to get a good 
LVM snapshot is to shut down the server and take the snapshot; you can 
then bring it back up while the backup proceeds.  However, see Peter 
Zaitsev's article on LVM backups for other opinions on this 
(mysqlperformanceblog.com).

Either way has downsides, it's true.  The downside of using a slave is 
you have to do table checksums if you really want to be sure the slave 
has the same data as the master.  http://mysqltoolkit.sourceforge.net/

Baron

Daevid Vincent wrote:
> We've got a production system with three databases.  The three databases
> together represent one logical set of data.  The databases contain a
> mixture of MyISAM and InnoDB tables.
> 
> What is the best way to backup the entire system (i.e. all three
> databases) to ensure that I get a coherent snapshot of the data?  Since
> this is a production system, it's not desirable to block access or
> shutdown the MySQL service while the backup runs.  Am I stuck with using
> mysqldump's --lock-all-tables option?
> 
> The issue with that is, we have many services that will be spinning
> waiting to write to the DB as the tables will be locked. On large sets,
> (MB and GB) this can take a while, and the issues then compound. If we
> lock a single table, then we have integrity issues as some tables will
> have data and others will be locked.
> 
> AFAIK, Oracle, SQL Server and any other production quality RDBMS does
> this type of thing on live production servers without shutting down the
> database or the services.
> 
> Thx.
> 
> 
Thread
Best way to backup three live databasesDaevid Vincent31 Jul
  • Re: Best way to backup three live databasesBaron Schwartz31 Jul
    • Re: Best way to backup three live databasesKen Jacobs2 Aug