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

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