故障转移后 MySQL 节点加入组复制失败导致额外事务

Question

我有一个部署在 kubernetes(k8s) 上的 3 节点 mysql InnoDB 集群。每个 mysql 节点都是一个 k8s POD。为了测试可用性，我手动删除了对应的docker容器mysql主节点(使用命令'docker rm xxxx -f)。不出所料，k8s 成功重新创建了容器。但是，新创建的一个未能加入集群(更具体地说，未能加入组复制)。它提示该成员有交易不在当前组中。但是，额外的事务是将 SESSION.GTID_NEXT 设置为 AUTOMATIC 的操作。不知道为什么在binlog里，导致重新加入失败。有人会给我一些提示或建议吗？非常感谢!

MySQL 版本是 mysql-community-server-minimal-5.7.20-1。它是从 rpm 安装的。

InnodDB集群是通过采用现有的复制组创建的。这表示我首先创建了复制组，然后创建了集群。

MySQL日志是这样的:

2018-03-12T08:17:21.208124Z 0 [Note] Plugin group_replication reported: 'new state x_run' 2018-03-12T08:17:21.218361Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 925deea1-75db-4e92-86a9-00259c8d8078:1-50 > Group transactions: 925deea1-75db-4e92-86a9-00259c8d8078:1-2' 2018-03-12T08:17:21.218433Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.' 2018-03-12T08:17:21.218444Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option' 2018-03-12T08:17:21.218493Z 3 [Note] Plugin group_replication reported: 'Going to wait for view modification' 2018-03-12T08:17:21.218546Z 0 [Note] Plugin group_replication reported: 'A new primary with address t-baj3bsrg4cp000a1sp3g-mysql-0.mysql:3306 was elected, enabling conflict detection until the new primary applies all relay logs.' 2018-03-12T08:17:21.218597Z 15 [Note] Plugin group_replication reported: 'This server is working as primary member.' 2018-03-12T08:17:21.218634Z 0 [Note] Plugin group_replication reported: 'Group membership changed to t-baj3bsrg4cp000a1sp3g-mysql-0.mysql:3306, t-baj3bsrg4cp000a1sp3g-mysql-1.mysql:3306, on view 15208422427926210:3.' 2018-03-12T08:17:21.218655Z 0 [Note] Plugin group_replication reported: 'The member with address t-baj3bsrg4cp000a1sp3g-mysql-2.mysql:3306 was declared online within the replication group' 2018-03-12T08:17:21.218825Z 4 [ERROR] Plugin group_replication reported: 'Unable to update certification result on server side, thread_id: 136' 2018-03-12T08:17:21.218838Z 4 [ERROR] Plugin group_replication reported: 'Error at event handling! Got error: 1' 2018-03-12T08:17:21.218849Z 4 [ERROR] Plugin group_replication reported: 'Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.' 2018-03-12T08:17:21.218864Z 4 [Warning] Plugin group_replication reported: 'Skipping leave operation: concurrent attempt to leave the group is on-going.' 2018-03-12T08:17:21.218996Z 7 [Note] Error reading relay log event for channel 'group_replication_applier': slave SQL thread was killed

故障节点上显示的 Bin 日志事件是:

+-------------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+ | t-baj3bsrg4cp000a1sp3g-mysql-0-bin.000003 | 4 | Format_desc | 100 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | | t-baj3bsrg4cp000a1sp3g-mysql-0-bin.000003 | 123 | Previous_gtids | 100 | 190 | 925deea1-75db-4e92-86a9-00259c8d8078:1-50 | +-------------------------------------------+-----+----------------+-----------+-------------+-------------------------------------------+

binlog 文件中显示的 925deea1-75db-4e92-86a9-00259c8d8078:1-50 的事务:

仅供引用，根据 mysql 日志，设置 group_replication_allow_local_disjoint_gtids_join 选项对我有用。但是，请参阅选项的最新文档 group_replication_allow_local_disjoint_gtids_join ，它将在 mysql 5.7.22 中被删除。所以，我必须找出问题的根本原因。谢谢!

Answer 1

我的建议是重新检查您的基础架构，因为您发现您没有 1 笔额外交易。如果你查看你粘贴的信息

925deea1-75db-4e92-86a9-00259c8d8078:1-50 > 925deea1-75db-4e92-86a9-00259c8d8078:1-2 

1-50 与 1-2 意味着您有 48 笔额外交易。

此外，您正在查看一个新的 binlog 文件，因为您清除了旧的文件或进行了轮换。在这个新文件中，您看到的唯一信息是 Previous-GTID 信息是 925deea1-75db-4e92-86a9-00259c8d8078:1-50，这意味着较旧的 binlog 文件包含有关这 50 个事务的信息。

可以使用命令

 SHOW BINARY LOGS;

然后

SHOW BINLOG EVENTS [IN 'log_name']

获取有关旧事件的更多信息或使用 mysqlbinlog 实用程序检查其他文件。