mysql replication skip statement. is it possible?

Question

There is a system with ROW-based replication. Yesterday i have executed a heavy statement on my master accidently and found my slaves far behind master. I have interrupted the query on master, but it was still running on slaves. So i got my slaves 15 hours behind master. I have already tried to step over one position by resetting slave and increasing MASTER_LOG_POS, but with no luck: position wasn't found, because relay log wasn't read further than a heavy query event. ``` Read_Master_Log_Pos == Exec_Master_Log_Pos ``` - Is there any way to skip the heavy query? (i don't care about data that has to be changed by query) - Is there a way to kill a query on a slave taken from relay log? - Is there a way to roll the slaves back in 1 position, remove the event from master bin-log and resume the replication?

Accepted Answer

First explore the binary logs on the master to find the SQL statement that is causing the issue, using the following on the master:

```
SHOW BINLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 200;
```

Then set the slave to only sync up to the statement before that, and then resume after the statement(s) you want to skip.

In this example we are going to skip the event in log position 100. We set the salve to sync until log position 99 and then resume from 101:

```
STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE='mysql-bin.000XXX', MASTER_LOG_POS=99;

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000XXX', MASTER_LOG_POS=101;
START SLAVE;
```

Change the master log file (mysql-bin.000XXX) and positions as required.

Warning: Skipping SQL statements on slaves will cause the data between the master and slave to be different, resulting in data integrity issues. Only do this if you fully understand what the SQL queries you are skipping do, and what any consequences could be should you resume replication.

mysql replication skip statement. is it possible?

Solution

Problem