Rechercher dans le manuel MySQL

17.1.3.7 Stored Function Examples to Manipulate GTIDs

Contents

MySQL includes some built-in (native) functions for use with GTID-based replication. These functions are as follows:

GTID_SUBSET(set1,set2)

Given two sets of global transaction identifiers set1 and set2, returns true if all GTIDs in set1 are also in set2. Returns false otherwise.

GTID_SUBTRACT(set1,set2)

Given two sets of global transaction identifiers set1 and set2, returns only those GTIDs from set1 that are not in set2.

WAIT_FOR_EXECUTED_GTID_SET(gtid_set[, timeout])

Wait until the server has applied all of the transactions whose global transaction identifiers are contained in gtid_set. The optional timeout stops the function from waiting after the specified number of seconds have elapsed.

For details of these functions, see Section 12.18, “Functions Used with Global Transaction Identifiers (GTIDs)”.

You can define your own stored functions to work with GTIDs. For information on defining stored functions, see Chapter 24, Stored Objects. The following examples show some useful stored functions that can be created based on the built-in GTID_SUBSET() and GTID_SUBTRACT() functions.

Note that in these stored functions, the delimiter command has been used to change the MySQL statement delimiter to a vertical bar, as follows:

  1. mysql> delimiter |

All of these functions take string representations of GTID sets as arguments, so GTID sets must always be quoted when used with them.

This function returns nonzero (true) if two GTID sets are the same set, even if they are not formatted in the same way.

  1. CREATE FUNCTION GTID_IS_EQUAL(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
  2. RETURNS INT
  3.   RETURN GTID_SUBSET(gtid_set_1, gtid_set_2) AND GTID_SUBSET(gtid_set_2, gtid_set_1)|

This function returns nonzero (true) if two GTID sets are disjoint.

  1. CREATE FUNCTION GTID_IS_DISJOINT(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
  2. RETURNS INT
  3.   RETURN GTID_SUBSET(gtid_set_1, GTID_SUBTRACT(gtid_set_1, gtid_set_2))|

This function returns nonzero (true) if two GTID sets are disjoint, and sum is the union of the two sets.

  1. CREATE FUNCTION GTID_IS_DISJOINT_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT, sum LONGTEXT)
  2. RETURNS INT
  3.   RETURN GTID_IS_EQUAL(GTID_SUBTRACT(sum, gtid_set_1), gtid_set_2) AND
  4.          GTID_IS_EQUAL(GTID_SUBTRACT(sum, gtid_set_2), gtid_set_1)|

This function returns a normalized form of the GTID set, in all uppercase, with no whitespace and no duplicates. The UUIDs are arranged in alphabetic order and intervals are arranged in numeric order.

  1. CREATE FUNCTION GTID_NORMALIZE(g LONGTEXT)
  2. RETURNS LONGTEXT
  3. RETURN GTID_SUBTRACT(g, '')|

This function returns the union of two GTID sets.

  1. CREATE FUNCTION GTID_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
  2. RETURNS LONGTEXT
  3.   RETURN GTID_NORMALIZE(CONCAT(gtid_set_1, ',', gtid_set_2))|

This function returns the intersection of two GTID sets.

  1. CREATE FUNCTION GTID_INTERSECTION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
  2. RETURNS LONGTEXT
  3.   RETURN GTID_SUBTRACT(gtid_set_1, GTID_SUBTRACT(gtid_set_1, gtid_set_2))|

This function returns the symmetric difference between two GTID sets, that is, the GTIDs that exist in gtid_set_1 but not in gtid_set_2, and also the GTIDs that exist in gtid_set_2 but not in gtid_set_1.

  1. CREATE FUNCTION GTID_SYMMETRIC_DIFFERENCE(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT)
  2. RETURNS LONGTEXT
  3.   RETURN GTID_SUBTRACT(CONCAT(gtid_set_1, ',', gtid_set_2), GTID_INTERSECTION(gtid_set_1, gtid_set_2))|

This function removes from a GTID set all the GTIDs from a specified origin, and returns the remaining GTIDs, if any. The UUID is the identifier used by the server where the transaction originated, which is normally the server_uuid value.

  1. CREATE FUNCTION GTID_SUBTRACT_UUID(gtid_set LONGTEXT, uuid TEXT)
  2. RETURNS LONGTEXT
  3.   RETURN GTID_SUBTRACT(gtid_set, CONCAT(UUID, ':1-', (1 << 63) - 2))|

This function reverses the previously listed function to return only those GTIDs from the GTID set that originate from the server with the specified identifier (UUID).

  1. CREATE FUNCTION GTID_INTERSECTION_WITH_UUID(gtid_set LONGTEXT, uuid TEXT)
  2. RETURNS LONGTEXT
  3.   RETURN GTID_SUBTRACT(gtid_set, GTID_SUBTRACT_UUID(gtid_set, uuid))|

Example 17.1 Verifying that a replication slave is up to date

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

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

  1. SELECT GTID_SUBSET(master_gtid_executed, slave_gtid_executed)

If this returns 0 (false), some GTIDs in master_gtid_executed are not present in slave_gtid_executed, so the master has applied some transactions that the slave has not applied, and the slave is therefore not up to date.

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

  1. SELECT GTID_SUBTRACT(master_gtid_executed, slave_gtid_executed)

This statement returns any GTIDs that are in master_gtid_executed but not in slave_gtid_executed. If any GTIDs are returned, the master has applied some transactions that the slave has not applied, and the slave 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 that will add 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:

    1. 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. If there is any overlap, with identical GTIDs present on both servers for some reason, you will see 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:

    1. server1> SELECT GTID_IS_DISJOINT($gtid_purged_set, @@GLOBAL.gtid_executed);

    If the result is 1, there is no overlap between the two GTID sets, so no duplicate GTIDs are present.

  • Use GTID_IS_DISJOINT_UNION to verify that the restore operation resulted in the correct GTID state on server1. Before restoring the backup, on server1, obtain the existing gtid_executed set by executing the following statement:

    1. server1> SELECT @@GLOBAL.gtid_executed;

    Store the result in a local variable $original_gtid_executed. Also store the gtid_purged set in a local variable as described above. When the backup from server2 has been restored onto server1, execute the following statement to verify the GTID state:

    1. server1> SELECT GTID_IS_DISJOINT_UNION($original_gtid_executed,
    2.                                        $gtid_purged_set,
    3.                                        @@GLOBAL.gtid_executed);

    If the result is 1, the stored function has verified that the original gtid_executed set from server1 ($original_gtid_executed) and the gtid_purged set that was added from server2 ($gtid_purged_set) have no overlap, and also that the updated gtid_executed set on server1 now consists of the previous gtid_executed set from server1 plus the gtid_purged set from server2, which is the desired result. Ensure that this check is carried out before any further transactions take place on server1, otherwise the new transactions in the gtid_executed set will cause it to fail.


Example 17.3 Selecting the most up-to-date slave for manual failover

The stored function GTID_UNION could be used to identify the most up-to-date replication slave from a set of slaves, in order to perform a manual failover operation after a replication master has stopped unexpectedly. If some of the slaves are experiencing replication lag, this stored function can be used to compute the most up-to-date slave without waiting for all the slaves to apply their existing relay logs, and therefore to minimize the failover time. The function can return the union of the gtid_executed set on each slave with the set of transactions received by the slave, which is recorded in the Performance Schema table replication_connection_status. You can compare these results to find which slave's record of transactions is the most up-to-date, even if not all of the transactions have been committed yet.

On each replication slave, compute the complete record of transactions by issuing the following statement:

  1. SELECT GTID_UNION(RECEIVED_TRANSACTION_SET, @@GLOBAL.gtid_executed)
  2.     FROM performance_schema.replication_connection_status
  3.     WHERE channel_name = 'name';

You can then compare the results from each slave to see which one has the most up-to-date record of transactions, and use this slave as the new replication master.


Example 17.4 Checking for extraneous transactions on a replication slave

The stored function GTID_SUBTRACT_UUID could be used to check whether a replication slave has received transactions that did not originate from its designated master or masters. If it has, there might be an issue with your replication setup, or with a proxy, router, or load balancer. This function works by removing from a GTID set all the GTIDs from a specified originating server, and returning the remaining GTIDs, if any.

For a replication slave with a single master, issue the following statement, giving the identifier of the originating replication master, which is normally the server_uuid value:

  1. SELECT GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed, server_uuid_of_master);

  If the result is not empty, the transactions returned are extra transactions that did not originate from the designated master.

For a slave in a multi-master replication topology, repeat the function, for example:

  1. SELECT GTID_SUBTRACT_UUID(GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed,
  2.                                              server_uuid_of_master_1),
  3.                                              server_uuid_of_master_2);

If the result is not empty, the transactions returned are extra transactions that did not originate from any of the designated masters.


Example 17.5 Verifying that a server in a replication topology is read-only

The stored function GTID_INTERSECTION_WITH_UUID could be used to verify that a server has not originated any GTIDs and is in a read-only state. The function returns only those GTIDs from the GTID set that originate from the server with the specified identifier. If any of the transactions in the server's gtid_executed set have the server's own identifier, the server itself originated those transactions. You can issue the following statement on the server to check:

  1. SELECT GTID_INTERSECTION_WITH_UUID(@@GLOBAL.gtid_executed, my_server_uuid);


Example 17.6 Validating an additional slave in a multi-master replication setup

The stored function GTID_INTERSECTION_WITH_UUID could be used to find out if a slave attached to a multi-master replication setup has applied all the transactions originating from one particular master. In this scenario, master1 and master2 are both masters and slaves and replicate to each other. master2 also has its own replication slave. The replication slave will also receive and apply master1's transactions if master2 is configured with log_slave_updates=ON, but it will not do so if master2 uses log_slave_updates=OFF. Whatever the case, we currently only want to find out if the replication slave is up to date with master2. In this situation, the stored function GTID_INTERSECTION_WITH_UUID can be used to identify the transactions that master2 originated, discarding the transactions that master2 has replicated from master1. The built-in function GTID_SUBSET can then be used to compare the result to the gtid_executed set on the slave. If the slave is up to date with master2, the gtid_executed set on the slave contains all the transactions in the intersection set (the transactions that originated from master2).

To carry out this check, store master2's gtid_executed set, master2's server UUID, and the slave's gtid_executed set, into client-side variables as follows:

  1. $master2_gtid_executed :=
  2.       master2> SELECT @@GLOBAL.gtid_executed;
  3.     $master2_server_uuid :=
  4.       master2> SELECT @@GLOBAL.server_uuid;
  5.     $slave_gtid_executed :=
  6.       slave> SELECT @@GLOBAL.gtid_executed;

Then use GTID_INTERSECTION_WITH_UUID and GTID_SUBSET with these variables as input, as follows:

  1. SELECT GTID_SUBSET(GTID_INTERSECTION_WITH_UUID($master2_gtid_executed,
  2.                                                $master2_server_uuid),
  3.                                                $slave_gtid_executed);

The server identifier from master2 ($master2_server_uuid) is used with GTID_INTERSECTION_WITH_UUID to identify and return only those GTIDs from master2's gtid_executed set that originated on master2, omitting those that originated on master1. The resulting GTID set is then compared with the set of all executed GTIDs on the slave, using GTID_SUBSET. If this statement returns nonzero (true), all the identified GTIDs from master2 (the first set input) are also in the slave's gtid_executed set (the second set input), meaning that the slave has replicated all the transactions that originated from master2.



Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-replication-gtids-functions.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut