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.
i like this site. i added on my bookmark, thanks for your work!
Hi Joseph, besides your method you may also try the ms-sql corrupt database recovery program, it automates the procedure of database recovery