Skip duplicate entries in a slave

The following one liner helps to sync a slave that is facing duplicate entry errors.

while [ 1 ]; do if [ `mysql -u root -e "show slave status \G;" | grep "Duplicate entry" | wc -l` -eq 1 ] ; then mysql -u root -e “stop slave; set global sql_slave_skip_counter=1; start slave;”; fi; sleep 1; mysql -u root -e “show slave status\G”; done

One of our peer groups had a badly configured setup where they were writing to 2 masters. Both the masters were setup with circular replication. They were also having a corrupted binlog event in the relay log.

To fix that, had to run a change master command to use the logfile listed under relay_master_log_file and the log position under exec_master_log_pos in show slave status.

One Response

  1. Great post. I had to make a couple tweaks to get it to work on my install of CentOS 5.2 running mysql-5.0.58, but you saved me bundles of time. I’ve included my modified version of your code in case it may help others:

    while [ 1 ]; do if [ `mysql -e "show slave status\G" | grep "Duplicate entry" | wc -l` -eq 1 ]; then mysqladmin stop-slave; mysql -e “set global sql_slave_skip_counter=1″; mysqladmin start-slave; fi; sleep 7; done

    Caveats are that you need to modify the mysql statements for your environment and adjust the sleep setting to a number you feel is sane.

Leave a Reply