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