Function to get binlog details for the given GTID in MySQL

MySQL & MariaDB
1

Hello,

I am working with MySQL 8 and need to retrieve the binlog file name and position from a given GTID (Global Transaction Identifier). Could anyone please help me with the function or query to get this information?

Any assistance would be highly appreciated. Thanks in advance!

2

Hello @preethi_subbu,
I could not find any functions for doing this. Your best solution is using mysqlbinlog on a few log files and simply grep’ing for the GTID. You can use glob-style filename pattern to the mysqlbinlog tool to make it easier.

1 Like
3

@preethi_subbu Also, If you using Percona (PS) MySQL 8 then you have the feasibility to use some user defined function.

For e.g,

mysql> CREATE FUNCTION get_binlog_by_gtid_set RETURNS STRING SONAME 'binlog_utils_udf.so';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT CAST(get_binlog_by_gtid_set("cbe2b67f-d4a1-11ef-af5a-52103420acdc:1-13") AS CHAR) AS result;
+---------------+
| result        |
+---------------+
| binlog.000002 |
+---------------+
1 row in set (0.00 sec)

You can check the same along with some other binary log/gtid set related functions here - Binary logs and replication improvements - Percona Server for MySQL