Movatterモバイル変換

MySQL 9.3 Reference Manual

19.1.3.8 Stored Function Examples to Manipulate GTIDs

This section provides examples of stored functions (seeChapter 27,Stored Objects) which you can create using some of the built-in functions provided by MySQL for use with GTID-based replication, listed here:

GTID_SUBSET(): Shows whether one GTID set is a subset of another.
GTID_SUBTRACT(): Returns the GTIDs from one GTID set that are not in another.
WAIT_FOR_EXECUTED_GTID_SET(): Waits until all transactions in a given GTID set have been executed.

SeeSection 14.18.2, “Functions Used with Global Transaction Identifiers (GTIDs)”, more more information about the functions just listed.

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

mysql> delimiter |

All of the stored functions shown in this section 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:

CREATE FUNCTION GTID_IS_EQUAL(gs1 LONGTEXT, gs2 LONGTEXT)  RETURNS INT  RETURN GTID_SUBSET(gs1, gs2) AND GTID_SUBSET(gs2, gs1)|

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

CREATE FUNCTION GTID_IS_DISJOINT(gs1 LONGTEXT, gs2 LONGTEXT)RETURNS INT  RETURN GTID_SUBSET(gs1, GTID_SUBTRACT(gs1, gs2))|

This function returns nonzero (true) if two GTID sets are disjoint andsum is their union:

CREATE FUNCTION GTID_IS_DISJOINT_UNION(gs1 LONGTEXT, gs2 LONGTEXT, sum LONGTEXT)RETURNS INT  RETURN GTID_IS_EQUAL(GTID_SUBTRACT(sum, gs1), gs2) AND         GTID_IS_EQUAL(GTID_SUBTRACT(sum, gs2), gs1)|

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

CREATE FUNCTION GTID_NORMALIZE(gs LONGTEXT)RETURNS LONGTEXT  RETURN GTID_SUBTRACT(gs, '')|

This function returns the union of two GTID sets:

CREATE FUNCTION GTID_UNION(gs1 LONGTEXT, gs2 LONGTEXT)RETURNS LONGTEXT  RETURN GTID_NORMALIZE(CONCAT(gs1, ',', gs2))|

This function returns the intersection of two GTID sets.

CREATE FUNCTION GTID_INTERSECTION(gs1 LONGTEXT, gs2 LONGTEXT)RETURNS LONGTEXT  RETURN GTID_SUBTRACT(gs1, GTID_SUBTRACT(gs1, gs2))|

This function returns the symmetric difference between two GTID sets, that is, the GTIDs that exist ings1 but not ings2, as well as the GTIDs that exist ings2 but not ings1.

CREATE FUNCTION GTID_SYMMETRIC_DIFFERENCE(gs1 LONGTEXT, gs2 LONGTEXT)RETURNS LONGTEXT  RETURN GTID_SUBTRACT(CONCAT(gs1, ',', gs2), GTID_INTERSECTION(gs1, gs2))|

This function removes from a GTID set all the GTIDs with the 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 value ofserver_uuid.

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

This function acts as the reverse of the previous one; it returns only those GTIDs from the GTID set that originate from the server with the specified identifier (UUID).

CREATE FUNCTION GTID_INTERSECTION_WITH_UUID(gs LONGTEXT, uuid TEXT)RETURNS LONGTEXT  RETURN GTID_SUBTRACT(gs, GTID_SUBTRACT_UUID(gs, uuid))|

Example 19.1 Verifying that a replica is up to date

The built-in functionsGTID_SUBSET() andGTID_SUBTRACT() can be used to check that a replica has applied at least every transaction that a source has applied.

To perform this check withGTID_SUBSET(), execute the following statement on the replica:

SELECT GTID_SUBSET(source_gtid_executed,replica_gtid_executed);

If the returns value is0 (false), this means that some GTIDs insource_gtid_executed are not present inreplica_gtid_executed, and that the replica has not yet applied transactions that were applied on the source, which means that the replica is not up to date.

To perform the same check withGTID_SUBTRACT(), execute the following statement on the replica:

SELECT GTID_SUBTRACT(source_gtid_executed,replica_gtid_executed);

This statement returns any GTIDs that are insource_gtid_executed but not inreplica_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 19.2 Backup and restore scenario

The stored functionsGTID_IS_EQUAL(),GTID_IS_DISJOINT(), andGTID_IS_DISJOINT_UNION() can be used to verify backup and restore operations involving multiple databases and servers. In this example scenario,server1 contains databasedb1, andserver2 contains databasedb2. The goal is to copy databasedb2 toserver1, and the result onserver1 should be the union of the two databases. The procedure used is to back upserver2 usingmysqldump, then to restore this backup onserver1.

Provided thatmysqldump was run with--set-gtid-purged set toON orAUTO (the default), the output contains aSET @@GLOBAL.gtid_purged statement which adds thegtid_executed set fromserver2 to thegtid_purged set onserver1.gtid_purged contains the GTIDs of all the transactions that have been committed on a given server but which do not exist in any binary log file on the server. When databasedb2 is copied toserver1, the GTIDs of the transactions committed onserver2, which are not in the binary log files onserver1, must be added togtid_purged forserver1 to make the set complete.

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

