Repair MySQL Replication by SQL_SLAVE_SKIP_COUNTER

If you set up MySQL replication, you probably have ever faced this problem. there are invalid MySQL queries which cause the replication not to work anymore. Here I’ll explain how you can repair the replication on MySQL slave without rebuilding the whole repication environment. There are two steps here:

1 – Identifying The Problem

To find out whether replication is working or not, and check mysql logs
to make sure what caused it to stop. On Debian, MySQL logs to
/var/log/syslog:

admon:/home/admin# grep mysql /var/log/syslog
server1:/home/admin# grep mysql /var/log/syslog
Jun 29 09:16:08 http2 mysqld[14380]: 080629 9:16:08 [ERROR] Slave: Error
'Table 'mydb.taggregate_temp_1212347876' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212347876 AS aggregate
Jun 29 09:16:08 http2 mysqld[14380]: ^ISET thread.views = thread.views + aggregate.views
Jun 29 09:16:08 http2 mysqld[14380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146
Jun 29 09:16:08 http2 mysqld[14380]: 080629 9:56:08 [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.001079' position 203015142

Now, you can see the query that caused the error, and the stop position. To determine the exact query:

mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
....
Last_Errno: 1146
Last_Error: Error 'Table 'mydb.taggregate_temp_1212347876' doesn't exist' on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212347876 AS aggregate
SET thread.views = thread.views + aggregate.views
WHERE thread.threadid = aggregate.threadid'
Skip_Counter: 0
......
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
mysql>

New we are prepare to fix this issue.
2 – Repairing The Replication
we need to stop the slave first, by running:

mysql> STOP SLAVE;

To Fix the issue is quite easy. We tell the slave to simply skip the invalid SQL query here:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This tells the slave to skip one query (which is the invalid one that
caused the replication to stop). If you wanna skip two queries, you’d
use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.

Now the issue has been fixed, we can start the slave again by running:

mysql> START SLAVE;

and then,check if replication is working smoothly again.