【实践经验分享】MySQL改表工具ptosc在主从数据同步中的妙用

Percona XtraDB Cluster ( 简称 PXC ) 是 Percona 公司开源的实现 MySQL 高可用的解决方案。它将 Percona Server 和 Percona XtraBackup 与 Galera 库集成，以实现多主同步复制。和 MySQL 传统的异步复制相比，能够保证数据的强一致性，任何时刻任意节点上的数据状态都是完全一致的，并且整个架构实现了去中心化，所有节点都是对等的，即允许在任意节点上进行写入和读取，集群会把数据状态同步至其他所有节点。Qunar 内部使用架构图如下：

PXC 高可用切换不同于传统的主从切换，业务无需再忍受短暂的数据库只读和闪断。以 namespace 服务名的方式对外服务，对业务层屏蔽的数据库集群节点真实的 ip 和端口，客户端通过配置中心拿到集群的拓扑信息，实现了再切换过程中业务平滑的过渡到新的写节点，对业务透明。

在一次数据库的迁移操作中，将节点从配置中心下线后发现仍有应用在连接该数据库，推测为应用使用 IP+port 的方式直接连接数据库进行读写操作，在沟通开发同学进行数据源变更后，发现该 PXC 集群中的某张表在各集群的数据不一致，经过排查发现 write 节点的数据远多于其他节点，且另外两个PXC 节点的数据一致，故怀疑是在 MMM 迁移 PXC 的过程中丢失了部分数据，导致现有集群数据不一致。

重做数据后发现 PXC 节点间数据不一致的数据仍然存在，然而此时各节点 wsrep_last_committed 一致，这证明集群同步状态是正常的，各个节点均执行了相同的事务，在创建测试表进行写入测试后，证明该集群的同步状态正常。进而通过查看各节点 binlog 发现除主节点外的其他节点并未执行该操作。

自行查找原因无果后向竹峰求助，在提示下查看该表的存储引擎，发现该表引擎为 MyISAM ，据 Percona 官方文档中介绍：

Replication works only with InnoDB storage engine.

确定出现数据不一致的原因为表的存储引擎不符合PXC集群的限制。

上图为 PXC 集群事务提交的流程图，由此可以看出，Galera  Cluster 实现全局一致的前提条件是存储引擎支持事务，所以 MyISAM 或任何其他非事务性存储引擎是 PXC 集群所不支持。

操作及后果

清楚数据未进行同步的原因后，就要开始考虑如何解决问题。首先思考的是对业务来讲，能不能直接把 MyISAM 表的存储引擎改为 InnoDB ，考虑到业务同学不需要关心底层数据存储的方式，InnoDB 可以完全兼容 MyISAM 的日常使用，所以决定不需要通知业务同学直接进行修改。

因为该表数据量很小，修改存储引擎锁表时间很短，故通过直接在各节点修改该表存储引擎的方式修复该问题。我们通过执行语句 ALTER TABLE test.sbtest1 engine=innodb 来修改存储引擎。

执行成功之后，看到该表的存储引擎已经修改成功，以为问题得到了解决。大概过了一分钟之后，报警直接打电话了，发现是两台从实例都同时挂了，抓紧查看错误日志(下图为复现时的日志)。

发现挂掉的两个实例，原因都是对该表的insert语句在apply时失败，进而导致该实例停止服务，根据自己运维PXC的经验，出现这种日志的原因，都是数据不一致导致的。更清楚地讲，是在修改存储引擎前，该表的修改操作不同步，所以各节点的数据是不同的，当修改存储引擎后该表的操作可以同步了，在执行Apply的时候，就会出现执行报错的问题，进而自杀退出集群，这是PXC自己本身的特性，下面我们详细讲述。

出现该问题的原因

Galera Cluster 的数据同步是基于验证的复制，是一种乐观的同步复制机制，一个将要被复制的事务(称为写集)，不仅包括被修改的表记录，还包括了这个事务产生的所有 Binlog，每一个节点在复制事务时，都会拿这些写集与正在 APPLY 队列的写集作对比，如果没有冲突的话，这个事务就可以继续提交或 APPLY ，这时这个事务就被认为提交了。之后在数据库层面，还需要继续做事务上的提交操作。这种方式的复制也被称为虚拟的同步复制，实际上是一种逻辑上的同步。因为每个节点的写入和提交操作还是独立的。假设集群中的每个节点数据都是同步的，同时在数据写入时，都有写集验证机制，所以理论上不会出现不一致的情况。

