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!
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
@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