How the MASTER_GTID_WAIT() function works in Mariadb?

The MASTER_GTID_WAIT() function is a useful tool for managing replication in Mariadb.

Posted on

The MASTER_GTID_WAIT() function is a useful tool for managing replication in Mariadb. It allows you to wait until a specific global transaction identifier (GTID) or a set of GTIDs has been applied on the current server. This can help you ensure data consistency and synchronization across multiple servers in a replication topology.

Syntax

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

MASTER_GTID_WAIT(gtid_set[, timeout])

The function takes two arguments:

  • gtid_set: A string that specifies one or more GTIDs to wait for. The GTIDs can be separated by commas, and each GTID consists of a domain ID, a server ID, and a sequence number. For example, 0-1-100,1-2-50 means to wait for the 100th transaction in domain 0 from server 1 and the 50th transaction in domain 1 from server 2.
  • timeout: An optional integer that specifies the maximum number of seconds to wait. If omitted or set to zero, the function will wait indefinitely until the GTIDs are applied or an error occurs.

The function returns an integer value that indicates the result of the wait operation:

  • 0: The wait was successful and all the specified GTIDs have been applied on the current server.
  • -1: The wait was interrupted by a user or a system event, such as a KILL statement or a server shutdown.
  • -2: The wait timed out and some or all of the specified GTIDs have not been applied on the current server.
  • -3: The wait failed due to an invalid argument, such as an empty or malformed GTID set.

Examples

In this section, we will show some examples of how to use the MASTER_GTID_WAIT() function in different scenarios.

Example 1: Waiting for a single GTID

Suppose you have a master server with the server ID 1 and a slave server with the server ID 2. You want to perform a backup on the slave server, but you want to make sure that it has applied the latest transaction from the master server. You can use the MASTER_GTID_WAIT() function to wait for the last GTID from the master server before starting the backup. For example, if the last GTID from the master server is 0-1-100, you can execute the following statement on the slave server:

SELECT MASTER_GTID_WAIT('0-1-100');

This will return 0 if the slave server has applied the GTID 0-1-100, or a negative value if the wait was interrupted, timed out, or failed.

Example 2: Waiting for multiple GTIDs

Suppose you have a master server with the server ID 1 and two slave servers with the server IDs 2 and 3. You want to perform a schema change on the master server, but you want to make sure that both slave servers have applied all the transactions from the master server before the change. You can use the MASTER_GTID_WAIT() function to wait for the union of the executed GTID sets from both slave servers before executing the schema change. For example, if the executed GTID sets from the slave servers are 0-1-100,1-2-50 and 0-1-100,1-3-60, respectively, you can execute the following statement on the master server:

SELECT MASTER_GTID_WAIT('0-1-100,1-2-50,1-3-60');

This will return 0 if the master server has applied all the GTIDs from both slave servers, or a negative value if the wait was interrupted, timed out, or failed.

Example 3: Waiting with a timeout

Suppose you have a master server with the server ID 1 and a slave server with the server ID 2. You want to perform a maintenance operation on the slave server, but you want to limit the downtime to 10 seconds. You can use the MASTER_GTID_WAIT() function to wait for the last GTID from the master server with a timeout of 10 seconds. For example, if the last GTID from the master server is 0-1-100, you can execute the following statement on the slave server:

SELECT MASTER_GTID_WAIT('0-1-100', 10);

This will return 0 if the slave server has applied the GTID 0-1-100 within 10 seconds, or -2 if the wait timed out. You can then decide whether to proceed with the maintenance operation or not based on the return value.

Conclusion

The MASTER_GTID_WAIT() function is a powerful and flexible function that can help you control the replication process and ensure data consistency and synchronization across multiple servers in Mariadb. You can use it to wait for specific GTIDs or sets of GTIDs to be applied on the current server, with or without a timeout. You can also use some other related functions to get or manipulate the GTID information of the servers. By using these functions, you can achieve a better management and monitoring of your replication topology.