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 <eric.robin...@psmnv.com> wrote: >> You need to quiesce the InnoDb background threads. One >> technique is mentioned here: >> http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp > aces.html > > > 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@lists.mysql.com,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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
