2.7.3 Skipping Transactions

2.7.3.1 Skipping Transactions With GTIDs

2.7.3.2 Skipping Transactions Without GTIDs

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 thread is stopped as well as the replication SQL 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 replication_applier_status_by_worker. 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 issue SHOW RELAYLOG EVENTS on the replica or SHOW BINLOG EVENTS on the source.

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 (gtid_mode is ON), see Section 2.7.3.1, “Skipping Transactions With GTIDs” .

• When GTIDs are not in use or are being phased in (gtid_mode is OFF, OFF_PERMISSIVE, or ON_PERMISSIVE), see Section 2.7.3.2, “Skipping Transactions Without GTIDs”.

To restart replication after skipping the transaction, issue START SLAVE, with the FOR CHANNEL clause if the replica is a multi-source replica.