RETURN GTID_SUBTRACT(gtid_set, GTID_SUBTRACT_UUID(gtid_set, uuid))| 

## Example 17.1 Verifying that a replica is up to date 

The built-in functions GTID_SUBSET  and GTID_SUBTRACT  can be used to check that a replica has applied at least every transaction that a source has applied. 

To perform this check with GTID_SUBSET , execute the following statement on the replica: 

SELECT GTID_SUBSET( source_gtid_executed , replica_gtid_executed ) 

If this returns 0 (false), some GTIDs in source_gtid_executed  are not present in replica_gtid_executed , so the source has applied some transactions that the replica has not applied, and the replica is therefore not up to date. 

To perform the check with GTID_SUBTRACT , execute the following statement on the replica: 

SELECT GTID_SUBTRACT( source_gtid_executed , replica_gtid_executed ) 

This statement returns any GTIDs that are in source_gtid_executed  but not in replica_gtid_executed . If any GTIDs are returned, the source has applied some transactions that the replica has not applied, and the replica is therefore not up to date. 

## Example 17.2 Backup and restore scenario 

The stored functions GTID_IS_EQUAL , GTID_IS_DISJOINT , and GTID_IS_DISJOINT_UNION  could be used to verify backup and restore operations involving multiple databases and servers. In this example scenario, server1  contains database db1 , and server2  contains database db2 . The goal is to copy database db2  to server1 , and the result on server1  should be the union of the two databases. The procedure used is to back up server2  using mysqlpump  or mysqldump , then restore this backup on server1 . 

Provided the backup program’s option --set-gtid-purged  was set to ON  or the default of AUTO , the program’s output contains a SET @@GLOBAL.gtid_purged  statement which adds the gtid_executed set from server2  to the gtid_purged  set on server1 . The gtid_purged  set contains the GTIDs of all the transactions that have been committed on a server but do not exist in any binary log file on the server. When database db2  is copied to server1 , the GTIDs of the transactions committed on server2 , which are not in the binary log files on server1 , must be added to server1 ’s gtid_purged  set to make the set complete. 

The stored functions can be used to assist with the following steps in this scenario: 

•Use GTID_IS_EQUAL  to verify that the backup operation computed the correct GTID set for the SET @@GLOBAL.gtid_purged  statement. On server2 , extract that statement from the mysqlpump  or mysqldump  output, and store the GTID set into a local variable, such as $gtid_purged_set . Then execute the following statement: 

server2> SELECT GTID_IS_EQUAL($gtid_purged_set, @@GLOBAL.gtid_executed); 

If the result is 1, the two GTID sets are equal, and the set has been computed correctly. 

•Use GTID_IS_DISJOINT  to verify that the GTID set in the mysqlpump  or mysqldump  output does not overlap with the gtid_executed  set on server1 . Having identical GTIDs present on both servers causes errors when copying database db2  to server1 . To check, on server1 , extract and store the gtid_purged  set from the output into a local variable as above, then execute the following statement: 

