From: Manuel Arostegui Date: October 21 2012 10:51am Subject: Re: How to sync SLAVE to a production MASTER List-Archive: http://lists.mysql.com/replication/2404 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary=0015175cd2a602e78d04cc8f873b --0015175cd2a602e78d04cc8f873b Content-Type: text/plain; charset=ISO-8859-1 2012/10/20 nik600 > 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. --0015175cd2a602e78d04cc8f873b--