Activities

December 2012
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

MySQL : How to skip Mysql replication errors

sometimes MySQL replication may cause to broken for some reasons. Most of the time it’s either application errors due to poor table normalization or any direct table edit which done by the programmer.

One of the servers has got frequent this type of errors ( it is not replication errors) which shows poor slave health. So we need to analyse the problematic table.

  Last_Error: Error 'Duplicate entry '109004' for key 'PRIMARY'' on query

What I used to do, run a table row count for both master and slave. Then skip this error by executing following commands

 mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

This is skip the problematic binary log and try to execute next log position and then continue. So that you will see the following errors on screen.

I’d also seen adding slave-skip_errors in “/etc/my.cnf” and they think it’s safe. But don’t expect one of these options are safe which will reduce the replication integrity.

few known error log numbers are shown below,

#slave-skip-errors=all
#slave-skip-errors=1054
#slave-skip-errors=1051
#slave-skip-errors=1146
#slave-skip-errors=1452
#slave-skip-errors=1062

This would required MySQL restart so it is not advisable unless you know what exactly you are doing with.

enjoy

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>