mysql gtid 还是pxc_PXC中的GTIDs

基本环境：PXC 5.7.19 Row+Gtid，3节点

一、Galera GTID vs MySQL GTID

1.1、Galera GTID vs MySQL GTID

Both kinds of GTIDs are using the same format: .

For Galera, is generated when the cluster is bootstrapped. This is shared by all nodes.

For MySQL, is the server uuid. So it is easy to identify from which server a transaction originates.

我们先来看下PXC环境Galera GTID和MySQL GTID分别指的是什么

# node1的server_uuid

mydba@192.168.85.132,3326 [replcrash]> select @@server_uuid;+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| 31304898-67d8-11e8-bb20-000c29c1025c |

+--------------------------------------+

1 row in set (0.00sec)

# node1写入一条数据

mydba@192.168.85.132,3326 [replcrash]> insert into py_user(name,add_time,server_id) select left(uuid(),32),now(),@@server_id;

Query OK,1 row affected (0.02sec)

Records:1 Duplicates: 0 Warnings: 0# node1查看gtid_executed

mydba@192.168.85.132,3326 [replcrash]> select @@global.gtid_executed;+----------------------------------------+

| @@global.gtid_executed |

+----------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1 |

+----------------------------------------+

1 row in set (0.00sec)

# node2的server_uuid

mydba@192.168.85.133,3326 [replcrash]> select @@server_uuid;+--------------------------------------+

| @@server_uuid |

+--------------------------------------+

| 570e2242-6898-11e8-bcae-000c2900c99c |

+--------------------------------------+

1 row in set (0.01sec)

# node2写入一条数据

mydba@192.168.85.133,3326 [replcrash]> insert into py_user(name,add_time,server_id) select left(uuid(),32),now(),@@server_id;

Query OK,1 row affected (0.45sec)

Records:1 Duplicates: 0 Warnings: 0# node2查看gtid_executed

mydba@192.168.85.133,3326 [replcrash]> select @@global.gtid_executed;+------------------------------------------+

| @@global.gtid_executed |

+------------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1-2 |

+------------------------------------------+

1 row in set (0.00 sec)

View Code

上面@@server_uuid对应的是MySQL，@@global.gtid_executed对应的是Galera

node1、node2上执行的insert语句使用相同的，仿佛它们在同一台实例上执行，并且与node1/node2自己的@@server_uuid不同

我们可以把Cluster当作MySQL复制中的Master，所有节点获取来自相同的数据

1.2、UUID

我们知道MySQL GTID的存储于$/datadir/auto.cnf，Galera GTID的是否也存在相关的文件？如果你足够仔细的话，你会发现Galera GTID与$/datadir/grastate.dat存在下面的关联

# 查看grastate.dat

[root@ZST1 data]#catgrastate.dat

# GALERA saved state

version:2.1uuid: f0f0e99b-6896-11e8-87cb-138e77b260da

seqno:-1safe_to_bootstrap:0[root@ZST1 data]#

# Galera GTID与$/datadir/grastate.dat的关联

Galera GTID + grastate.dat

= 0f0f1664-9769-ee17-7834-ec71884d9f25 + f0f0e99b-6896-11e8-87cb-138e77b260da= ffffffff-ffff-ffff-ffff-ffffffffffff

View Code

Cluster的第一节点启动时，会读取grastate.dat文件，用它里面的uuid信息构建Galera GTID的。如果删除$/datadir/grastate.dat文件，下次重新启动集群，它的Galera GTID会改变(类似删除$/datadir/auto.cnf文件，重新启动实例MySQL GTID也会改变)。Galera GTID与MySQL GTID之间并没有对应关系，它们是相互独立的。PXC中的@@global.gtid_executed的初始化可以参考：gtid_executed和gtid_purged变量是如何初始化的，只要明白Galera GTID的来源，其他完全可以套用MySQL GTID

PXC中其实还存在其他uuid

mydba@192.168.85.132,3326 [replcrash]> show status like '%uuid%';+--------------------------+--------------------------------------+

| Variable_name | Value |

+--------------------------+--------------------------------------+

| wsrep_local_state_uuid | f0f0e99b-6896-11e8-87cb-138e77b260da |

| wsrep_gcomm_uuid | 8ab36be3-6897-11e8-bf70-cee6630daa30 |

| wsrep_cluster_state_uuid | f0f0e99b-6896-11e8-87cb-138e77b260da |

+--------------------------+--------------------------------------+

3 rows in set (0.00sec)

wsrep_cluster_state_uuid

Provides thecurrent State UUID. This is a unique identifier for the current state of the cluster and the sequence ofchanges it undergoes.

wsrep_local_state_uuid

The UUIDof the state stored onthis node.

wsrep_gcomm_uuid

Displays thegroup communications UUID.

View Code

wsrep_cluster_state_uuid、wsrep_local_state_uuid对应的就是grastate.dat中的uuid，wsrep_gcomm_uuid是group communications uuid

