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

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

Answer 2

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 100;

Then set the slave to only sync up to the statement before that:

STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;

When you want it to carry on replication after the bad statement (warning, this can be dangerous if the statement changed data) you can tell the slave to continue from a specific point in the masters log. To do this get the position using the first command on the master, then set the slave to go:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000663', MASTER_LOG_POS=4;
START SLAVE;

Answer 3

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 4

I found the starting the io_thread first

start slave io_thread;

and checking the relay logs with the command

SHOW RELAYLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 100;

This saved me a lot of time.

Answer 5

You can set a skip counter as follow:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE; 

To see the processlist:

mysql> show [full] processlist;
kill "number from first col";

Start slave from specific position:

START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

Ref: http://dev.mysql.com/doc/refman/5.0/en/start-slave.html