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?

Enwomb

Answer 1

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.

Postrider

SHOW BINLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 200; saved me, when you open the binary log file located at C:\ProgramData\MySQL\MySQL Server 8.0\Data\...-bin.000086 you have not too much information about the problem. When I executed this command I say there was a table with a problem being executed in a loop, that was why the binlog file was increasing 1MB each second. – Basaltware

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

Shum

Just a note for multi-source replications: you need to stop all slaves; then set this skip-counter; then run just the slave which needs to be skipped e.g. START SLAVE FOR CHANNEL "source_1";; then you can check that it got skipped via SHOW VARIABLES LIKE 'slave_max_allowed_packet'; it must be 0, then you can start other slaves START SLAVE; – Ferrotype

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.

Rabble

Answer 4

You will get this error if you have GTID_MODE enabled:

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

You can generate empty transaction using this method.

1. Find faulty GTID:

show slave status

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 4 failed executing transaction '47f34807-98ef-11ee-ac0c-d00d1ad56dd7:409671' at master log mysql-bin.000033, end_log_pos 11323712. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

2. Stop slave and replace the faulty GTID:

STOP SLAVE;
SET GTID_NEXT="47f34807-98ef-11ee-ac0c-d00d1ad56dd7:409671";
BEGIN; COMMIT;

3. Set back GTID_NEXT and start the slave

SET GTID_NEXT="AUTOMATIC";
START SLAVE;

Frontality

Answer 5

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.

Offset

Answer 6

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

Flabellate