FLUSH TABLES WITH READ LOCK does work consistently on MyISAM and my
experience confirms this. I do remember reading something on this
list eons ago that asserted that it is not necessarily effective on
InnoDB due to it's multi-versioning.. uncommited transactions might be
caught in an inconsistent state.
In one extreme instance, having a few terabytes of data across several
instances (on distinct hosts), I was required to do a full-refactoring
data migration with an absolute limitation on allowable downtime.
Among the technique which I used (and I can't take credit for this
one) was to use rsync on the live server for innodb files (this phase
took a very long time, but did not interfere with operations). The
result of this phase was, as you would expect, a set a seriously
broken files which were notheless very similar to the correct files.
When that phase was complete, I shut the server down and did another
rsync. It required perhaps a minute or 2, but the result was 100%
clean innodb data files which satisfied my downtime limitations.
FLUSH TABLES WITH READ LOCK might suffice if all transactions are
completed/rolled-back but I would stil advise that you scan SHOW
ENGINE INNODB STATUS but I would carefully experiment with that.
As for maat-kit, don't let the disclaimers discourage you. If you
read the disclaimers carefully on any product (at least those released
with the benefit(?) of legal advice), you would have a hard time
trusting any of it with your enterprise. The maat-kit team (and Baron
Schwartz in particular) and quite simply the *best* MySQL engineering
team out there, with the possible exception of the vendor. I would
not hesitate to trust them with my data.
- michael dykman
On Fri, Jan 28, 2011 at 11:04 AM, Robinson, Eric
>> You need to quiesce the InnoDb background threads. One
>> technique is mentioned here:
> Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES
> WITH READ LOCK is sufficient to quiesce the InnoBD background threads
> per Shawn's message above?
> Eric Robinson
> Disclaimer - January 28, 2011
> This email and any files transmitted with it are confidential and intended solely for
> mysql@stripped,Shawn Green (MySQL). If you are not the named addressee you should
> not disseminate, distribute, copy or alter this email. Any views or opinions presented in
> this email are solely those of the author and might not represent those of Physicians'
> Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or
> Physician Select Management has taken reasonable precautions to ensure no viruses are
> present in this email, the company cannot accept responsibility for any loss or damage
> arising from the use of this email or attachments.
> This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
- michael dykman
May the Source be with you.