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 slave. Before skipping a transaction, ensure that the replication slave's I/O thread is stopped as well as the 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
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 slave, 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 slave should not be restarted.
Does the transaction that stopped replication need to be applied on the slave? If so, either make the appropriate corrections and reapply the transaction, or manually reconcile the data on the slave.
Did the transaction that stopped replication need to be applied on the master? 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 184.108.40.206, “Skipping Transactions With GTIDs” .
When GTIDs are not in use or are being phased in (
ON_PERMISSIVE), see Section 220.127.116.11, “Skipping Transactions Without GTIDs”.
To restart replication after skipping the transaction, issue
START SLAVE, with the
FOR CHANNEL clause if the slave is a
multi-source replication slave.