How the BINLOG_GTID_POS() function works in Mariadb?

The BINLOG_GTID_POS() function is a replication function that returns the global transaction identifier (GTID) position of a given binary log file and position.

Posted on

The BINLOG_GTID_POS() function is a replication function that returns the global transaction identifier (GTID) position of a given binary log file and position. The BINLOG_GTID_POS() function can be used to get the GTID position of a binary log event, which is a unique identifier that represents a transaction in a replication setup. For example, the BINLOG_GTID_POS() function can be used to get the GTID position of the last binary log event on a master server.

Syntax

The syntax of the BINLOG_GTID_POS() function is as follows:

BINLOG_GTID_POS(log_name, log_pos)

The log_name parameter is a string value that specifies the name of the binary log file. The log_name parameter cannot be NULL or empty.

The log_pos parameter is an integer value that specifies the position of the binary log event within the file. The log_pos parameter cannot be NULL or negative.

The BINLOG_GTID_POS() function returns a string value, which is the GTID position of the input binary log file and position. If the input parameters are NULL or invalid, the BINLOG_GTID_POS() function returns NULL.

Examples

Here are some examples of using the BINLOG_GTID_POS() function in Mariadb:

Example 1: GTID position of a literal binary log file and position

To get the GTID position of a literal binary log file and position, you can simply pass the values as the parameters. For example:

SELECT BINLOG_GTID_POS('mysql-bin.000001', 1234) AS result;

This statement returns ‘0-1-2’ as the output, which is the GTID position of the binary log event at the file ‘mysql-bin.000001’ and the position 1234.

Example 2: GTID position of a column value

To get the GTID position of a column value, you can use the column name as the parameter. For example, suppose you have a table called binlog_events that stores the binary log file name and position of each binary log event. You can use the BINLOG_GTID_POS() function to get the GTID position of the column value for each row. For example:

SELECT file_name, file_pos, BINLOG_GTID_POS(file_name, file_pos) AS gtid_pos
FROM binlog_events;

This statement returns the file name, the file position, and the GTID position for each row in the binlog_events table. For example, if the table contains the following data:

file_name file_pos
mysql-bin.000001 1234
mysql-bin.000002 5678
mysql-bin.000003 9012

The statement returns the following output:

file_name file_pos gtid_pos
mysql-bin.000001 1234 0-1-2
mysql-bin.000002 5678 0-1-3
mysql-bin.000003 9012 0-1-4

Example 3: GTID position of a function call

To get the GTID position of a function call, you can use the function call as the parameter. For example, you can use the BINLOG_GTID_POS() function to get the GTID position of the binary log file and position that are returned by another replication function. For example:

SELECT BINLOG_GTID_POS(MASTER_POS_WAIT('mysql-bin.000001', 1234)) AS result;

This statement returns ‘0-1-2’ as the output, which is the GTID position of the binary log file and position that are returned by the MASTER_POS_WAIT() function, which waits until the slave has read and applied all updates up to the specified position in the master’s binary log.

Some of the functions that are related to the BINLOG_GTID_POS() function are:

  • The GTID_CURRENT_POS() function, which returns the current GTID position of the server. The GTID_CURRENT_POS() function can be used to get the GTID position of the last committed transaction on the server. For example, GTID_CURRENT_POS() returns ‘0-1-5’ as the output, which is the current GTID position of the server.
  • The GTID_BINLOG_POS() function, which returns the GTID position of the last binary log event on the server. The GTID_BINLOG_POS() function can be used to get the GTID position of the last written transaction on the server. For example, GTID_BINLOG_POS() returns ‘0-1-6’ as the output, which is the GTID position of the last binary log event on the server.
  • The GTID_SUBTRACT() function, which returns the difference between two GTID sets. The GTID_SUBTRACT() function can be used to get the GTID set that contains the transactions that are in the first GTID set but not in the second GTID set. For example, GTID_SUBTRACT('0-1-5', '0-1-3') returns ‘0-1-4,0-1-5’ as the output, which is the GTID set that contains the transactions that are in the GTID set ‘0-1-5’ but not in the GTID set ‘0-1-3’.

Conclusion

The BINLOG_GTID_POS() function is a useful function that can help you get the GTID position of a given binary log file and position in Mariadb. You can use it with any valid binary log file name and position, such as a column name, a literal value, or a function call. The BINLOG_GTID_POS() function returns a string value, which is the GTID position of the input binary log file and position. You can also use some other functions that are related to the BINLOG_GTID_POS() function, such as the GTID_CURRENT_POS(), GTID_BINLOG_POS(), and GTID_SUBTRACT() functions.