• 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. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  2. How to Restrict MySQL User Creation with Blank Password
  3. Beginners Guide to MySQL Data Types
  4. MySQL Backup stuck at “Starting to lock all the tables”
  5. How To Restore an NDBCluster Backup Using ndb_restore
  6. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  7. What is the MySQL Enterprise Monitor?
  8. Beginners Guide to Storage Engines in MySQL
  9. “expect” script to provide password to mysql_config_editor
  10. How to configure resource groups for MySQL Server running on Linux

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright