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?

Answer 1

For those on Amazon RDS MySQL you can skip one error at a time on the slave with:

CALL mysql.rds_skip_repl_error;

No need to stop replication before running this.

Answer 2

Try the following on the slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

This will stop the slaves threads and skips the next event from the master. This you normally use when you have problems with statements to skip over them.

Also read following part of the mysql docs: set-global-sql-slave-skip-counter