List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:January 28 2011 7:01pm
Subject:Re: InnoDB and rsync
View as plain text  
 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.robinson@stripped> 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@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
 - mdykman@stripped

 May the Source be with you.
Thread
Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesReindl Harald21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesMySQL)21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesJoerg Bruehe24 Jan
      • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
        • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
          • RE: Slow query on MySQL4 server doing simple inner join of twoInnoDB tablesGavin Towey25 Jan
            • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford25 Jan
  • InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMattia Merzi25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
      • Re: InnoDB and rsyncJohan De Meersman25 Jan
        • Re: InnoDB and rsyncReindl Harald25 Jan
        • RE: InnoDB and rsyncJerry Schwartz25 Jan
    • Re: InnoDB and rsyncSteve Musumeche25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
    • Re: InnoDB and rsyncJohan De Meersman26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncMichael Dykman28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncEric Bergen1 Feb