List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:February 1 2011 2:11am
Subject:Re: InnoDB and rsync
View as plain text  
I skimmed over this thread and I think I can help clarify the innodb,
rsync, and lvm situation.

The basic issue with just running rsync on the files under a running
mysqld is that the rsync
will copy different parts of files at different points in time. This
means that it could sync things
to disk before and after rsync has read that part of a file which,
when restored, will be corrupted.
This is the worst case scenario.

The next best thing is to run a FLUSH TABLES WITH READ LOCK (global
read lock) and maintain
the lock while the rsync is running. This works fine for myisam tables
because all file activity to
myisam tables is stopped while a global read lock is held.

This isn't guaranteed to work with innodb. Internally to mysql flush
tables with read lock only stops
queries from acquiring write locks which let them modify tables. This
won't make innodb hold still
for a few different reasons.

First a select query in innodb can actually modify data files. A
select on a page with unmerged
records from the insert buffer will cause innodb to merge those
records before making the page
available for selects. This causes some disk i/o. If this happens
while rsync is running the resulting
backup can be corrupted. So even while holding a global read lock and
only running selects innodb
can write data.

The master thread(s) perform background tasks such as flushing dirty
pages, merging the insert buffer
and purging old records whenever innodb feels like there is spare i/o
capacity. These threads don't
know how to hold still during a global read lock and can corrupt a
backup if it were taken with rsync.

The safest way to create a backup without using something like
XtraBackup is to get a snapshot at
the filesystem level or below that at the block device level. This is
effectively what LVM does. When
you create a LVM snapshot it freezes that device at a single point in
time. When you restore the backup
innodb goes through it's recovery procedure as if the power went out.

Some possible solutions to this were discussed on the internals list a
few years ago. I'm not sure what
has been implemented since then. The list thread is here:
http://lists.mysql.com/internals/35527

-Eric

On Fri, Jan 28, 2011 at 1:59 PM, Robinson, Eric <eric.robinson@stripped> wrote:
>> * flush atbles
>> * rsync while mysqld is running
>> * stop mysqld
>> * second rsync
>>
>
> Unless we can verify 100% that there is a safe way to do it without
> shutting down MySQL, then I'm sure the approach you described above is
> the one we will end up with. Thanks for your input.
>
> --
> Eric Robinson
>
>
>
> Disclaimer - January 28, 2011
> This email and any files transmitted with it are confidential and intended solely for
> Reindl Harald,mysql@stripped. 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
>
>



-- 
Eric Bergen
eric.bergen@stripped
http://www.ebergen.net
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