Daevid, another idea is to consider InnoDB Hot Backup. While it is not
free (perhaps NOT the most important point!), it does have the ability to do
online ("hot" backups) of InnoDB tables while they are being used and
updated. It also backs up MyISAM tables, with a minimal period of time
when they cannot be accessed. The backups are consistent ... that is, they
relate to a particular point in time.
Since the backup created of one database is not going to be from the same
point in time as others, you can address this on recovery ... restore all
three databases from the closest matching backups, and roll forward to the
present (or to a point in time you like after the end of the backup).
From your description of your needs, InnoDB seems capable of addressing your
requirement ... allowing you to have consistent backups while not locking
out the updates during the backup.
See the InnoDB website for more details: http://www.innodb.com/hot-backup.
Regards,
Ken
----- Original Message -----
From: "Baron Schwartz" <[EMAIL PROTECTED]>
To: "Daevid Vincent" <[EMAIL PROTECTED]>
Cc: "'MySQL General'" <mysql@lists.mysql.com>
Sent: Monday, July 30, 2007 7:20 PM
Subject: Re: Best way to backup three live databases
<div class="moz-text-flowed" style="font-family: -moz-fixed">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.
</div>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
