List:Replication« Previous MessageNext Message »
From:Eric Bergen Date:April 23 2008 2:12am
Subject:Re: innodb replication
View as plain text  
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
Thread
innodb replicationEdson Noboru Yamada23 Mar
  • Re: innodb replicationMarcus Bointon24 Mar
  • Re: innodb replicationEd W18 Apr
    • Re: innodb replicationMarcus Bointon18 Apr
      • Re: innodb replicationEd W18 Apr
        • Re: innodb replicationMarcus Bointon18 Apr
      • RE: innodb replicationRick James19 Apr
        • Re: innodb replicationEric Bergen20 Apr
          • Re: innodb replicationAugusto Bott22 Apr
            • Re: innodb replicationMark Callaghan22 Apr
              • Re: innodb replicationAugusto Bott22 Apr
                • Re: innodb replicationEric Bergen22 Apr
              • Re: innodb replicationAugusto Bott23 Apr
                • Re: innodb replicationEric Bergen23 Apr
                  • RE: innodb replicationRick James23 Apr
                    • Re: innodb replicationMarcus Bointon23 Apr
                    • Re: innodb replicationJeremy Cole23 Apr
                      • RE: innodb replicationRick James23 Apr
                        • Re: innodb replicationJeremy Cole23 Apr
                          • RE: innodb replicationRick James23 Apr
                            • Re: innodb replicationMarcus Bointon23 Apr
                              • Re: innodb replicationEric Bergen24 Apr
                                • Re: innodb replicationMoon's Father21 May