Tags
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.
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.
Excellent script! This really saved my skin today!
Thanks!
A better way to do this might be to add:
slave-skip-errors = 1062
to the [mysqld] section of your my.ini file.
That would be ideal when we want to skip throughout the application life span.
As Commented by Chirs,
A better way to do this might be to add:
slave-skip-errors = 1062
to the [mysqld] section of your my.ini file.
Since this script will not trigger immediately when duplicates happen – for that once we need to create trigger otherwise set an cronjob every hour.
I agree, yes using the my.cnf option would be a better alternative. But this snippet was used to skip the errors only once since the slave was out of sync. Ideally we don’t want these errors to happen unless it was by design. We would want to use the my.cnf option only when we expect duplicate errors as per design. In this case it was not so. Once this script got the slave going, we did a recovery on the slave so that it will be consistent with the master.
hi Alex. Thanks for your idea.
But, may i know where i put this script? And how to running it?
thanks
Masbro, you run that from the command line on the mysql server.
This is very neat. Thank you!