From: Marcus Bointon
Date: April 18 2008 10:09am
Subject: Re: innodb replication
List-Archive: http://lists.mysql.com/replication/1230
Message-Id: <21391293-D6BE-4229-97B8-A6D88CDBFF50@synchromedia.co.uk>
MIME-Version: 1.0 (Apple Message framework v919.2)
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

On 18 Apr 2008, at 10:50, Ed W wrote:

> mysqldump --master-data --lock-all-tables
>
> work..?  I guess although the read lock doesn't stop innodb writing  
> and starting transactions, those transactions will be written after  
> the noted commit point in the log files?  I guess the problem is if  
> the dump reads uncommitted transaction data or the transaction can  
> commit in some way despite the read lock?

I've not tried doing it that way - if it works it's certainly a more  
elegant solution than doing the lock manually. Seems like the MySQL  
docs could use an update on this. I would expect that any uncommitted  
transaction would not appear in the dump at all, but if you did a lock  
tables it would probably prevent the transactions from completing  
until after the dump is complete, so I don't think there's any danger  
there.

> Sure - although technically then you don't have a consistent dump  
> between table types...


Sure, it's just that I don't tend to mix table types within a DB.  
Actually, I don't tend to use anything but InnoDB anyway - I find  
foreign key constraints and transactions too good to live without.

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/