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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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 Cluster Point-In-Time Recovery (PITR)
  2. Managing MySQL Using Systemd As A Non Root User
  3. MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements
  4. How to Change the Default Character Set and Collation for a Database in MySQL
  5. Backup and Restore Of Group Replication Node ( MySQL 8.0 )
  6. MySQL – How to undo (rollback) a set of SQL statements
  7. Beginners Guide to Storage Engines in MySQL
  8. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  9. CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)
  10. How To Restore an NDBCluster Backup Using ndb_restore

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary