MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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.
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set
[,
timeout
][,channel
])
Like WAIT_FOR_EXECUTED_GTID_SET(),
but
for a single started replication channel. Use
WAIT_FOR_EXECUTED_GTID_SET()
instead to
ensure all channels are covered in all states.
For details of these functions, see Section 12.19, “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 22, 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:
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.
CREATE FUNCTION GTID_IS_EQUAL(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT) RETURNS INT 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.
CREATE FUNCTION GTID_IS_DISJOINT(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT) RETURNS INT 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.
CREATE FUNCTION GTID_IS_DISJOINT_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT, sum LONGTEXT) RETURNS INT RETURN GTID_IS_EQUAL(GTID_SUBTRACT(sum, gtid_set_1), gtid_set_2) AND 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.
CREATE FUNCTION GTID_NORMALIZE(g LONGTEXT) RETURNS LONGTEXT RETURN GTID_SUBTRACT(g, '')|
This function returns the union of two GTID sets.
CREATE FUNCTION GTID_UNION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT) RETURNS LONGTEXT RETURN GTID_NORMALIZE(CONCAT(gtid_set_1, ',', gtid_set_2))|
This function returns the intersection of two GTID sets.
CREATE FUNCTION GTID_INTERSECTION(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT) RETURNS LONGTEXT 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
.
CREATE FUNCTION GTID_SYMMETRIC_DIFFERENCE(gtid_set_1 LONGTEXT, gtid_set_2 LONGTEXT) RETURNS LONGTEXT 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.
CREATE FUNCTION GTID_SUBTRACT_UUID(gtid_set LONGTEXT, uuid TEXT) RETURNS LONGTEXT 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).
CREATE FUNCTION GTID_INTERSECTION_WITH_UUID(gtid_set LONGTEXT, uuid TEXT) RETURNS LONGTEXT RETURN GTID_SUBTRACT(gtid_set, GTID_SUBTRACT_UUID(gtid_set, uuid))|
Example 16.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 16.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 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
the gtid_purged
set for
server1
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
. If there is any overlap, with
identical GTIDs present on both servers for some reason,
copying database db2
to
server1
produces errors. 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:
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:
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:
server1> SELECT GTID_IS_DISJOINT_UNION($original_gtid_executed, $gtid_purged_set, @@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 causes it
to fail.
Example 16.3 Selecting the most up-to-date replica for manual failover
The stored function GTID_UNION
could 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 replication 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 of the
gtid_executed
set on each
replica with the set of transactions received by the replica,
which is recorded in the Performance Schema table
replication_connection_status
. 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 slave as the new replication source server.
Example 16.4 Checking for extraneous transactions on a replica
The stored function GTID_SUBTRACT_UUID
could 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
server_uuid
value:
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 multi-source replication topology, repeat the function, for example:
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 16.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:
SELECT GTID_INTERSECTION_WITH_UUID(@@GLOBAL.gtid_executed, my_server_uuid);
Example 16.6 Validating an additional replica in a multi-source replication setup
The stored function
GTID_INTERSECTION_WITH_UUID
could be used
to find out if a replica attached to a multi-source replication
setup has applied all the transactions originating from one
particular source. In this scenario, source1
and source2
are both sources and replicas and
replicate to each other. source2
also has its
own replica. The replica also receives and applies transactions
from source1
if source2
is
configured with
log_slave_updates=ON
, but it
does not do so if source2
uses
log_slave_updates=OFF
. Whatever
the case, we currently only want to find out if the replica is
up to date with source2
. In this situation,
the stored function
GTID_INTERSECTION_WITH_UUID
can be used to
identify the transactions that source2
originated, discarding the transactions that
source2
has replicated from
source1
. The built-in function
GTID_SUBSET
can then be used to compare the
result to the gtid_executed
set
on the replica. If the replica is up to date with
source2
, the
gtid_executed
set on the
replica contains all the transactions in the intersection set
(the transactions that originated from
source2
).
To carry out this check, store source2
's
gtid_executed
set,
source2
's server UUID, and the replica's
gtid_executed
set, into
client-side variables as follows:
$source2_gtid_executed := source2> SELECT @@GLOBAL.gtid_executed; $source2_server_uuid := source2> SELECT @@GLOBAL.server_uuid; $replica_gtid_executed := replica> SELECT @@GLOBAL.gtid_executed;
Then use GTID_INTERSECTION_WITH_UUID
and
GTID_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 from source2
($source2_server_uuid
) is used with
GTID_INTERSECTION_WITH_UUID
to identify and
return only those GTIDs from source2
's
gtid_executed
set that
originated on source2
, omitting those that
originated on source1
. The resulting GTID set
is then compared with the set of all executed GTIDs on the
replica, using GTID_SUBSET
. If this
statement returns nonzero (true), all the identified GTIDs from
source2
(the first set input) are also in the
replica's gtid_executed
set
(the second set input), meaning that the replica has replicated
all the transactions that originated from
source2
.