Mysql主从同步出错
Last_Error: Query caused different errors on master and slave. Error on master: ‘Deadlock found when trying to get lock; try restarting transaction’ (1213), Error on slave: ‘no error’ (0). Default database: ‘…’. Query: ‘INSERT INTO …’
Last_Error: Error ‘Duplicate entry ‘…’ for key 1′ on query. Default database: ‘…’. Query: ‘…’
遇到如上情况报错可用以下方式解决
(从DB上运行)
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; #从DB运行,用来跳过1个事件,只有当同步进程出现错误而停止的时候才可以执行.
slave start;
show slave statue\G;
如果同步还有报错就继续重复以上三步操作直至无报错.
自动脚本
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 |
另外,最后没有报错,但slave_io_running no的话,先slave stop;slave start;还是no的话就只能通过重导数据来重做同步了.