UseGTID_IS_EQUAL() to verify that the backup operation computed the correct GTID set for theSET @@GLOBAL.gtid_purged statement. Onserver2, extract that statement from themysqldump 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.
UseGTID_IS_DISJOINT() to verify that the GTID set in themysqldump output does not overlap with thegtid_executed set onserver1. Having identical GTIDs present on both servers causes errors when copying databasedb2 toserver1. To check, onserver1, extract and storegtid_purged from the output into a local variable as done previously, then execute the following statement:
```
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.
UseGTID_IS_DISJOINT_UNION() to verify that the restore operation resulted in the correct GTID state onserver1. Before restoring the backup, onserver1, obtain the existinggtid_executed set by executing the following statement:
```
server1> SELECT @@GLOBAL.gtid_executed;
```
Store the result in a local variable$original_gtid_executed, as well as the set fromgtid_purged in another local variable as described previously. When the backup fromserver2 has been restored ontoserver1, execute the following statement to verify the GTID state:
```
server1> SELECT       ->   GTID_IS_DISJOINT_UNION($original_gtid_executed,      ->                          $gtid_purged_set,      ->                          @@GLOBAL.gtid_executed);
```
If the result is1, the stored function has verified that the originalgtid_executed set fromserver1 ($original_gtid_executed) and thegtid_purged set that was added fromserver2 ($gtid_purged_set) have no overlap, and that the updatedgtid_executed set onserver1 now consists of the previousgtid_executed set fromserver1 plus thegtid_purged set fromserver2, which is the desired result. Ensure that this check is carried out before any further transactions take place onserver1, otherwise the new transactions ingtid_executed cause it to fail.

Example 19.3 Selecting the most up-to-date replica for manual failover

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

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

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

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

Example 19.4 Checking for extraneous transactions on a replica

The stored functionGTID_SUBTRACT_UUID() can be used to check whether a replica has received transactions that did not originate from its designated source or sources. 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 replica with a single source, issue the following statement, giving the identifier of the originating source, which is normally the same asserver_uuid:

SELECT GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed, server_uuid_of_source);

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

For a replica in a multisource topology, include the server UUID of each source in the function call, like this:

SELECT   GTID_SUBTRACT_UUID(GTID_SUBTRACT_UUID(@@GLOBAL.gtid_executed,                                        server_uuid_of_source_1),                                        server_uuid_of_source_2);

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

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

The stored functionGTID_INTERSECTION_WITH_UUID() can 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 listed ingtid_executed from this server use the server's own identifier, the server itself originated those transactions. You can issue the following statement on the server to check:

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

Example 19.6 Validating an additional replica in multisource replication

The stored functionGTID_INTERSECTION_WITH_UUID() can be used to find out if a replica attached to a multisource replication setup has applied all the transactions originating from one particular source. In this scenario,source1 andsource2 are both sources and replicas and replicate to each other.source2 also has its own replica. The replica also receives and applies transactions fromsource1 ifsource2 is configured withlog_replica_updates=ON, but it does not do so ifsource2 useslog_replica_updates=OFF. Whichever the case, we currently want only to find out if the replica is up to date withsource2. In this situation,GTID_INTERSECTION_WITH_UUID() can be used to identify the transactions thatsource2 originated, discarding the transactions thatsource2 has replicated fromsource1. The built-in functionGTID_SUBSET() can then be used to compare the result with thegtid_executed set on the replica. If the replica is up to date withsource2, thegtid_executed set on the replica contains all the transactions in the intersection set (the transactions that originated fromsource2).

To carry out this check, store the values ofgtid_executed and the server UUID fromsource2 and the value ofgtid_executed from the replica into user variables as follows:

source2> SELECT @@GLOBAL.gtid_executed INTO @source2_gtid_executed;source2> SELECT @@GLOBAL.server_uuid INTO @source2_server_uuid;replica> SELECT @@GLOBAL.gtid_executed INTO @replica_gtid_executed;

Then useGTID_INTERSECTION_WITH_UUID() andGTID_SUBSET() with these variables as input, as follows:

SELECT   GTID_SUBSET(    GTID_INTERSECTION_WITH_UUID(@source2_gtid_executed,                                @source2_server_uuid),                                @replica_gtid_executed);

The server identifier fromsource2 (@source2_server_uuid) is used withGTID_INTERSECTION_WITH_UUID() to identify and return only those GTIDs from the set of GTIDs that originated onsource2, omitting those that originated onsource1. The resulting GTID set is then compared with the set of all executed GTIDs on the replica, usingGTID_SUBSET(). If this statement returns nonzero (true), all the identified GTIDs fromsource2 (the first set input) are also found ingtid_executed from the replica, meaning that the replica has received and executed all the transactions that originated fromsource2.

PREV HOME UP NEXT