DevOps开发运维
成长之路

mysql-binlog日志的GTID新特性

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;
赞(1)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图