• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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.

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
  2. MySQL Interview Questions : Multi-source Replication in MySQL Server
  3. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  4. MySQL: How to Set Account Resource Limits
  5. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  6. How to configure resource groups for MySQL Server running on Linux
  7. How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  8. Understanding MySQL Privileges
  9. How To Start And Stop MySQL Cluster
  10. MySQL: How To Find Queries Taking Longer Than N Seconds

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright