Replication with Global Transaction Identifiers 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: 3472