MySQL 8.0 Reference Manual
MySQL 8.0 Release Notes
To skip failing transactions when GTIDs are not in use or are
being phased in (gtid_mode is
OFF, OFF_PERMISSIVE, or
ON_PERMISSIVE), you can skip a specified
number of events by issuing a SET GLOBAL
sql_replica_skip_counter statement (from MySQL
8.0.26) or a SET GLOBAL
sql_slave_skip_counter statement. Alternatively, you
can skip past an event or events by issuing a
CHANGE REPLICATION SOURCE TO or
CHANGE MASTER TO statement to
move the source binary log position forward.
These methods are also suitable when you have enabled GTID
assignment on a replication channel using the
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
option of the CHANGE REPLICATION SOURCE
TO or CHANGE MASTER
TO statement.
When you use these methods, it is important to understand that you are not necessarily skipping a complete transaction, as is always the case with the GTID-based method described previously. These non-GTID-based methods are not aware of transactions as such, but instead operate on events. The binary log is organized as a sequence of groups known as event groups, and each event group consists of a sequence of events.
For transactional tables, an event group corresponds to a transaction.
For nontransactional tables, an event group corresponds to a single SQL statement.
A single transaction can contain changes to both transactional and nontransactional tables.
When you use a SET GLOBAL
sql_replica_skip_counter or SET GLOBAL
sql_slave_skip_counter statement to skip events and
the resulting position is in the middle of an event group, the
replica continues to skip events until it reaches the end of
the group. Execution then starts with the next event group.
The CHANGE REPLICATION SOURCE
TO or CHANGE MASTER
TO statement does not have this function, so you
must be careful to identify the correct location to restart
replication at the beginning of an event group. However, using
CHANGE REPLICATION SOURCE TO or
CHANGE MASTER TO means you do
not have to count the events that need to be skipped, as you
do with SET GLOBAL sql_replica_skip_counter
or SET GLOBAL sql_slave_skip_counter, and
instead you can just specify the location to restart.
When you have assessed the failing transaction for any other
appropriate actions as described previously (such as
security considerations), count the number of events that
you need to skip. One event normally corresponds to one SQL
statement in the binary log, but note that statements that
use AUTO_INCREMENT or
LAST_INSERT_ID() count as two events in
the binary log. When binary log transaction compression is
in use, a compressed transaction payload
(Transaction_payload_event) is counted as
a single counter value, so all the events inside it are
skipped as a unit.
If you want to skip the complete transaction, you can count
the events to the end of the transaction, or you can just
skip the relevant event group. Remember that with
SET GLOBAL sql_replica_skip_counter or
SET GLOBAL sql_slave_skip_counter, the
replica continues to skip to the end of an event group. Make
sure you do not skip too far forward and go into the next
event group or transaction so that it is not also skipped.
Issue the SET statement as follows, where
N is the number of events from
the source to skip:
SET GLOBAL sql_slave_skip_counter = N
Or from MySQL 8.0.26:
SET GLOBAL sql_replica_skip_counter = N
This statement cannot be issued if
gtid_mode=ON is set, or if
the replication I/O (receiver) and SQL (applier) threads are
running.
The SET GLOBAL sql_replica_skip_counter
or SET GLOBAL sql_slave_skip_counter
statement has no immediate effect. When you issue the
START REPLICA statement for
the next time following this SET
statement, the new value for the system variable
sql_replica_skip_counter or
sql_slave_skip_counter is
applied, and the events are skipped. That
START REPLICA statement also
automatically sets the value of the system variable back to
0. If the replica is a multi-source replica, when you issue
that START REPLICA statement,
the FOR CHANNEL clause is required. Make
sure that you name the correct channel, otherwise events are
skipped on the wrong channel.
When you have assessed the failing transaction for any other appropriate actions as described previously (such as security considerations), identify the coordinates (file and position) in the source's binary log that represent a suitable position to restart replication. This can be the start of the event group following the event that caused the issue, or the start of the next transaction. The replication I/O (receiver) thread begins reading from the source at these coordinates the next time the thread starts, skipping the failing event. Make sure that you have identified the position accurately, because this statement does not take event groups into account.
Issue the CHANGE REPLICATION SOURCE
TO or CHANGE MASTER
TO statement as follows, where
source_log_name is the binary log
file that contains the restart position, and
source_log_pos is the number
representing the restart position as stated in the binary
log file:
CHANGE MASTER TO MASTER_LOG_FILE='source_log_name', MASTER_LOG_POS=source_log_pos;
Or from MySQL 8.0.24:
CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='source_log_name', SOURCE_LOG_POS=source_log_pos;
If the replica is a multi-source replica, you must use the
FOR CHANNEL clause to name the
appropriate channel on the CHANGE
REPLICATION SOURCE TO or
CHANGE MASTER TO statement.
This statement cannot be issued if
SOURCE_AUTO_POSITION=1 or
MASTER_AUTO_POSITION=1 is set, or if the
replication I/O (receiver) and SQL (applier) threads are
running. If you need to use this method of skipping a
transaction when SOURCE_AUTO_POSITION=1
or MASTER_AUTO_POSITION=1 is normally
set, you can change the setting to
SOURCE_AUTO_POSITION=0 or
MASTER_AUTO_POSITION=0 while issuing the
statement, then change it back again afterwards. For
example:
CHANGE MASTER TO MASTER_AUTO_POSITION=0, MASTER_LOG_FILE='binlog.000145', MASTER_LOG_POS=235;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
Or from MySQL 8.0.24:
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=0, SOURCE_LOG_FILE='binlog.000145', SOURCE_LOG_POS=235;
CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=1;