mysql怎么保证一致性 mysql主从怎么保证数据一致性

技术分享 | 两个单机 MySQL 该如何校验数据一致性

业务有两个 MySQL 集群是通过 MQ 进行同步的，昨晚 MQ 出现异常，报了很多主键冲突，想请 dba 帮忙校验一下两个集群的数据是否一致。

创新互联公司专业为企业提供横山网站建设、横山做网站、横山网站设计、横山网站制作等企业网站建设、网页设计与制作、横山企业网站模板建站服务，10年横山做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

当接到这个需求的时候并没当回事，隐约有点印象 pt-table-checksum 能通过 dsn 实现 MySQL 的数据校验，所以当时就应承下来了。不曾想，啪啪打脸，回想起来真是草率了。

本文参考的是 pt-table-checksum 的校验逻辑，基于数据块去遍历每个表，然后比对 checksum 的值判断该块是否一致，本文主要是想聊聊我在实现数据校验脚本过程中遇到的问题以及解决思路，希望对大家有帮助。

利用线上的配置文件搭建一套主从环境。

这个用例将通过 dsn 方式连接从库。

这个用例将通过 dsn 方式连接从库，但是会将从库的复制链路 stop 掉，并清空复制信息。

熟悉 pt-table-checksum 的朋友应该都知道，该工具是基于主键(非空唯一键)进行扫描数据行，其实这个逻辑针对整型单列主键实现起来很简单，但是如果是联合主键且是字符型，好像就没那么简单了，有兴趣的可以思考一下。下面我先说一下大致的逻辑：

第一步：判断 _min_rowid 是否为空，为空就取该表的第一行，并记作 _min_rowid 。

第二步：根据 _min_rowid 作为条件进行扫描该表，取下一个数据块的数据，记录数据块的最后一行数据的主键值，记录 checksum 的值，并记下 _min_rowid 。

第三步：判断_min_rowid是否为空，非空重复第二步，为空退出检查。

通过上述三个步骤可以看到，如果是单列整型的主键，实现起来很简单，但是问题来了，业务的表的主键五花八门，有的是联合主键，有的是字符型的联合主键，还有整型+字符型的联合主键，那么上述的实现方式显然是有问题的。所以实现起来需要多考虑几个问题：

鉴于存在上述两个问题，可以参考如下实现逻辑：

假如有这么一个联合主键字段 primary key(a,b,c) 都是整型，该如何编写遍历 sql 呢？起初我的想法很简单，具体如下：

至此在编写校验脚本过程遇到的两个问题就算告一段落了，剩下的就是各种逻辑处理了，不过多赘述，有兴趣的可以自行阅读脚本文件。

本着最低程度影响业务，所以取消加锁逻辑。但是又要保证该数据块的数据一致性，如果这个数据块是个热数据，当前正在变更，那么校验的时候难免会不一致。所以只能通过多次校验实现，默认是校验20次，其中有一次校验结果是一致，就认为是一致的，如果前5次校验过程中，这个数据块的数据没有变化，也视为不一致（可能是因为延迟，也可能是真的不一致）。

pt-table-checksum 不校验表结构，改写时添加表结构的校验。

可以基于表的并行校验，可由用户指定并行数，但是脚本有个安全机制，如果用户指定的并行数大于当前 cpu 空闲核心数，就会按当前（空闲核心数-1）作为并行数。

添加网络监控，由用户指定网络上限百分比，当网卡流量超过这个百分比就暂停任务，等待网卡流量低于阈值才会继续任务。这个主要是出于对于中间件（mycat）的场景或者分布式数据库（tidb）的场景。

支持定时任务功能，用户可以使用这个功能规避业务高峰，仅在业务低峰进行数据校验。

不仅限于主从节点的校验，只要目标对象支持 MySQL 的标准 SQL 语法就能做数据校验。

校验逻辑是通过 SQL 采集目标节点的数据库，如果目标数据库系统当前存在异常，无疑是雪上加霜，将会触发未知问题，所以添加超时机制，单次取数据块的阈值是5s，超过5秒就放弃等待重试。测试发现，有时候即便触发超时了，但是 SQL 任务还是会在目标数据库的 processlist 中能看到，所以又添加了一个 kill 机制，超时后会触发一个 kill processlist id 的动作。另外为了避免 kill 错，在每个 SQL 对象添加了一个32位的 md5 值，每次 kill 的时候会校验这个 md5 值。

本工具借鉴 pt-table-checksum 工具思路改写，可以检查随意两个 mysql（支持 mysql sql 语法的数据库）节点的数据一致性。

