MySQL Forums
Forum List  »  InnoDB

Re: FLUSH TABLES WITH READ LOCK does work with InnoDB
Posted by: Eric Bergen
Date: November 23, 2007 02:31PM

Hi Dave,

The reason it doesn't work for InnoDB tables is that while flush tables with read lock does prevent queries from accessing InnoDB it doesn't prevent InnoDB from modifying it's own data files.

If you are using lvm it's possible to take a snapshot of the datadir, move it, and use replication to sync the two, then failover to the new daemon. There is a fair amount of plate spinning involved in that.

LVM works because it takes an atomic snapshot of the filesystem. Using mv doesn't work because it copies files while InnoDB is moving them. InnoDB can be modifying it's data files when no queries are running due to things like the secondary key insert buffer and reclaiming deleted records.

It sounds like you should setup a second master that you can failover to in order to perform maintenance on your primary master.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: FLUSH TABLES WITH READ LOCK does work with InnoDB
6992
November 23, 2007 02:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.