List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 26 2011 3:59am
Subject:Re: InnoDB and rsync
View as plain text  
On 1/25/2011 10:45, Robinson, Eric wrote:
>>> There is a very good reason: it is the phenomenon of row drift. The
>>> master and slave can appear to be in good sync, but often it is not
>>> actually the case.
>>
>> ... sounds interesting; have you got any document explaining
>> this phenomenon? AFAIK, the things that (silently) break
>> replication are:
>> - non-deterministic functions in statement-based replication
>> - hand-made updates on the slave db
>> is this enough to justify a *daily* resync?!
>
>
> I'm definitely no expert on this. All I know is that we used to
> frequently experience situations where queries to the slaves would
> return different recordsets than the same queries to the masters. Yet by
> all other indications the servers were in sync. All the replication
> threads were running and the row counts were identical, but the data in
> the rows was sometimes different. I asked about this in the list and the
> answers I got back were that the phenomenon was called row drift and was
> fairly well known and not always easy (or sometimes even possible) to
> eliminate because of bad programming practices in some off-the-shelf
> applications. At that time, the consensus in the list was that it was
> not safe to trust replication slaves for backup purposes. That's when I
> came up with the idea of doing an rsync every night, which creates a
> slave that is 100% reliable for using as a backup source and also
> eliminates problems with row-drift. Since we started using that
> technique, we don't get calls from users complaining that their reports
> are showing bogus totals and such.
>

I suspect that your queries were not as deterministic as you thought 
they were. Do you have a sample of a query that produced different 
results between the master and the slave? We shouldn't need the results, 
just the query.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
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