基于主键以一个块遍历数据表，比对checksum的值，块的大小可通过参数指定。 （1）获取该表的第一个数据块的查询SQL。 （2）将两个目标节点的数据块的checksum的值，记录到临时文件，file1 file2。 （3）比对file1 file2是否一致。

第一步：先开启一个 screen 监控网络

第二步：新开启一个screen执行校验任务

（1）info.log 文件

（2）list目录

（3）md5 目录

（4）pri 目录

（5）res 目录

这是 table 目录下记录某个数据块不一致的一个例子

这是 diff 目录下记录某个数据行不一致的一个例子

（6）skip.log 文件

本工具是参考了 pt-table-checksum 工具的一些思路并结合自身经验进行改写，尚有很多不足之处，仅做学习交流之用， 如有线上环境使用需求，请在测试环境充分测试。

如何使MySQL前后端连接SESSION变量保持一致

最近在开发MySQL代理中间件过程中, 遇到这样一个问题: 当前后端连接不是一一对应的关系时, 对前端连接设置SESSION级别的变量时, 如何能使前端后变量的值保持一致?

一个直观的实现方式是, 拦截SET语句, 判断scope是SESSION时, 将变量的值保存在前端连接中. 当执行其他查询语句时, 在获取后端连接后, 先将前端连接中保存的变量逐一发送到后端连接, 然后执行查询语句, 释放后端连接时再将变量的值恢复.

这种实现方式存在几个显著的问题.

1 存在一定的性能损耗, 至少增加了1次与后端MySQL的交互. 但是, 这也是后端连接池方案都会遇到的问题, 前后端连接绑定不存在这种问题, 但是也就失去了连接池带来的好处.

2 需要判断参数值是否合法, 实现起来非常繁琐. 例如, 如果考虑支持字符集设置, 就要枚举出MySQL支持的所有字符集及字符序类型, 解析 SET NAMES 'utf8' COLLATE 'utf8_general_ci' 时, 对字符集和字符序进行合法性判断. 对于某些参数, 例如sql_mode, 还需要考虑MySQL版本之前的差异.

在这种实现方式下, 有几个优化点可以参考.

1 批量执行SET语句. MySQL语法层面支持 SET SESSION var1 = val1, SESSION var2 = val2; , 因此不论前端连接接收到的SET语句是单条的还是批量的, 在将这些kv值发往后端时, 可以整合成一条SQL, 从而减少与MySQL的交互次数, 提高执行效率.

2 后端连接按需重置. 当归还后端连接时, 不再重置连接, 而是在下一次获取连接时, 先判断连接中的变量值与前端连接是否全部相同, 如果全部相同, 则不需要重置, 可以直接使用, 否则, 将那些值不相同的变量设置到后端连接.

3 使用 COM_RESET_CONNECTION 命令重置连接. COM_RESET_CONNECTION命令可以将连接恢复到初始状态, 具体内容可参考文档: . 但是, 如果连接创建时的状态不是连接的默认状态, 就不能使用这种方式重置连接. 例如, 在启动mysql client时指定的字符集不是DEFAULT, 那么执行该命令后会错误地把连接的字符集设置成DEFAULT.

最后, 如果你有更好的解决方案, 欢迎讨论交流.

MySQL主备库数据一致性校验及修复

很多时候需要把一个从库提升为主库，但对从库和主库的数据一致性不敢保证，这时我们就可以利用 pt-table-checksum来检查主库数据的一致性，如果存在不一致的数据，我们可以利用pt-table-sync来修复这些不一致的数据。

在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。

下面通过实际的例子来解释该工具如何使用：

主库（10.8.23.209）数据：

从库（10.8.23.208）数据：

从库（10.8.23.210）数据：

很明显主备数据不一致，我们使用工具来检测下：

校验命令参数解释：

校验结果字段解释：

好了，命令以及常用参数都介绍了，一起解释下上面执行的效果，通过DIFFS 是1 就可以看出主从的表数据不一致。怎么不一致呢？ 通过指定—replicate=test.checksums 参数，就说明把检查信息都写到了checksums表中。

进入备库（10.8.23.208）中查看checksums表的信息：

进入备库（10.8.23.210）中查看checksums表的信息：

通过上面找到了这些不一致的数据，如何修复呢？利用另外一个工具 pt-table-sync。

高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。接着上面的复制情况，主库和从库的aaa表数据不一致，需要修复。

参数解释：

命令介绍完了，一起解释下执行的效果：通过（--print）打印出来了修复数据的sql语句，可以手动的去从行执行，让他们数据保持一致性。那能否直接执行？当然可以，通过(--execute)

