List:Replication« Previous MessageNext Message »
From:Augusto Bott Date:April 23 2008 12:22am
Subject:Re: innodb replication
View as plain text  
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@ ?)

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?

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
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