List:Replication« Previous MessageNext Message »
From:Rick James Date:October 23 2012 5:25pm
Subject:RE: How to sync SLAVE to a production MASTER
View as plain text  
Replaying the binlog is very unsafe.  Consider this query:
   UPDATE tbl SET foo = foo + 1;
(in SBR mode)

If by "sync" you mean that the Slave has been running, but somehow got out of sync, then
consider Percona's  pt-table-checksum.  That will find the discrepancies, and prep for
another tool that applies them.


> -----Original Message-----
> From: Manuel Arostegui [mailto:manuel@stripped]
> Sent: Sunday, October 21, 2012 3:52 AM
> To: nik600
> Cc: replication@stripped
> Subject: Re: How to sync SLAVE to a production MASTER
> 
> 2012/10/20 nik600 <nik600@stripped>
> 
> > Dear all
> >
> > i'm planning the configuration of a replication environment on a
> mysql
> > master with more of 300 database and about 50 GB of data, the master
> > is a production server and can't be stopped or write-locked for a
> long
> > time (more than 5 minutes is a logn time in this case)
> >
> > The database contains both MyISAM and InnoDB tables.
> >
> > I've read that the LOAD-DATA-FROM-MASTER command is deprecated and
> not
> > suggested
> >
> > http://dev.mysql.com/doc/refman/5.1/en/load-data-from-master.html
> >
> > But
> >
> > - i can't run a mysqldump on the production server as it  takes about
> > 3 hours and during that time there will be write operation on it.
> > - i can't write lock tables as it will require a stop time of many
> > hours
> > - i can't use the load-data-from-master as it doesn't support InnoDB
> >
> > What happend if i force the SLAVE to re-process some bin-file already
> > processed?
> >
> > I mean:
> >
> > - take note of the current binlog on the server (example:
> > mysql-bin.004409 position x)
> > - make the mysqldump on master
> > - import the dump on slave
> > - force the slave to restart from  (example: mysql-bin.004409
> position
> > x)
> >
> > Can i do that?
> >
> > Or is there another solution?
> >
> >
> Hi,
> 
> You can either:
> 
> mysqldump -u -p DB --single-transaction --master-data=2 > foo..sql
> 
> --master-data=2 will include the binlog file and position in the
> mysqldump itself.
> 
> Or even better, you can use xtrabackup from the master. Then start
> replication from the binlog and position etc.
> 
> Manuel.
Thread
How to sync SLAVE to a production MASTERnik60020 Oct
  • Re: How to sync SLAVE to a production MASTERMarcus Bointon20 Oct
    • Re: How to sync SLAVE to a production MASTERDimitre Radoulov20 Oct
  • Re: How to sync SLAVE to a production MASTERManuel Arostegui21 Oct
    • RE: How to sync SLAVE to a production MASTERRick James23 Oct