MySQL学习笔记GTID 介绍
GTID(Global Transaction ID) 5.6 版本新加的特性,5.7中做了加强 5.6 中不开启,没有这个功能. 5.7 中的GTID,即使不开也会有自动生成 SET @@SESSION.GTID_NEXT= 'ANONYMOUS' 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 它的官方定义如下： GTID = source_id（server id） ：transaction_id（事务号） 7E11FA47-31CA-19E1-9E56-C43AA21293967:29
重要参数介绍
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true systemctl restart mysqld 演示操作： Master [(none)]>create database gtid charset utf8; Query OK, 1 row affected (0.01 sec) Master [(none)]>show master status ; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000004 | 326 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql [(none)]>select @@server_uuid; mysql [(none)]>system cat /data/mysql/auto.cnf [auto] server-uuid=7233d683-0932-11eb-9511-000c29159620 Master [(none)]>use gtid Database changed Master [gtid]>create table t1 (id int); Query OK, 0 rows affected (0.01 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 489 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>create table t2 (id int); Query OK, 0 rows affected (0.01 sec) Master [gtid]>create table t3 (id int); Query OK, 0 rows affected (0.02 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 815 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>begin; Query OK, 0 rows affected (0.00 sec) Master [gtid]>insert into t1 values(1); Query OK, 1 row affected (0.00 sec) Master [gtid]>commit; Query OK, 0 rows affected (0.00 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 1068 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) Master [gtid]>begin; Query OK, 0 rows affected (0.00 sec) Master [gtid]>insert into t2 values(1); Query OK, 1 row affected (0.00 sec) Master [gtid]>commit; Query OK, 0 rows affected (0.01 sec) Master [gtid]>show master status ; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000004 | 1321 | | | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql [gtid]>show binlog events in 'mysql-bin.000002'; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | | | mysql-bin.000002 | 154 | Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= '7233d683-0932-11eb-9511-000c29159620:1' | | mysql-bin.000002 | 219 | Query | 6 | 323 | create database aab charset utf8 | | mysql-bin.000002 | 323 | Gtid | 6 | 388 | SET @@SESSION.GTID_NEXT= '7233d683-0932-11eb-9511-000c29159620:2' | | mysql-bin.000002 | 388 | Query | 6 | 486 | use `gtid`; create table t1 (id int) | | mysql-bin.000002 | 486 | Gtid | 6 | 551 | SET @@SESSION.GTID_NEXT= '7233d683-0932-11eb-9511-000c29159620:3' | | mysql-bin.000002 | 551 | Query | 6 | 648 | use `gtid`; create table t2(id int) | | mysql-bin.000002 | 648 | Gtid | 6 | 713 | SET @@SESSION.GTID_NEXT= '7233d683-0932-11eb-9511-000c29159620:4' | | mysql-bin.000002 | 713 | Query | 6 | 785 | BEGIN | | mysql-bin.000002 | 785 | Table_map | 6 | 830 | table_id: 232 (gtid.t1) | | mysql-bin.000002 | 830 | Write_rows | 6 | 870 | table_id: 232 flags: STMT_END_F | | mysql-bin.000002 | 870 | Xid | 6 | 901 | COMMIT /* xid=33 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 13 rows in set (0.00 sec)
基于GTID进行查看binlog
具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids跳过 mysqlbinlog --skip-gtids --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004
GTID的幂等性
开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了 --skip-gtids 跳过幂等性，生成全新的 mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:1-3' /data /binlog/mysql-bin.000004 >/tmp/bin.sql set sql_log_bin=0; source /tmp/binlog.sql set sql_log_bin=1;