二、什么情况下当前节点的事务不会复制到其他节点

普通主从环境，从库@@global.gtid_executed只会显示主库执行的事务。如果我们在从库手动执行事务，那么从库@@global.gtid_executed就会包含本地的信息

对于PXC来说，它支持多节点写入，任何节点的DML操作先在本地执行，在提交前将所有的变更和对应主键保存到write-set，集群下发write-set给每个节点(包括DML执行的节点)，使用主键验证各节点是否可以应用write-set。如果验证失败，则删除write-set并回滚原始事务；如果验证成功，提交事务并应用其他未完成的write-set。

是不是任何节点上的任何事务都能复制所有节点？有没有可能某个事务只存在于一个节点？

• DML On MyISAM

最大的可能是对MyISAM表的DML操作，会使用server_uuid记录binlog，并且不会被复制到集群中的其他节点！

# node1创建myisam引擎的表

mydba@192.168.85.132,3326 [replcrash]> create table py_user_myisam(id int not null primary key) engine=myisam;

Query OK,0 rows affected (0.66sec)

# 往py_user_myisam表中插入数据

mydba@192.168.85.132,3326 [replcrash]> insert into py_user_myisam(id) select 1;

ERROR1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (replcrash.py_user_myisam) that resides in non-transactional storage engine with pxc_strict_mode = ENFORCING orMASTER

# 修改mode为permissive

mydba@192.168.85.132,3326 [replcrash]> set global pxc_strict_mode='permissive';

Query OK,0 rows affected (0.00sec)

# 重新插入数据

mydba@192.168.85.132,3326 [replcrash]> insert into py_user_myisam(id) select 1;

Query OK,1 row affected, 1 warning (0.09sec)

Records:1 Duplicates: 0 Warnings: 1# node1查看gtid_executed

mydba@192.168.85.132,3326 [replcrash]> select @@global.gtid_executed;+----------------------------------------------------------------------------------+

| @@global.gtid_executed |

+----------------------------------------------------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1-3,31304898-67d8-11e8-bb20-000c29c1025c:1 |

+----------------------------------------------------------------------------------+

1 row in set (0.00sec)

# 查看binlog events

mydba@192.168.85.132,3326 [replcrash]> show binlog events in 'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

| mysql-bin.000003 | 4 | Format_desc | 1323326 | 123 | Server ver: 5.7.19-17-29.22-log, Binlog ver: 4 |

| mysql-bin.000003 | 123 | Previous_gtids | 1323326 | 194 | 0f0f1664-9769-ee17-7834-ec71884d9f25:1 |

| mysql-bin.000003 | 194 | Gtid | 1333326 | 259 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:2' |

| mysql-bin.000003 | 259 | Query | 1333326 | 327 | BEGIN |

| mysql-bin.000003 | 327 | Table_map | 1333326 | 390 | table_id: 121 (replcrash.py_user) |

| mysql-bin.000003 | 390 | Write_rows | 1333326 | 476 | table_id: 121 flags: STMT_END_F |

| mysql-bin.000003 | 476 | Xid | 1333326 | 507 | COMMIT /*xid=3*/ |

| mysql-bin.000003 | 507 | Gtid | 1323326 | 572 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:3' |

| mysql-bin.000003 | 572 | Query | 1323326 | 726 | use `replcrash`; create table py_user_myisam(id int not null primary key) engine=myisam |

| mysql-bin.000003 | 726 | Gtid | 1323326 | 791 | SET @@SESSION.GTID_NEXT= '31304898-67d8-11e8-bb20-000c29c1025c:1' |

| mysql-bin.000003 | 791 | Query | 1323326 | 873 | BEGIN |

| mysql-bin.000003 | 873 | Table_map | 1323326 | 935 | table_id: 224 (replcrash.py_user_myisam) |

| mysql-bin.000003 | 935 | Write_rows | 1323326 | 975 | table_id: 224 flags: STMT_END_F |

| mysql-bin.000003 | 975 | Query | 1323326 | 1058 | COMMIT |

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

14 rows in set (0.05sec)

# node2查看gtid_executed

mydba@192.168.85.133,3326 [replcrash]> select @@global.gtid_executed;+------------------------------------------+

| @@global.gtid_executed |

+------------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1-3 |

+------------------------------------------+

1 row in set (0.10 sec)

View Code

创建MyISAM表的语句使用DDL机制，它能复制到各节点；但是对MyISAM表执行DML操作，它使用@@server_uuid(类似常规MySQL复制)，不会复制到Cluster的其他节点，但是它能复制到node1节点的异步Slave

• Flush Commands

# node1执行flush privilegesmydba@192.168.85.132,3326 [replcrash]> flush privileges;

Query OK,0 rows affected (0.26sec)

# node1查看gtid_executed

mydba@192.168.85.132,3326 [replcrash]> select @@global.gtid_executed;+----------------------------------------------------------------------------------+

