List:Replication« Previous MessageNext Message »
From:Manuel Arostegui Date:October 21 2012 10:51am
Subject:Re: How to sync SLAVE to a production MASTER
View as plain text  
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