List:Replication« Previous MessageNext Message »
From:Rick James Date:April 18 2008 11:14pm
Subject:RE: innodb replication
View as plain text  
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
> 
> 
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