mysql replication skip statement. is it possible?

mysql replication
Volodymyr Linevych picture Volodymyr Linevych · Jul 17, 2013 · Viewed 27k times · Source

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

Flo Doe picture Flo Doe · Jul 17, 2013

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

Related questions

MySQL slave I/O thread not running

I have set up replication for MySQL server. I can connect from the slave machine to the master server using the replication user/password. I have got the slave SQL thread running, but the slave I/O thread is not …

Read More
Error: "could not initailize master info structure" while doing Master Slave Replication in MySQL

I am trying to do Master Slave Replication for MySQL. When i am typing the following command: CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=451228; …

Read More
Where can I find the file my.ini file for windows mysql server?

The same question is repeated again and again and i am another one repeating this question . I have already set up my mysql server . Its location is C:\Program Files\MySQL\MySQL Server 5.5 but i am not able to find …

Read More