If replication stops due to an issue with an event in a replicated transaction, you can resume replication by skipping the failed transaction on the replica. Before skipping a transaction, ensure that the replication I/O (receiver) thread is stopped as well as the SQL (applier) thread.
First you need to identify the replicated event that caused the
error. Details of the error and the last successfully applied
transaction are recorded in the Performance Schema table
You can use mysqlbinlog to retrieve and
display the events that were logged around the time of the
error. For instructions to do this, see
Point-in-Time (Incremental) Recovery. Alternatively, you can
SHOW RELAYLOG EVENTS on the
SHOW BINLOG EVENTS on
Before skipping the transaction and restarting the replica, check these points:
Is the transaction that stopped replication from an unknown or untrusted source? If so, investigate the cause in case there are any security considerations that indicate the replica should not be restarted.
Does the transaction that stopped replication need to be applied on the replica? If so, either make the appropriate corrections and reapply the transaction, or manually reconcile the data on the replica.
Did the transaction that stopped replication need to be applied on the source? If not, undo the transaction manually on the server where it originally took place.
To skip the transaction, choose one of the following methods as appropriate:
When GTIDs are in use (
ON), see Section 22.214.171.124, “Skipping Transactions With GTIDs” .
When GTIDs are not in use or are being phased in (
ON_PERMISSIVE), see Section 126.96.36.199, “Skipping Transactions Without GTIDs”.
If you have enabled GTID assignment on a replication channel using the
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSoption of the
CHANGE REPLICATION SOURCE TOor
CHANGE MASTER TOstatement, see Section 188.8.131.52, “Skipping Transactions Without GTIDs”. Using
ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONSon a replication channel is not the same as introducing GTID-based replication for the channel, and you cannot use the transaction skipping method for GTID-based replication with those channels.
To restart replication after skipping the transaction, issue
REPLICA, with the
clause if the replica is a multi-source replica.