但是在本例中，异常表的存储引擎为 MyISAM 不进行同步，导致在修改存储引擎前的数据是不一致的。当使用 ALTER 语句直接修改表的存储引擎时，因为在每个节点上面，都是同样地执行这个 ALTER 语句，这样导致在修改引擎之后，数据没有变，数据的不一致问题并没有改变。

所以在表的存储引擎修改为 InnoDB 之后，业务对该表所请求的 DML 语句所产生的 binlog ，在其他节点 APPLY 的过程中会发现对应的数据（主库 DML 请求所产生的修改）不一致，表现是数据已经存在或者数据不存在，此时 PXC 对于此问题时如何处理呢？

当 PXC 集群出现数据不一致，比如主库(相对)执行 update 成功，而从库无该条数据，说明此时数据不一致，那么此时 Galera Cluster 所采取的方式时将出现不一致数据的节点踢出集群，实际情况是，执行时报错(数据不一致时 Row 模式 Binlog 执行时会报错)的节点会自己执行 shutdown ，从而退出集群。本例中的现象就是数据不一致的节点出现自杀的现象。所以在执行完上述的 DDL 操作后，该表的写入操作也会在集群中进行同步，此时在变更前不会被发现的数据不一致问题就会出现，进而导致该节点自杀。

上面已经讲了，因为主库节点是完整的，而两个从节点的数据是没有同步的，在修改存储引擎之后，数据可以同步了，此时会发现两个从节点都会遇到数据不一致的问题，他们的 APPLY 都会失败，所以导致了我们这个问题，在改完存储引擎之后的没多久时间内，三个节点的 PXC 集群，只剩下了一个节点，其它两个节点都因为数据不一致而自己 shutdown 了。

定位了问题的原因后为了避免后续再出现数据不一致的现象，重做了两个读节点的数据，解决了此次出现的问题。

为了根治这个问题，我们巡检了所有数据库表，发现我们的线上 PXC 集群中仍有数十张 MyISAM 表，那么就需要寻找其他的解决方案。

pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.

When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.

从上述流程中可以得知，pt-online-schema-change 执行 DDL 操作时，会创建一个新的 InnoDB 引擎的新表，在写节点将原表的数据依次插入到新表中，从而实现了各节点中新表的数据和存储引擎是一致的，在 rename 操作后即实现了数据覆盖，可以保证新表在各节点的数据是完全一致的，故而在执行完 rename 之后，能够实现新表在各个节点数据一致。然而在前面的处理方式中，直接执行修改存储引擎的 ddl 语句只会修改该表存储引擎，并不会修改各个节点中该表的数据，无法实现数据覆盖。

在确定使用 pt-online-schema-change 工具可以实现需求后，在测试环境进行测试，首先创建 MyISAM 表并写入数据，表结构如下

报错信息中明确写到 cluster 集群的 MyISAM 表不能使用该工具，此时去翻看 pt-online-schema-change 工具的官方文档，发现了以下信息。

pt-online-schema-change works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer, but there are two limitations: only InnoDB tables can be altered, and wsrep_OSU_method must be set to TOI (total order isolation). The tool exits with an error if the host is a cluster node and the table is MyISAM or is being converted to MyISAM (ENGINE=MyISAM), or if wsrep_OSU_method is not TOI. There is no way to disable these checks.

pt 工具对修改 MyISAM 做了限制，可以想到，他是为了安全而考虑的，就是怕数据会被覆盖，而我们这里的目标，就是想通过数据的覆盖，来解决数据不一致的问题，让数据变为一致的，所以个人认为 pt 工具是可以修改 PXC 集群存储引擎的。

在确定了自己的判断之后，与竹峰讨论使用 pt-online-schema-change 修改 MyISAM 表是否可行，竹峰认为这样操作是可以的，至于在修改时 pt 工具报错的问题，建议看看 PT 源码。看下有没有参数，可以控制不报错，或者可以将代码临时修改。看了 PT 的代码后，发现没有参数可以控制，但看到对存储引擎检查的代码如下：

很简单，将这个代码块儿注释起来，就不会再报错了。然后在测试环境进行测试，变更顺利完成。修改后的表结构如下：

完成 pt-online-schema-change 的测试后，通过数据库工单平台完成了剩余表的存储引擎的修改，有效的避免了存储引擎带来的风险。

五、总结

参考文档

招募贴：

END