Re-syncing master-slave after a broken replication

Hi everyone.We are managing an OLTP system.We are doing master-slave replication on MySQL server 5.5.24.We are yet to go live.The replication has been setup and is working.I would like to know in case the replication breaks,how can i fix it and re-sync data between the master and the slave server.We don't want to take data dumps and put it from master to slave.Please suggest a proper way to do it.I believe there might be better ways than taking dumps and importing it.Thanx.

 

you can try this on your slave database:

mysql>stop slave;
mysql>load data from master;
mysql>start slave;

Code (markup):

To do this you need to grant reload privileges to your replication user.

it worked on my test system and i am yet to go live so i am not totally sure about large database...!!

regards,

--amrush

 

Hey thnx for the reply....Does "load data...." works on mysql-server- 5.5.I doubt it.

 

Ya dude it doesn't work.....please check this link... http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html

 

My test system is 5.0 and it works there and didn't test it on 5.5...and also thanks for the link....if you find any solution please share...i am also looking for it....!

regards,

--amrush

 

Have not found anything as of now.... mysqldump is the only option as of now.I have been taking and putting dumps from master to slave(in test environment) but i regularly encounter error# 1062 (related to duplicate value entry in Primary key column).However there is nothing wrong as such.There are no duplicate value inserts anywhere.Because of the error the replication breaks frequently and i have to set it up again. Am really worried.Need to find a solution for this.

 

I have tried another way to resync slave with master....

first stop slave....then i ensure that all my binlog in master db is ok...then check error log of slave to find out the binlog number exact point of failure....then used "change master" command on slave db...then start slave....it resync data with the master....i did this is in my test environment...!!

regards,

--amrush

 

thank you for this...But i have not enabled standard logging for mysql servers(both master and slave) because of some reasons.We can't enable logging...So i guess another dead-end for me