List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:October 16 2012 7:55pm
Subject:Re: Odd Behavior During Replication Start-Up
View as plain text  
load data from master never worked for innodb.

On 2012-10-16 3:52 PM, "Tim Gustafson" <tjg@stripped> wrote:

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 inn...
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.


> You can do replication flawlessly for InnoDB tables without stopping
> master at all.
>
> what yo...
All total, we have approximately 125GB of MySQL databases.  That
command would take hours to run.  During that time, no new
transactions could be committed to any of our databases, and
performance for read-only queries would be seriously affected.
Further, we have a combination of MyISAM and InnoDB databases and
tables, and the --single-transaction parameter to mysqldump does not
lock MyISAM tables.  There used to be a MySQL command that basically
did all that in one statement ("LOAD DATA FROM MASTER"), but they
dropped it because of the difficulties in getting all the master data
that way.


-- 

Tim Gustafson
tjg@stripped
831-459-5354
Baskin Engineering, Room 313A

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: ht...

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