MySQL 8.0.23 introduces a new feature that makes replication possible from a source server that has been configured without Global Transaction Identifiers (GTIDs) to a replica server configured with GTIDs. This can be achieved by configuring replication channels to use the parameter ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS with the CHANGE REPLICATION SOURCE command. Enabling this option will allow the replica to assign a new GTID to every non-GTID(anonymous) transaction received from the source.
Some of the possible use cases of the feature:
- To test the use of GTIDs on your database setup, just add a new replica having GTID_MODE=ON, and enable the feature on that replica. The new replica will process all your workload and assign GTIDs to the transactions, so you can see that everything works as it should without interfering with the production servers.
- The feature can also be used when you cannot enable GTIDs on the source, for instance, it is managed by someone else, and you want to migrate to a modern topology that uses GTIDs.
Enabling GTIDs is necessary for tasks such as Group Replication, automatic fail-over, and in general an easier management of database replication, since GTIDs provide a mean to observe and compare the execution state among servers of a topology and determine if a transaction with a given GTID was already processed in some server of a topology.
Let us understand how to use this feature and what are the acceptable inputs for it.
One can assign the values
- OFF: This is also the default. It simply means we are not enabling the feature on that channel.
- LOCAL: In this case the UUID used in the GTID will be the same as the ‘server_uuid’ global variable for the server where the channel is being setup.
- <UUID>: You can specify a valid UUID which will be used while generating GTID transactions on this channel.
Usage
CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=OFF|LOCAL|<UUID>;
Here are some examples of query execution and replication were you can inspect both the source server binlog file and also the replica relay logs and see the outcome of the option being used.
Below you can see the execution of some commands in the source and also the resulting binlog file.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> CREATE TABLE t(s INT); mysql> INSERT INTO t VALUES(10); mysql> SHOW BINLOG EVENTS IN 'source-bin.000001'; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ | source-bin.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.24-tr-debug-asan, Binlog ver: 4 | | source-bin.000001 | 125 | Previous_gtids | 1 | 156 | | | source-bin.000001 | 156 | Anonymous_Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | source-bin.000001 | 233 | Query | 1 | 342 | use `test`; CREATE TABLE t(s INT) /* xid=5 */ | | source-bin.000001 | 342 | Anonymous_Gtid | 1 | 421 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | source-bin.000001 | 421 | Query | 1 | 496 | BEGIN | | source-bin.000001 | 496 | Table_map | 1 | 543 | table_id: 112 (test.t) | | source-bin.000001 | 543 | Write_rows | 1 | 583 | table_id: 112 flags: STMT_END_F | | source-bin.000001 | 583 | Xid | 1 | 614 | COMMIT /* xid=37 */ | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ |
Lets now look at the relay log files in the replica.
The CREATE command on source will be replicated with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=OFF.
The INSERT command on source will be replicated with ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> SHOW BINLOG EVENTS IN 'replica-bin.000001'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ | replica-bin.000001 | 4 | Format_desc | 2 | 125 | Server ver: 8.0.24-tr-debug-asan, Binlog ver: 4 | | replica-bin.000001 | 125 | Previous_gtids | 2 | 156 | | | replica-bin.000001 | 156 | Anonymous_Gtid | 1 | 240 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | replica-bin.000001 | 240 | Query | 1 | 349 | use `test`; CREATE TABLE t(s INT) /* xid=8 */ | | replica-bin.000001 | 349 | Rotate | 2 | 396 | replica-bin.000002;pos=4 | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------+ ... mysql>CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL; ... mysql> SHOW BINLOG EVENTS IN 'replica-bin.000004'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | replica-bin.000004 | 4 | Format_desc | 2 | 125 | Server ver: 8.0.24-tr-debug-asan, Binlog ver: 4 | | replica-bin.000004 | 125 | Previous_gtids | 2 | 156 | | | replica-bin.000004 | 156 | Gtid | 1 | 242 | SET @@SESSION.GTID_NEXT= 'b90cdeb5-824b-11eb-83ad-0010e0dc7238:1' | | replica-bin.000004 | 242 | Query | 1 | 312 | BEGIN | | replica-bin.000004 | 312 | Table_map | 1 | 359 | table_id: 112 (test.t) | | replica-bin.000004 | 359 | Write_rows | 1 | 399 | table_id: 112 flags: STMT_END_F | | replica-bin.000004 | 399 | Xid | 1 | 430 | COMMIT /* xid=28 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ mysql> SELECT @@SERVER_UUID; +--------------------------------------+ | @@SERVER_UUID | +--------------------------------------+ | b90cdeb5-824b-11eb-83ad-0010e0dc7238 | +--------------------------------------+ |
You can see that the GTID associated with the INSERT have the same UUID as the global variable ‘server_uuid’.
This is because we used ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = LOCAL to replicate the INSERT statement.
Now if we set ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS = <UUID>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS='c9029522-824b-11eb-8395-0010e0dc7238'; ... mysql> show binlog events in 'replica-bin.000004'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | replica-bin.000004 | 4 | Format_desc | 2 | 125 | Server ver: 8.0.24-tr-debug-asan, Binlog ver: 4 | | replica-bin.000004 | 125 | Previous_gtids | 2 | 156 | | | replica-bin.000004 | 156 | Gtid | 1 | 242 | SET @@SESSION.GTID_NEXT= 'b90cdeb5-824b-11eb-83ad-0010e0dc7238:1' | | replica-bin.000004 | 242 | Query | 1 | 312 | BEGIN | | replica-bin.000004 | 312 | Table_map | 1 | 359 | table_id: 112 (test.t) | | replica-bin.000004 | 359 | Write_rows | 1 | 399 | table_id: 112 flags: STMT_END_F | | replica-bin.000004 | 399 | Xid | 1 | 430 | COMMIT /* xid=28 */ | | replica-bin.000004 | 430 | Gtid | 1 | 516 | SET @@SESSION.GTID_NEXT= 'c9029522-824b-11eb-8395-0010e0dc7238:1' | | replica-bin.000004 | 516 | Query | 1 | 586 | BEGIN | | replica-bin.000004 | 586 | Table_map | 1 | 633 | table_id: 112 (test.t) | | replica-bin.000004 | 633 | Write_rows | 1 | 673 | table_id: 112 flags: STMT_END_F | | replica-bin.000004 | 673 | Xid | 1 | 704 | COMMIT /* xid=37 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ |
You can see the UUID changed in the later transaction and it is the same as what was passed to ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS in CHANGE REPLICATION SOURCE.
Specifying the UUID manually can be useful to identify the source of each transactions, in case the server replicates from multiple source servers, or if there are clients that commits transactions on the same replica.
If the replication breaks in between running transactions, you will have to manually re-position the replica and do the troubleshooting, a limitation shared with position based replication topologies.
Conclusion:
Whenever possible, we recommend migrating all servers to use GTIDs. The new feature’s intention is to ease the migration in two cases. First, you can preview how it is for your workload to have GTIDs, on a replica that is not in production. Second, in the case that you do not have enough control to enable GTIDs on the source server, you can enable GTIDs on a downstream part of the topology where you do have control. In this case it is important to understand that the benefits of GTIDs only apply within the part of the topology where you have enabled GTIDs.
With this feature, we hope to make the migration to enable GTIDs even easier, so that more people unlock group replication, fail-over, and other features that ease the replication administration.