Duplicate entry in MySQL slave

Question

I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query.

But when I tried to delete the entry from that table result shown as 0 rows affected.

I tried to fetch the record using select statement. Even then the result shown as 0 rows affected.

Then I checked slave status again. The same state persists and the same error appears.

Answer 1

First, let me start with a warning. Be very careful when you start messing with the data on the slave. It can really ruin the integrity of the replication setup.

That being said, if you are absolutely certain you don't want the slave to execute that particular statement, you can issue the following commands on the slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

This will skip the next statement in the replication log and move on. Here's the documentation for reference.