1. Log onto the slave and check slave status
2. If the error is due to a duplicate, see example below, you can first set skip counter to see if it skips it, if not then just delete the row on the slave and restart replication. If there are many duplicates, then use the script in part c).
a) before skipping, just check if it is the exact same row being reinserted that fails,
select * from <table> where <key field> = <see value in message>, If all the returned columns match what was being inserted it means the master has sent the records twice. This is a known intermittant bug in mySQL that was supposed to be fixed in vn 4, but still happens in vn 4 and 5. The work around is to put the following entry into the my.cnf on the slave to automatically skip these duplicates.
[mysqld]
slave-skip-errors = 1062
..and restart the server.
b) try skipping one row manually as in this example:
ERROR :
Subject: mySQL error on mk-lluprequal-2
080130 11:57:39 [ERROR] Slave: Error 'Duplicate entry '7851915' for key 1' on query. Default database: 'prequal'. Query: 'insert into checker_requests (api_version, package, cli, ip, ts) values ('1.7','btv9','01793762637','195.224.207.200',now())', Error_code: 1062 080130 11:57:39 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000042' position 771163277
Steps taken :
Show slave status \G
Stop slave;
set global sql_slave_skip_counter=1;
start slave;
Show slave status \G
and repeat until Show slave status \G shows that there are no more errors. Also move the error log.
If the error is still there, then try deleting it as follows:
b) try skipping as in this example:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mk-fusion-1.uk.intranet
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000038
Read_Master_Log_Pos: 87465725
Relay_Log_File: mk-fusion-2-relay-bin.000002
Relay_Log_Pos: 84940721
Relay_Master_Log_File: mysql-bin.000038
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry 'oxythony77@tiscali.co.uk' for key 1' on query. Default database: 'WALLEDGARDEN'. Query: 'insert into USERS(`DIALLEDNUMBER`,`USERNAME`,`DATE`,`SERVICEID`) values('NONE','oxythony77@tiscali.co.uk','2008-01-17 17:26:19','2')'
Skip_Counter: 0
Exec_Master_Log_Pos: 84983826
Relay_Log_Space: 87426970
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Using this example, issue the following commands
· use WALLEDGARDEN;
· delete from USERS where username = 'oxythony77@tiscali.co.uk';
· stop slave;
· start slave;
· show slave status \G
If you get more than 1 duplicate, repeat the delete with the new value and stop / restart the slave again. Continue until show slave status shows no more duplicates
c) Script to remove many duplicates
on msh/bth where sockets are used:
#!/bin/sh
Slave_SQL_Running=`/slave/mysql-bin/mysql/bin/mysql -uroot -p*** -S /tmp/mysql-slave.sock -e "SHOW SLAVE STATUS\G" | grep -i Slave_SQL_Running | awk '{ p
rint $2 }'`
echo "SLAVE running-->$Slave_SQL_Running"
if [ $Slave_SQL_Running != 'Yes' ]
then
echo "mySQL not running. Stopping slave...."
/slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "STOP SLAVE";
/slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "set global sql_slave_skip_counter=1";
/slave/mysql-bin/mysql/bin/mysql -uroot -p***-S /tmp/mysql-slave.sock -e "START SLAVE";
else
echo "SLAVE running-->$Slave_SQL_Running"
fi
On most of the other databases:
[root@mk-nn-radproxy-5 ~]# vi skip_duplicate.sh
#!/bin/sh
export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/games:/usr/local/sbin:/usr/local/bin:/usr/X11R6/bin:/usr/home/hlakhan/bin
Slave_SQL_Running=`mysql -uroot -p'Bisohh!b' -e "SHOW SLAVE STATUS\G" | grep -i Slave_SQL_Running | awk '{ print $2 }'`
echo "SLAVE running-->$Slave_SQL_Running"
if [ $Slave_SQL_Running != 'Yes' ]
then
echo "mySQL not running. Stopping slave...."
mysql -uroot --p*** -e "STOP SLAVE";
mysql -uroot --p*** -e "set global sql_slave_skip_counter=1";
mysql -uroot --p*** -e "START SLAVE";
else
echo "SLAVE running-->$Slave_SQL_Running"
fi
