Mysql replication. Is it possible?

Question

There is a ROW based replication system. Yesterday I executed a heavy expression about my master by accident and found my slaves far behind the master. I interrupted the request for the master, but it still worked for subordinates. So I got my slaves 15 hours behind the master.

I already tried to step over one position by dropping the slave and increasing MASTER_LOG_POS, but no luck: the position was not found because the relay log was not read more than the heavy request event.

Read_Master_Log_Pos == Exec_Master_Log_Pos 

• Is there a way to skip a heavy request? (I do not need data that needs to be changed upon request)
• Is there a way to kill a slave request taken from a relay log?
• Is there a way to move the slaves back to position 1, delete the event from the main bin log, and resume replication?

Answer 1

First, examine the wizard's binary logs to find the SQL statement that is causing the problem using the following wizard command:

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

Then install the slave only to synchronize with the instructions before:

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

If you want it to replicate after a bad statement (warning, this can be dangerous if the operator changes the data), you can tell the slave to continue working from a certain point in the wizard log. To do this, use the first command on the master, then install the slave:

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

Answer 2

Try the following on a slave:

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

This will stop the slave threads and skip the next event from the master. This is what you usually use when you have problems with operators to skip them.

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

Answer 3

I started running io_thread first

 start slave io_thread; 

and checking relay logs with the command

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

It saved me a lot of time.

Answer 4

You can set the skip counter as follows:

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

To view a list of processes:

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

Run the slave from a specific position:

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

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

Answer 5

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

 CALL mysql.rds_skip_repl_error; 

No need to stop replication before starting.