How To Skip a Transaction When GTIDs Are Enabled? (Doc ID 2043205.1)

tech2023-02-09 90

How To Skip a Transaction When GTIDs Are Enabled? (Doc ID 2043205.1)To Bottom

Goal Solution References

APPLIES TO:

MySQL Server - Version 5.6 and later Information in this document applies to any platform.

GOAL

Learn how to skip a transaction on a replication slave when GTIDs are enabled.

SOLUTION

When gtid_mode = ON you cannot use sql_slave_skip_counter to skip transactions. Instead you need to inject an empty transaction.

Important: The whole transaction is skipped. Just like using sql_slave_skip_counter, skipping a transaction is likely to bring the replication slave further out of sync. See also: Should I use sql_slave_skip_counter or slave_skip_errors.

The steps are:

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:

shell$ 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.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.

REFERENCES

https://dev.mysql.com/doc/refman/en/replication-gtids.htmlhttps://dev.mysql.com/doc/refman/en/replication-options-gtids.html#sysvar_gtid_modehttps://dev.mysql.com/doc/refman/en/replication-options-slave.html#sysvar_sql_slave_skip_counterhttps://dev.mysql.com/doc/refman/en/set-global-sql-slave-skip-counter.htmlNOTE:1450190.1 - How to Recover From a Replication Error?NOTE:1560197.1 - Should I use sql_slave_skip_counter or slave_skip_errorshttps://dev.mysql.com/doc/refman/en/replication-gtids-failover.html#replication-gtids-failover-emptyNOTE:1023716.1 - This document has been replaced with more recent information on this topic. Please refer to more recent documentation.NOTE:1023449.1 - Replication stopped with 'Duplicate key error.'https://dev.mysql.com/doc/refman/en/mysqlbinlog.htmlNOTE:2306350.1 - How to Avoid the Replication Slave Getting Out of Sync?

HOWTO PUBLISHED

Related Products

MySQL Server

Information Centers

Oracle Database 11g Release 2 Information Center [1436725.2]

Redirect - 1307334.1 Master Note for ORA-1555 Errors [1338919.2]

Document References

How to Recover From a Replication Error? [1450190.1]

Should I use sql_slave_skip_counter or slave_skip_errors [1560197.1]

Replication stopped with 'Duplicate key error.' [1023449.1]

How to Avoid the Replication Slave Getting Out of Sync? [2306350.1]