List:Replication« Previous MessageNext Message »
From:Eric Bergen Date:April 22 2008 12:46am
Subject:Re: innodb replication
View as plain text  
The global read lock is enough to prevent sql from executing that can
modify tables. Commit is blocked and write queries are blocked. This
doesn't mean it's safe to copy innodb data files from under mysql.
Even with the global read lock held innodb can still modify the data
files. If you cp files while innodb is modifying them you get
different parts of the file from different times and a corrupt backup.
Things like flush tables with read lock; lvm snapshot; work because
innodb always keeps it's data files in a consistent state on disk. An
atomic snapshot will work where a plain cp won't.

I double checked the innodb code, it doesn't check
LOCK_global_read_lock anywhere.

On Mon, Apr 21, 2008 at 5:42 PM, Augusto Bott <augusto@stripped> wrote:
> Hi Mark!
>
>  That is true - the InnoDB file threads can possibly keep working in
>  the background.
>  However, the global read lock /should/ prevent any transactions from
>  committing and modifying datafiles with new data while the lock is
>  held (at least, according to the docs).
>
>  --
>  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
>
>



-- 
Eric Bergen
eric.bergen@stripped
http://www.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