List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 30 2007 11:52pm
Subject:Best way to backup three live databases
View as plain text  
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.


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