没发现任何异常，然后检查主从数据的一致性：

主库（10.8.23.209）数据：

从库（10.8.23.208）数据：

从库（10.8.23.210）数据：

OK，数据已经保持一致了。

不过建议还是--print 打印出来的好，这样就可以知道那些数据有问题，可以人为的干预下。

不然直接执行了，出现问题之后不好处理。总之还是在处理之前做好数据的备份工作。

汗颜！工作10年去面试，被“MySQL怎么保证事物一致性”难倒了

阿牛去一家中意的公司面试，本以为凭借以往丰富的经验，肯定手到擒来，结果第一个问题，我就“出门右拐”了。

问题就是：MySQL是怎么保证事务一致性的？

回到家阿牛翻阅资料，终于搞懂了，在这里分享给大家。

定义

在搞清楚问题答案之前，先搞清楚以下几个名词以及大致的用处

redo log：

通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)、Innodb特有的，他在存储引擎层。循环写的，空间固定会用完。作用是crash-safe能力

binlog：

是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ” 是 MySQL 的 Server 层实现的，所有引擎都可以使用。是可以追加写入的,“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。作用是数据归档

undo log：

有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

SQL执行的过程

了解了以上名词之后，让我们看一下“一条更新SQL语句执行的过程是什么？”

如图1有几个关键步骤：

1、先查找记录所在的Innodb页在不在内存里；如果不在内存里则将记录所在的页加载在内存里；根据SQL语句在内存中将记录更新

2、将更新前的记录写入undolog

3、根据记录的更新值将变更写入redolog（buffer）中，并将状态变更为prepare

4、将变更记录到逻辑日志

5、redolog日志中的状态修改为commit，返回结束

至此：一条更新语句的过程结束

上面的步骤中有些同学可能会有一些疑问：为什么更新一条记录要把一整页数据加载到内存里答：因为Innodb引擎中，最小的存储单位是页为什么一定要加载到内存里？答：因为所有的计算操作都是在内存里，操作完成后最终才写回磁盘为什么要写入redolog，直接写入磁盘，然后写入binlog就好了啊？答：这将在下面会提到，请往后看

为了加深理解，准备了下面2张图辅助理解

以图3为例，让我们看看在每个步骤出现异常的时候，到底怎么保证事物一致性的吧！1、步骤123，所有的操作最多还只是内存里，如果出现宕机、断电等异常，  记录不会有任何变动，事物是一致的2、步骤4刚执行完，断电了，因为redolog还处在prepare状态，   这时候事物也是一致的3、步骤5记录binlog的过程中断电了，这时候要保证主从一致性，  事物也是不生效的，最终也是一致的4、步骤6、7如果中间任何一个时刻断电了，这时候情况就不一样了，事物是生效的，因为redolog、binlog的数据都是完整的，服务器重启后可以按照xid来去查看binlog、redolog中是否都存在，  都存在该事物就是生效的。上面就是怎么保证事务一致性的根本原因

为什么要使用redolog？

回答这个问题之前，我们先看看redolog用图形表示的

图4是redolog的形象一点的表现，并不是说redolog 长这个样子，只是为了更形象；一般情况下redolog一组4个文件，每个文件1个G，其中write pos是指redolog当前写到什么位置了，check point是指上次刷脏结束的位置，当write log和check point重合时，所有的进程停止，开始新一轮的刷脏操作。刷完后redolog清空开始下一轮的写入，往返重复。

可能这样表示有点抽象，让我们看下图5

从上图中可以看的更形象一点，在sql执行的时候，会有磁盘IO将数据页加载到内存，然后在内存中将数据修改，修改后的数据页在内存中叫做脏页（叫脏页因为和磁盘中的数据不一致啊），又因为在内存中容易丢失，所以将数据页的变更记录如redolog中，随着记录插入、更新等操作的增多，redolog空间慢慢的满了，这时候就开始刷脏操作了，page cleaner thread线程会将所有的脏页数据刷新到磁盘，使得变更最终被持久化到磁盘。

讲到这里一定还会有人不太理解，刷脏之前断电了咋办？

这就是redolog的另一个重要的作用，crash-safe能力，实现的逻辑是这样的，断电后内存的数据都没了，重启后读取redolog文件，因为redolog文件记录的是在Innodb页x的m处做了y的修改，所以根据redolog将涉及到的Innodb页重新加载到内存，根据redolog的记录将内存中的数据重新修改，这样就能恢复断电前的数据了。

                                                                 完

下期预告：还是MySQL，敬请期待

本文首发自： 程序员阿牛