Skip navigation links

MySQL Forums :: InnoDB :: FLUSH TABLES WITH READ LOCK does *NOT* work with InnoDB


Advanced Search

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
FLUSH TABLES WITH READ LOCK does *NOT* work with InnoDB 2920 Dave Juntgen 11/23/2007 12:15PM
Re: FLUSH TABLES WITH READ LOCK does work with InnoDB 5193 Eric Bergen 11/23/2007 02:31PM
Re: FLUSH TABLES WITH READ LOCK does work with InnoDB 2521 Dave Juntgen 11/23/2007 10:29PM
Re: FLUSH TABLES WITH READ LOCK does work with InnoDB 2106 Eric Bergen 11/27/2007 08:22PM


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.