Talking about GTID-four basic functions and usage scenarios related to GTID
Four basic functions
1. GTID_SUBSET (set1,set2)-if the GTID in set1 is also in set2, return true, otherwise return false (set1 is a subset of set2).
[root@mysql.sock] [(none)] > SELECT GTID_SUBSET (3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57') ('3E11FA47-71CA-11E1-9E33murc C80AA9429562purse 23')
+-+
| | GTID_SUBSET (3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57') ('3E11FA47-71CA-11E1-9E33murC80AA9429562VOR 23') |
+-+
| | 1 |
+-+
1 row in set (0.00 sec)
[root@mysql.sock] [(none)] > SELECT GTID_SUBSET (3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57') in '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25'.
+-+
| | GTID_SUBSET ('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25 recording 3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57') |
+-+
| | 0 |
+-+
1 row in set (0.00 sec)
2. GTID_SUBTRACT (set1,set2)-returns the GTID collection (the difference between set1 and set2) that is in set1 but not in set2.
[root@mysql.sock] [(none)] > SELECT GTID_SUBTRACT ('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57 charge 3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25')
+-+
| | GTID_SUBTRACT ('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57 recording 3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25') |
+-+
| | 3e11fa47-71ca-11e1-9e33-c80aa9429562:26-57 | |
+-+
1 row in set (0.01 sec)
[root@mysql.sock] [(none)] > SELECT GTID_SUBTRACT (3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24') in '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'.
+-+
| | GTID_SUBTRACT ('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57 recording 3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24') |
+-+
| | 3e11fa47-71ca-11e1-9e33-c80aa9429562:21-22:25-57 | |
+-+
1 row in set (0.00 sec)
3. WAIT_FOR_EXECUTED_GTID_SET (gtid_set [, timeout])-wait for the execution of gtid_set, that is, GTID_SUBSET (gtid_subset, @ @ GLOBAL.gtid_executed) is established. Timeout is optional, timeout parameter. Function returns 0, which indicates success; 1, which indicates timeout; and other values, which indicates error.
[root@mysql.sock] [(none)] > SELECT WAIT_FOR_EXECUTED_GTID_SET ('c4e5d4a0-a9be-11e9-b444-080027f22add:1-5)
+-- +
| | WAIT_FOR_EXECUTED_GTID_SET ('c4e5d4a0-a9be-11e9-b444-080027f22add:1-5 minutes 5) | |
+-- +
| | 0 |
+-- +
1 row in set (0.00 sec)
4. WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS (gtid_set [, timeout] [, channel])-similar to WAIT_FOR_EXECUTED_GTID_SET (), the channel parameter is added.
Working with scen
1. Check that all GTIDs on master is executed on the slave node.
Execute on the salve node, SELECT GTID_SUBSET (master_gtid_executed, slave_gtid_executed). If 0 is returned, it means that GTIDs is still not executed on the slave node.
Execute on the slave node, SELECT GTID_SUBTRACT (master_gtid_executed, slave_gtid_executed). If GTIDs is returned, it also means that there is still GTIDs not executed on the slave node.
