How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled

What is GTID

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique, not only to the server on which it originated but also across all servers in a given replication setup. There is a one-to-one mapping between all transactions and all GTIDs.

A GTID is represented as a pair of coordinates, separated by a colon character (:):

GTID = source_id:transaction_id

The source_id option identifies the originating server. Normally, the server’s server_uuid option is used for this purpose. The transaction_id option is a sequence number determined by the order in which the transaction was committed on this server. For example, the first transaction to be committed has 1 as its transaction_id, and the tenth transaction to be committed on the same originating server is assigned a transaction_id of 10.

Skipping Transaction When GTIDs Are Enabled

When “gtid_mode = ON” you cannot use sql_slave_skip_counter to skip transactions. Instead you need to inject an empty transaction. The steps are:

1. Find the GTID of the transaction you need to skip. The best way is to use mysqlbinlog to read the master’s binary log at the point where the replication is stopped. For example if SHOW SLAVE STATUS shows:

        Relay_Master_Log_File: binlog.000010
...
          Exec_Master_Log_Pos: 194

Then use mysqlbinlog (on the replicaton master) with that file and position:

$ mysqlbinlog --start-position=194 binlog.000010

and look for the “SET @@SESSION.GTID_NEXT” statement, for example:

# at 194
#180710  9:47:54 server id 572238408  end_log_pos 259 CRC32 0x83a04322  GTID    last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '4ab8feff-5272-11e8-9320-08002715584a:201840'/*!*/;

Most often, the GTID in “SET @@SESSION.GTID_NEXT” will be the next up from the value displayed in Executed_Gtid_Set in the “SHOW SLAVE STATUS” output for the UUID of the replication master – however, it is not guaranteed to be the case.

2. Skip the transaction with the GTID from the SET @@SESSION.GTID_NEXT statement in 1. (assuming the GTID to skip is ‘4ab8feff-5272-11e8-9320-08002715584a:201840’):

mysql> SET @@SESSION.GTID_NEXT= '4ab8feff-5272-11e8-9320-08002715584a:201840';
Query OK, 0 rows affected (0.04 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GTID_NEXT='AUTOMATIC';
Query OK, 0 rows affected (0.00 sec)

Note that you can simply copy the SET @@SESSION.GTID_NEXT statement from the mysqlbinlog output.