In managed services, we get requests to migrate from traditional to GTID-based replication. However, the customer does not want to first enable the GTID on the source node (production). Before MySQL 8.0.23, replication from the disabled GTID source to an enabled GTID replica was impossible.
In this blog, I will talk about a new MySQL feature introduced in 8.0.23, which allows MySQL to replicate from a GTID-disabled source to GTID-enabled replica. You can enable GTID assignment on a replication channel using the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS.
Note: The replica must have gtid_mode=ON set, which cannot be changed afterward unless you remove the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS setting.
Acceptable inputs
OFF: As the name suggests, it keeps this feature turned off
Local: Returns the local server UUID, same as server_uuid global variable
UUID: Specify a valid UUID which will be used while generating GTID transactions
Syntax
1 | CHANGE REPLICATION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=OFF|LOCAL|<UUID>; |
Let’s demonstrate the feature ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS.
Initially, we keep the GTID replication disabled on the source and replica node.
Source: GTID replication is disabled
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select @@enforce_gtid_consistency,@@gtid_mode; +----------------------------+-------------+ | @@enforce_gtid_consistency | @@gtid_mode | +----------------------------+-------------+ | OFF | OFF | +----------------------------+-------------+ 1 row in set (0.00 sec) |
Replica: GTID replication disabled
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select @@enforce_gtid_consistency,@@gtid_mode; +----------------------------+-------------+ | @@enforce_gtid_consistency | @@gtid_mode | +----------------------------+-------------+ | OFF | OFF | +----------------------------+-------------+ 1 row in set (0.00 sec) |
Let’s create a table on the source and check binlog events.
1 2 | mysql> create table gtid_test1 (id int ); Query OK, 1 row affected (0.01 sec) |
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 | mysql> show binlog events in 'binarylogs.000008'; +---------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+ | binarylogs.000008 | 4 | Format_desc | 3 | 126 | Server ver: 8.0.30-22, Binlog ver: 4 | | binarylogs.000008 | 126 | Previous_gtids | 3 | 157 | | | binarylogs.000008 | 157 | Anonymous_Gtid | 3 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binarylogs.000008 | 234 | Query | 3 | 365 | use `test_gtid`; create table gtid_test1 (id int ) /* xid=196 */ | | binarylogs.000008 | 365 | Anonymous_Gtid | 3 | 444 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binarylogs.000008 | 444 | Query | 3 | 524 | BEGIN | | binarylogs.000008 | 524 | Table_map | 3 | 585 | table_id: 101 (test_gtid.gtid_test1) | | binarylogs.000008 | 585 | Write_rows | 3 | 625 | table_id: 101 flags: STMT_END_F | | binarylogs.000008 | 625 | Xid | 3 | 656 | COMMIT /* xid=198 */ | +---------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+ 9 rows in set (0.00 sec) |
Replica: binlog events on replica node
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | | binarylogs.000014 | 1176 | Query | 3 | 1251 | BEGIN | | | binarylogs.000014 | 1350 | Xid | 3 | 1381 | COMMIT /* xid=149 */ | | binarylogs.000014 | 1381 | Anonymous_Gtid | 3 | 1465 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binarylogs.000014 | 1465 | Query | 3 | 1596 | use `test_gtid`; create table gtid_test1 (id int ) /* xid=162 */ | | binarylogs.000014 | 1596 | Anonymous_Gtid | 3 | 1682 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binarylogs.000014 | 1682 | Query | 3 | 1757 | BEGIN | | binarylogs.000014 | 1757 | Table_map | 3 | 1818 | table_id: 99 (test_gtid.gtid_test1) | | binarylogs.000014 | 1818 | Write_rows | 3 | 1858 | table_id: 99 flags: STMT_END_F | | binarylogs.000014 | 1858 | Xid | 3 | 1889 | COMMIT /* xid=164 */ | +-----------------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------+ 22 rows in set (0.00 sec) |
Let’s enable the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=local on the replica and insert a record.
Note: Make sure that gtid_mode=on before enabling the ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=local
Replica Node:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> select @@gtid_mode; +-------------+ | @@gtid_mode | +-------------+ | ON | +-------------+ 1 row in set (0.00 sec) |
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | mysql> CHANGE REPLICATIION SOURCE TO ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS=LOCAL; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show binlog events in 'binarylogs.000017'; +-----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ | binarylogs.000017 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30-22, Binlog ver: 4 | | binarylogs.000017 | 126 | Previous_gtids | 1 | 197 | 867e5079-8420-11ed-a0bf-1260d715ed11:1-12 | | binarylogs.000017 | 197 | Gtid | 3 | 283 | SET @@SESSION.GTID_NEXT= '867e5079-8420-11ed-a0bf-1260d715ed11:13' | | binarylogs.000017 | 283 | Query | 3 | 358 | BEGIN | | binarylogs.000017 | 358 | Table_map | 3 | 419 | table_id: 99 (test_gtid.gtid_test1) | | binarylogs.000017 | 419 | Write_rows | 3 | 459 | table_id: 99 flags: STMT_END_F | | binarylogs.000017 | 459 | Xid | 3 | 490 | COMMIT /* xid=183 */ | +-----------------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> select @@server_uuid; +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | 867e5079-8420-11ed-a0bf-1260d715ed11 | +--------------------------------------+ 1 row in set (0.00 sec) |
You can see the binlog has server UUID in replica binlogs. I hope this blog post will help you migrate to GTID replication even easier.
Reference:
https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-assign-anon.html
Thanks for blogging about this Gaurav. I missed this new feature release in 8.0.23 and I am learning about this via your post.
I find super interesting that this is included in MySQL 8 since January 2021 (8.0.23 released on 2021-01-18). This is basically implementing my trick to migrate to GTID online in 5.6. More about this trick in my 2018 post Unforeseen use case of my GTID work: replicating from AWS Aurora to Google CloudSQL.