ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
source link: https://www.percona.com/blog/useful-gtid-feature-for-migrating-to-mysql-gtid-replication-assign_gtids_to_anonymous_transactions/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
A Useful GTID Feature for Migrating to MySQL GTID Replication – ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS
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.
You can read the Percona doc if you are unaware of how GTID replication works:
https://www.percona.com/doc/percona-server/5.6/flexibility/online_gtid_deployment.html
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
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
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
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.
mysql> create table gtid_test1 (id int ); Query OK, 1 row affected (0.01 sec) |
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
| 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:
mysql> select @@gtid_mode; +-------------+ | @@gtid_mode | +-------------+ +-------------+ 1 row in set (0.00 sec) |
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
STAY UP-TO-DATE With Percona!
Join 50,000+ of your fellow open-source enthusiasts! Our newsletter provides updates on Percona open source software releases, technical resources, and valuable MySQL, MariaDB, PostgreSQL, and MongoDB-related articles. Get information about Percona Live, our technical webinars, and upcoming events and meetups where you can talk with our experts.
By submitting my information I agree that Percona may use my personal data in send communication to me about Percona services. I understand that I can unsubscribe from the communication at any time in accordance with the Percona Privacy Policy.
Author
Gaurav has worked as a DBA for more than 7 years, he has worked in healthcare and finance. He likes cricket, travelling and trekking. He Joined percona in 2021.
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK