MySql Replication: Skip Duplicate Key Error (Error 1062, Unique Constraint Violation)

MySQL replication will stop whenever there is a query causing an error on the slave. The error message will be written to the error log; therefore, it allows us to identify the problem and fix it,

One of the common error is error 1062, unique constraint violation, that is, when a duplicate key is being insert into a table. For example you might see an errors message in the error log or from “show slave status” output:

"1062 Error 'Duplicate entry 'abc' for key 1' on query. Default database: 'mydb'. Query: 'INSERT INTO ..."

Since, in most case, this error will need to fix in the source (master) database, we can skip the error and let the replication continue on slave.

To skip an error , from the slave, you can use the SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1 statement to skip a sql statement which caused the 1062 error.

mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

For, 1062 error, during the normal replication process, you can also specify the following variable in the
my.cnf configuration file to tell the slave to skip all 1062 errors

slave-skip-errors = 1062

Of cause, please make sure that all the statements, which were skipped, will eventually be corrected in the master and will not cause any data issue.