List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:October 16 2012 8:13pm
Subject:Re: Odd Behavior During Replication Start-Up
View as plain text  
On 10/16/2012 4:02 PM, spameden wrote:
> 2012/10/16 Tim Gustafson <tjg@stripped>
>
>> Thanks for all the responses; I'll respond to each of them in turn below:
>>
>>> you can not simply copy a single database in this state
>>> innodb is much more complex like myisam
>>
>> I know; that's why I rsync'd the entire /var/db/mysql folder (which
>> includes the ib_logfile and ibdata files, as well as all other
>> database and table data), not just individual databases.  I also made
>> sure that "flush tables with read lock" had been executed before
>> creating the snapshot.  The steps I followed were verbatim what the
>> MySQL documentation said to do.  The MySQL documentation even mentions
>> ZFS snapshots as an effective way to make a backup:
>>
>> http://dev.mysql.com/doc/refman/5.5/en/flush.html
>>
>>> I have to agree with Harald on this: filesystem snapshots are not an
>>> effective way to clone innodb databases.  The rsync-based method
>>> described has worked for me in large scale data situations very
>>> reliably.
>>
>> I'm confused: in the first sentence, you say snapshots are bad (which
>> directly contradicts the official MySQL documentation), and in the
>> second sentence you say rsync is good.  Why would an rsync of a file
>> system snapshot not be good enough?  By the way: I forgot to mention
>> that I also did create a snapshot when the MySQL server on db-01 was
>> actually shut down, and got the same sort of results.
>>
>
... snip ...

The part you have all missed here is this:

121016 10:40:20 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!

As InnoDB operates, it copies data in the background into the tablespace 
file(s). You cannot stop this using FLUSH TABLES WITH READ LOCK.  What 
you need to do is to wait for the database to quiesce using the 
procedure documented at the bottom of this page for using ALTER TABLE 
IMPORT TABLESPACE

http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html
###
In this context, a “clean” .ibd file backup is one for which the 
following requirements are satisfied:

     There are no uncommitted modifications by transactions in the .ibd 
file.

     There are no unmerged insert buffer entries in the .ibd file.

     Purge has removed all delete-marked index records from the .ibd file.

     mysqld has flushed all modified pages of the .ibd file from the 
buffer pool to the file.

You can make a clean backup .ibd file using the following method:

     Stop all activity from the mysqld server and commit all transactions.

     Wait until SHOW ENGINE INNODB STATUS shows that there are no active 
transactions in the database, and the main thread status of InnoDB is 
Waiting for server activity. Then you can make a copy of the .ibd file.
###

The same rules apply to performing a hot backup of the main tablespace 
file(s) as they do to backing up any individual tablespaces.

Of course, if you have achieved a clean shutdown, then the on-disk image 
is consistent and, as mentioned in other replies, your rsync will work 
just fine. Also, if you are doing the cold-shutdown method, you can 
start replication from position 4 of the next binary log created after 
you restart your master. The step to save the master's binary log 
position can be skipped if you are not trying to do a hot (or warm) 
backup.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


Thread
Odd Behavior During Replication Start-UpTim Gustafson16 Oct
  • Re: Odd Behavior During Replication Start-UpReindl Harald16 Oct
    • Re: Odd Behavior During Replication Start-UpMichael Dykman16 Oct
Re: Odd Behavior During Replication Start-UpTim Gustafson16 Oct
  • Re: Odd Behavior During Replication Start-UpMichael Dykman16 Oct
    • Re: Odd Behavior During Replication Start-UpTim Gustafson16 Oct
      • Re: Odd Behavior During Replication Start-Upspameden16 Oct
    • Re: Odd Behavior During Replication Start-UpReindl Harald16 Oct
      • Re: Odd Behavior During Replication Start-UpJohan De Meersman17 Oct
        • Re: Odd Behavior During Replication Start-UpReindl Harald17 Oct
          • Re: Odd Behavior During Replication Start-UpJohan De Meersman17 Oct
            • Re: Odd Behavior During Replication Start-UpReindl Harald17 Oct
              • Re: Odd Behavior During Replication Start-UpJohan De Meersman17 Oct
              • RE: Odd Behavior During Replication Start-UpRick James17 Oct
                • Re: Odd Behavior During Replication Start-UpReindl Harald17 Oct
                  • Re: Odd Behavior During Replication Start-UpTim Gustafson17 Oct
  • Re: Odd Behavior During Replication Start-Upspameden16 Oct
    • Re: Odd Behavior During Replication Start-UpShawn Green16 Oct