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.
Filed under: mysql | Tagged: bash, corruption, mysql, relay log, replication, sync
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.