0

I have done master-slave replication for performing web-server mirroring. Web contents are getting replicated properly. But I am facing issue in database replication. Database is replicated and slave can read the master log position as well. I have seen various blogs and saw skip errors as a solution but it is not good for our environment as it loses database consistency. I have tried making slave read-only by using read-only=1 in slave database my.cnf but it didn't work. I am just wondering how database table data are getting duplicated even after doing fresh replication.

Error:

Last_Error: Error 'Duplicate entry '155251' for key 'PRIMARY'' on query. Default database:...............

1

This message means that the entry you try to insert is already present on the slave.

First of all, look at the master log files if the error exists. If this is the case, the problem is not at the replication level.

So, if the error is not present on the master, you should repeat the cycle of dump/restore.

To ensure consistency, you need to stop writing ( SET GLOBAL read_only = 1 ;) on the master while you dump the whole databases. When dump is done, you can disable read_only. Make sure you include --single-transaction --add-drop-database --master-data as options to mysqldump.

--add-drop-database : drop database on the slave before restoring. So as there is nothing left that could break consistency

--master-data : include the master file and master position. No need to insert it manually

Do not forget to START SLAVE; after the restore part on the slave is done!

Hope this will helps ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.