| @@global.gtid_executed |

+----------------------------------------------------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1-4,31304898-67d8-11e8-bb20-000c29c1025c:1 |

+----------------------------------------------------------------------------------+

1 row in set (0.00sec)

# node1查看binlog events

mydba@192.168.85.132,3326 [replcrash]> show binlog events in 'mysql-bin.000003';+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

| mysql-bin.000003 | 4 | Format_desc | 1323326 | 123 | Server ver: 5.7.19-17-29.22-log, Binlog ver: 4 |

| mysql-bin.000003 | 123 | Previous_gtids | 1323326 | 194 | 0f0f1664-9769-ee17-7834-ec71884d9f25:1 |

| mysql-bin.000003 | 194 | Gtid | 1333326 | 259 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:2' |

| mysql-bin.000003 | 259 | Query | 1333326 | 327 | BEGIN |

| mysql-bin.000003 | 327 | Table_map | 1333326 | 390 | table_id: 121 (replcrash.py_user) |

| mysql-bin.000003 | 390 | Write_rows | 1333326 | 476 | table_id: 121 flags: STMT_END_F |

| mysql-bin.000003 | 476 | Xid | 1333326 | 507 | COMMIT /*xid=3*/ |

| mysql-bin.000003 | 507 | Gtid | 1323326 | 572 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:3' |

| mysql-bin.000003 | 572 | Query | 1323326 | 726 | use `replcrash`; create table py_user_myisam(id int not null primary key) engine=myisam |

| mysql-bin.000003 | 726 | Gtid | 1323326 | 791 | SET @@SESSION.GTID_NEXT= '31304898-67d8-11e8-bb20-000c29c1025c:1' |

| mysql-bin.000003 | 791 | Query | 1323326 | 873 | BEGIN |

| mysql-bin.000003 | 873 | Table_map | 1323326 | 935 | table_id: 224 (replcrash.py_user_myisam) |

| mysql-bin.000003 | 935 | Write_rows | 1323326 | 975 | table_id: 224 flags: STMT_END_F |

| mysql-bin.000003 | 975 | Query | 1323326 | 1058 | COMMIT |

| mysql-bin.000003 | 1058 | Gtid | 1323326 | 1123 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:4' |

| mysql-bin.000003 | 1123 | Query | 1323326 | 1219 | use `replcrash`; flush privileges |

+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

16 rows in set (0.00sec)

# node2查看gtid_executed

mydba@192.168.85.133,3326 [replcrash]> select @@global.gtid_executed;+------------------------------------------+

| @@global.gtid_executed |

+------------------------------------------+

| 0f0f1664-9769-ee17-7834-ec71884d9f25:1-4 |

+------------------------------------------+

1 row in set (0.00sec)

# node2查看binlog events

mydba@192.168.85.133,3326 [replcrash]> show binlog events in 'mysql-bin.000004';+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

| mysql-bin.000004 | 4 | Format_desc | 1333326 | 123 | Server ver: 5.7.19-17-29.22-log, Binlog ver: 4 |

| mysql-bin.000004 | 123 | Previous_gtids | 1333326 | 194 | 0f0f1664-9769-ee17-7834-ec71884d9f25:1 |

| mysql-bin.000004 | 194 | Gtid | 1333326 | 259 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:2' |

| mysql-bin.000004 | 259 | Query | 1333326 | 349 | BEGIN |

| mysql-bin.000004 | 349 | Table_map | 1333326 | 412 | table_id: 121 (replcrash.py_user) |

| mysql-bin.000004 | 412 | Write_rows | 1333326 | 498 | table_id: 121 flags: STMT_END_F |

| mysql-bin.000004 | 498 | Xid | 1333326 | 529 | COMMIT /*xid=3*/ |

| mysql-bin.000004 | 529 | Gtid | 1323326 | 594 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:3' |

| mysql-bin.000004 | 594 | Query | 1323326 | 748 | use `replcrash`; create table py_user_myisam(id int not null primary key) engine=myisam |

| mysql-bin.000004 | 748 | Gtid | 1323326 | 813 | SET @@SESSION.GTID_NEXT= '0f0f1664-9769-ee17-7834-ec71884d9f25:4' |

| mysql-bin.000004 | 813 | Query | 1323326 | 909 | use `replcrash`; flush privileges |

+------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------+

11 rows in set (0.00 sec)

View Code

实验表明，flush命令被正常复制到其他节点，binlog中使用的是Cluster UUID。看来Issuing FLUSH commands on a node will create a local transaction on GTID-configured clusters已经被修复●-●

• SQL_LOG_BIN=0

在当前节点 SET SQL_LOG_BIN=0，不会记录对应的binlog，难道就没有对应的write-set？这种情况对于事后也没办法知道原始事务发生在哪个节点，没GTID，没binlog，太阔怕･ω･

三、参考文档