Consistent state, yes. But what about mid-transaction? Might it be that the snapshot
will have half the transaction and have to ROLLBACK, while the live machine (after
UNLOCK) will COMMIT the transaction?
> -----Original Message-----
> From: eric.bergen@stripped
> [mailto:eric.bergen@stripped] On Behalf Of Eric Bergen
> Sent: Tuesday, April 22, 2008 7:12 PM
> To: augusto@stripped
> Cc: replication@stripped
> Subject: Re: innodb replication
>
> Innodb always keeps it's files in a consistent state on disk. Power
> failure and snapshots of the system won't result in a corrupt data
> file. You can find more details on how innodb achieves this here:
> http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/
>
> More replies inline.
>
> On Tue, Apr 22, 2008 at 5:22 PM, Augusto Bott
> <augusto@stripped> wrote:
> > Hi Mark, Eric!
> >
> > The last comments on this thread got me thinking about all those
> > backup chalenges... since a FLUSH TABLES won't stop internal InnoDB
> > threads from working and changing the datafiles in the
> background, how
> > can we be absolutely sure that a filesystem snapshot will
> be actually
> > consistent when it's taken? What if when we're taking that
> snapshot in
> > the precise moment when InnoDB threads are working and
> modifying data?
> > How can we be sure that the snapshot will not be taken on
> the precise
> > moment there's a write in place?
> >
> > I must acknowledge under this scenario, taking a 'instant' snapshot
> > from the filesystem will greatly reduce the probability of
> corruption
> > but... any ideas on how to get it 100% safe? (hm...
> perhaps we should
> > discuss this on internals@ ?)
>
> See the notes above on why the snapshot is guaranteed not to
> be corrupt.
>
> >
> > On the other hand, we're talking about warm backups on a
> slave, right?
> > So... what if we stop replication, issued a flush tables,
> waited for a
> > moment until IO operations stop, and then start copying
> the datafiles
> > or taking that snapshot?
>
> You may as well just shutdown the slave. It's safer than trying to
> determine if innodb isn't going to write something.
>
> >
> > Anyway... I don't recall having experienced corruption
> when restoring
> > from a warm backup (ever), but that may be that I just
> "got lucky" or
> > didn't look into the data deep enough to actually find any problems
> > :-)
> >
> > Cheers!
> >
> > --
> > Augusto Bott
> >
> >
> >
> >
> >
> > On Mon, Apr 21, 2008 at 8:27 PM, Mark Callaghan
> <mcallaghan@stripped> wrote:
> > >
> > >
> > >
> > > On Mon, Apr 21, 2008 at 5:14 PM, Augusto Bott
> <augusto@stripped> wrote:
> > > > Hi Rick, Eric, Marcus!
> > > >
> > > > I must correct some of the things that have been said
> on this thread.
> > > > I'm not commenting on rumors, just the facts from the
> documentation
> > > > (and a few years of experience with MySQL) :-)
> > > >
> > > > If you're running your MySQL server with
> > > > innodb_flush_log_at_trx_commit=1 (that's the default),
> issuing FLUSH
> > > > TABLES WITH READ LOCK will indeed prevent modification to the
> > > > datafiles while the global read lock is held since all
> committed
> > > > transactions (so far) will be for sure on disk. The
> global read lock
> > > > stays in place until UNLOCK TABLES is issued or until
> that connection
> > > > is closed/timed out. This makes it safe to copy the
> datafiles for a
> > > > backup - the result is indeed a consistent backup.
> > >
> > > FLUSH TABLES WITH READ LOCK should allow for a snapshot
> (LVM, ZFS) to be
> > > used to copy all files -- as long as they are on the
> same filesystem. But I
> > > do not think it is safe to copy InnoDB files without a
> snapshot as the
> > > background IO threads (purge, insert buffer, page
> writer) can do IO during
> > > FLUSH TABLES WITH READ LOCK. I have not seen any code
> that makes the InnoDB
> > > background IO threads wait when there is a global read lock.
> > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > This operation can be called a 'warm backup'. A hot
> backup is what
> > > > "InnoDB Hot Backup" does: it does not lock anything
> while it's running
> > > > and transactions can start and commit while it's
> running (the tool
> > > > requires a brief global read lock at the beginning,
> when the backup is
> > > > starting) and takes a full consistent backup (you must
> license it to
> > > > use it). A cold backup would be shutting down MySQL,
> copying the
> > > > datafiles and firing it up again (also a safe move).
> > > >
> > > > The "caveat" when you make a warm backup is that the
> datafiles will be
> > > > marked 'dirty' and on the next startup, the logs will
> be replayed to
> > > > bring the database to a consistent point, since it
> "wasn't closed
> > > > properly" (to the very same point when you issued the
> FLUSH TABLES
> > > > WITH READ LOCK statement).
> > > >
> > > > --
> > > > Augusto Bott
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Sun, Apr 20, 2008 at 5:46 PM, Eric Bergen
> <ebergen@stripped>
> > > wrote:
> > > > > Hi,
> > > > >
> > > > > The flush tables with read lock issue has to do
> with copying the files
> > > > > off the disk, not with using mysqldump's sql. The
> issue is that flush
> > > > > tables with read lock is enough to prevent clients
> from modifying
> > > > > innodb but it doesn't make innodb "hold still" . It
> still has
> > > > > background threads modifying the data files. If you
> did a flush tables
> > > > > with read lock; then copied the data files they
> will almost certainly
> > > > > be corrupted because different parts of the files
> are copied at
> > > > > different times.
> > > > >
> > > > > If you use mysqldump --master-data it will turn on
> --lock-all-tables
> > > > > automatically giving you a consistent snapshot
> across all storage
> > > > > engines. The downside of this is that mysqldump has
> to hold a read
> > > > > lock the entire time it's dumping data. If you're
> only using innodb
> > > > > you can specify --master-data --single-transaction
> mysqldump will
> > > > > issue a flush tables with read lock long enough to
> copy the master
> > > > > data and start a transaction. With a transaction
> started innodb will
> > > > > only return rows that were committed before the
> transaction was
> > > > > started. Other transactions can proceed and innodb
> won't see the rows.
> > > > >
> > > > >
> > > > >
> > > > > On Fri, Apr 18, 2008 at 4:14 PM, Rick James
> <rjames@stripped>
> > > wrote:
> > > > > > Rumor has it that InnoDB used to still have some
> things in RAM after
> > > the "FLUSH TABLES WITH READ LOCK", thereby disallowing
> it as a safe way to
> > > dump InnoDB tables. Another rumor has it that that has
> been fixed. [Sorry
> > > for lack of details; hope someone will respond with details.]
> > > > > >
> > > > > > I prefer to stop mysql, copy the necessary
> directories, then restart
> > > mysql. An improvement on that is to use Snapshots (eg
> LVM on Linux). They
> > > make that extremely fast, hence very practical. (The
> copy can be done from
> > > the snapshot _after_ restarting mysql.)
> > > > > >
> > > > > >
> > > > > >
> > > > > > > -----Original Message-----
> > > > > > > From: Marcus Bointon
> [mailto:marcus@stripped]
> > > > > > > Sent: Friday, April 18, 2008 2:42 AM
> > > > > > > To: replication@stripped
> > > > > > > Subject: Re: innodb replication
> > > > > > >
> > > > > > > On 18 Apr 2008, at 10:27, Ed W wrote:
> > > > > > >
> > > > > > > >> I´m going to set up a innodb database
>
> replication, and I
> > > > > > > have some
> > > > > > > >> doubts
> > > > > > > >> about the backing up the master database
> step.
> > > > > > > >>
> > > > > > > >> Normally, I do a "FLUSH TABLES WITH READ
> LOCK", backup the data
> > > > > > > >> files (or
> > > > > > > >> use mysqldump), get the current log
> coordinates with "SHOW
> > > MASTER
> > > > > > > >> STATUS"
> > > > > > > >> and unlock the tables. Using exactly the
> same steps work for
> > > innodb
> > > > > > > >> databases?
> > > > > > > >>
> > > > > > > >> According to some texts, it seems not, but
>
> I haven´t found
> > > > > > > a clear
> > > > > > > >> cookbook.
> > > > > > > >>
> > > > > > > >>
> > > > > > > >> Just doing a "mysqldump --master-data
> --single-transaction" is
> > > > > > > >> enough to
> > > > > > > >> replace the above steps?
> > > > > > > >>
> > > > > > > >>
> > > > > > > >
> > > > > > > > How would you put these two things together
> in the case of
> > > > > > > a server
> > > > > > > > using mixed database table types for a clean
> backup that
> > > > > > > can be used
> > > > > > > > to restart synchronisation?
> > > > > > >
> > > > > > >
> > > > > > > You can't. Only InnoDB supports
> single-transaction, so though
> > > > > > > it would
> > > > > > > still generate a dump file for other table
> types, you'd have no
> > > > > > > assurance that they are clean because writes could
> be
> > > > > > > happening as you
> > > > > > > do it. This is one of the advantages of using
> innodb.
> > > > > > >
> > > > > > > You can of course do separate dumps for other
> DBs, and because
> > > that
> > > > > > > wouldn't need to include the innodb tables, it
> would at least
> > > reduce
> > > > > > > the time that your tables are locked.
> > > > > > >
> > > > > > > You could also look at maatkit's parallel
> dump/load scripts.
> > > > > > >
> > > > > > > Marcus
> > > > > > > --
> > > > > > > Marcus Bointon
> > > > > > > Synchromedia Limited: Creators of
> http://www.smartmessages.net/
> > > > > > > UK resellers of info@hand CRM solutions
> > > > > > > marcus@stripped |
> http://www.synchromedia.co.uk/
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > > MySQL Replication Mailing List
> > > > > > > For list archives:
> http://lists.mysql.com/replication
> > > > > > > To unsubscribe:
> > > > > > >
> http://lists.mysql.com/replication?unsub=1
> > > > > >
> > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > --
> > > > > > MySQL Replication Mailing List
> > > > > > For list archives: http://lists.mysql.com/replication
> > > > > > To unsubscribe:
> > > http://lists.mysql.com/replication?unsub=1
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > Eric Bergen
> > > > > eric.bergen@stripped
> > > > > http://www.provenscaling.com
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > MySQL Replication Mailing List
> > > > > For list archives: http://lists.mysql.com/replication
> > > > > To unsubscribe:
> > > http://lists.mysql.com/replication?unsub=1
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > >
> > > > >
> > > >
> > > > --
> > > > MySQL Replication Mailing List
> > > > For list archives: http://lists.mysql.com/replication
> > > > To unsubscribe:
> > > http://lists.mysql.com/replication?unsub=1
> > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Mark Callaghan
> > > mcallaghan@stripped
> >
> > --
> >
> > MySQL Replication Mailing List
> > For list archives: http://lists.mysql.com/replication
> > To unsubscribe:
> http://lists.mysql.com/replication?unsub=1
> >
> >
>
>
>
> --
> high performance mysql consulting.
> http://provenscaling.com
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/replication?unsub=1
>
>
