gtid的新特性assign_gtids_to_anonymous_transactions
2023-01-23 15:17 abce 阅读(145) 评论(0) 编辑 收藏 举报在MySQL8.0.23之前，想创建一个主从环境，主库不开启GTID、从库开启GTID，这是不可能的
MySQL8.0.23中引入了一个新特性：assign_gtids_to_anonymous_transactions，支持主从复制环境中，主库禁用GTID、从库开启GTID。从库必须设置gtid_mode=on，之后就不能再修改，除非你移除assign_gtids_to_anonymous_transactions设置。
assign_gtids_to_anonymous_transactions可接受的设置：
·off：默认不开启该特性
·local：返回本地server的uuid，和全局变量server_uuid相同
·uuid：指定一个有效的uuid，在生成事务的gtid时需要
语法：
change replication source to assign_gtids_to_anonymous_transactions=off|local|<uuid>;
下面来演示一下该新特性：
最初，主从都是没有开启GTID
主库：
mysql> select @@enforce_gtid_consistency,@@gtid_mode; +----------------------------+-------------+ | @@enforce_gtid_consistency | @@gtid_mode | +----------------------------+-------------+ | OFF | OFF | +----------------------------+-------------+ 1 row in set (0.00 sec)
从库：
mysql> select @@enforce_gtid_consistency,@@gtid_mode; +----------------------------+-------------+ | @@enforce_gtid_consistency | @@gtid_mode | +----------------------------+-------------+ | OFF | OFF | +----------------------------+-------------+ 1 row in set (0.00 sec)
在主库上创建一张表并检查binlog事件：
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)
从库的binlog事件：
| 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)
从库端设置assign_gtids_to_anonymous_transactions=local(设置前要开启gtid_mode=on)，并插入一条记录
从库：
mysql> select @@gtid_mode; +-------------+ | @@gtid_mode | +-------------+ | ON | +-------------+ 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)
你可以看到binlog在从库binlogs中有UUID。
