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

　　作者：莫善
　　某互联网公司高级DBA。
　　本文来源：原创投稿
　　爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。需求介绍
　　业务有两个MySQL集群是通过MQ进行同步的，昨晚MQ出现异常，报了很多主键冲突，想请dba帮忙校验一下两个集群的数据是否一致。一、前言
　　当接到这个需求的时候并没当回事，隐约有点印象pttablechecksum能通过dsn实现MySQL的数据校验，所以当时就应承下来了。不曾想，啪啪打脸，回想起来真是草率了。
　　本文参考的是pttablechecksum的校验逻辑，基于数据块去遍历每个表，然后比对checksum的值判断该块是否一致，本文主要是想聊聊我在实现数据校验脚本过程中遇到的问题以及解决思路，希望对大家有帮助。二、测试dsn
　　利用线上的配置文件搭建一套主从环境。搭建步骤略
　　本例使用mysql5。7。26进行测试
　　本例使用perconatoolkit3。2。1进行测试1、校验主从数据一致性
　　这个用例将通过dsn方式连接从库。
　　配置dsn过程略。binpttablechecksumh192。168。1。1，umydba，ptest123456，P6666nocheckreplicationfiltersreplicatetest。checksumsnocheckbinlogformatddbatest1recursionmethoddsntperconaschema。dsnsCheckingifalltablescanbechecksummed。。。Startingchecksum。。。TSERRORSDIFFSROWSDIFFROWSCHUNKSSKIPPEDTIMETABLE0113T17：48：200000100。377dbatest1。dbatest
　　可以看到测试通过，能正常做校验。2、校验非主从数据一致性
　　这个用例将通过dsn方式连接从库，但是会将从库的复制链路stop掉，并清空复制信息。mysqlstopslave；QueryOK，0rowsaffected（0。01sec）mysqlresetslaveall；QueryOK，0rowsaffected（0。01sec）mysql
　　。binpttablechecksumh192。168。1。1，umydba，ptest123456，P6666nocheckreplicationfiltersreplicatetest。checksumsnocheckbinlogformatddbatest1recursionmethoddsntperconaschema。dsnsCheckingifalltablescanbechecksummed。。。Startingchecksum。。。Replicamysql2isstopped。Waiting。Replicamysql2isstopped。Waiting。Replicamysql2isstopped。Waiting。Replicamysql2isstopped。Waiting。
　　直接翻车，可以看到校验会提示从库【isstopped。Waiting】，这就尴尬了，pttablechecksum居然不支持非主从环境的数据校验，既然如此那只能另想他法了。三、开发工具遇到的问题1、解决复杂的联合主键问题（1）查询索引失效，或者查询报错问题
　　熟悉pttablechecksum的朋友应该都知道，该工具是基于主键（非空唯一键）进行扫描数据行，其实这个逻辑针对整型单列主键实现起来很简单，但是如果是联合主键且是字符型，好像就没那么简单了，有兴趣的可以思考一下。下面我先说一下大致的逻辑：
　　第一步：判断minrowid是否为空，为空就取该表的第一行，并记作minrowid。if〔z｛minrowid｝〕then拿出当前表的最小行id，每次拿数据的起始行minrowid（｛mysqlcomm｝NBeselectmin（pk）fromtable）fi
　　第二步：根据minrowid作为条件进行扫描该表，取下一个数据块的数据，记录数据块的最后一行数据的主键值，记录checksum的值，并记下minrowid。selectfromtablewherepk｛minrowid｝limitchecksize计算这个块的checksum值minrowid（｛mysqlcomm｝NBeselectmax（pk）from（selectpkfromtablewherepk｛minrowid｝orderbypklimitchecksize）a）记录下一个最小行pk值
　　第三步：判断minrowid是否为空，非空重复第二步，为空退出检查。if〔z｛minrowid｝〕thenbreakelsecontinuefi
　　通过上述三个步骤可以看到，如果是单列整型的主键，实现起来很简单，但是问题来了，业务的表的主键五花八门，有的是联合主键，有的是字符型的联合主键，还有整型字符型的联合主键，那么上述的实现方式显然是有问题的。所以实现起来需要多考虑几个问题：需要考虑主键是否是联合主键。
　　如果是联合主键，在取数据块的时候查询条件就是wherepk1xxxandpk2yyy需要考虑主键字段的数据类型是否是整型或字符型。
　　如果主键字段是字符型，在取数据块的时候查询条件就是wherepkxxx，否则查询将不会使用到索引。
　　鉴于存在上述两个问题，可以参考如下实现逻辑：获取主键字段列表，放在数组里priname（（｛mysqlcomm｝NBeselectCOLUMNNAMEfrominformationschema。columnswheretablenametableandtableschemadbandCOLUMNKEYPRI；））根据主键字段名获取字段的数据类型，放在关联数组里fortmpin｛priname〔〕｝do将各个主键字段的数据类型记录到字典，后文会判断主键字段的类型是否是字符型，如果是字符型就需要特殊处理（带引号）selectDATATYPEfrominformationschema。columnswheretableschemadbandtablenametableandCOLUMNKEYPRIandCOLUMNNAME｛tmp｝pritype〔｛tmp｝〕（｛mysqlcomm｝NBe｛｝2devnull）done根据字段的数据类型，如果是字符型就需要做特殊处理fortmpin｛priname〔〕｝do这步主要是解决将主键弄清楚，到底是单列主键还是多列，到底是整型还是其他，然后根据不同的类型拼接成一个字符串，主要是用来作为后面取数是否要加单引号因为整型取出来不用做特殊处理，但是非整型需要加上单引号，要不然作为where条件对比的时候肯定有问题if〔（grepicint｛pritype〔｛tmp｝〕｝）x！1x〕thenselectstrtmpconcat（，｛tmp｝，）pkstylestrelseselectstrtmp｛tmp｝fiif〔z｛selectstr｝〕thenselectstr｛selectstrtmp｝elseselectstr｛selectstr｝，｛selectstrtmp｝fidone这步的作用是说在取每个块的数据，需要记录minrowid的时候会根据主键的类型记录不一样的值，比如：
　　整型就记录成minrowid1
　　字符型就记录成minrowid1
　　整型字符型的联合主键就记录成minrowid1，1
　　字符型的联合主键就记录成minrowid1，2
　　这样在每次取数据块的时候where后面的条件既能正确的使用索引，也不至于因为是整形而没有带上引号而报错。（2）如何界定每个数据块的左区间的边界
　　假如有这么一个联合主键字段primarykey（a，b，c）都是整型，该如何编写遍历sql呢？起初我的想法很简单，具体如下：minrowid（xxx，yyy，zzz）selectfromwhere11andaxxxandbyyyandczzzorderbya，b，climitchecksize
　　乍一看好像逻辑没问题，但是实际跑脚本的时候发现这个逻辑不能完全扫完全表，后来经过多次测试校验，得出下面的逻辑sqlminrowid（xxx，yyy，zzz）selectfromwhere11and（（axxx）or（axxxandbyyy）or（axxxandbyyyandczzz））orderbya，b，climitchecksize
　　至此在编写校验脚本过程遇到的两个问题就算告一段落了，剩下的就是各种逻辑处理了，不过多赘述，有兴趣的可以自行阅读脚本文件。四、数据校验工具做了哪些改动1、取消forupdate
　　本着最低程度影响业务，所以取消加锁逻辑。但是又要保证该数据块的数据一致性，如果这个数据块是个热数据，当前正在变更，那么校验的时候难免会不一致。所以只能通过多次校验实现，默认是校验20次，其中有一次校验结果是一致，就认为是一致的，如果前5次校验过程中，这个数据块的数据没有变化，也视为不一致（可能是因为延迟，也可能是真的不一致）。
　　另外checksum状态是写到临时文件而非写到业务数据库。2、支持表结构校验
　　pttablechecksum不校验表结构，改写时添加表结构的校验。3、支持基于表的并行校验
　　可以基于表的并行校验，可由用户指定并行数，但是脚本有个安全机制，如果用户指定的并行数大于当前cpu空闲核心数，就会按当前（空闲核心数1）作为并行数。4、支持网络监控
　　添加网络监控，由用户指定网络上限百分比，当网卡流量超过这个百分比就暂停任务，等待网卡流量低于阈值才会继续任务。这个主要是出于对于中间件（mycat）的场景或者分布式数据库（tidb）的场景。5、支持定时任务功能
　　支持定时任务功能，用户可以使用这个功能规避业务高峰，仅在业务低峰进行数据校验。
　　指定了时间段执行校验任务，如果当天没校验完成，等到次日会继续校验。6、支持任意两个节点的校验
　　不仅限于主从节点的校验，只要目标对象支持MySQL的标准SQL语法就能做数据校验。7、添加超时机制及自杀机制
　　校验逻辑是通过SQL采集目标节点的数据库，如果目标数据库系统当前存在异常，无疑是雪上加霜，将会触发未知问题，所以添加超时机制，单次取数据块的阈值是5s，超过5秒就放弃等待重试。测试发现，有时候即便触发超时了，但是SQL任务还是会在目标数据库的processlist中能看到，所以又添加了一个kill机制，超时后会触发一个killprocesslistid的动作。另外为了避免kill错，在每个SQL对象添加了一个32位的md5值，每次kill的时候会校验这个md5值。
　　保留threadsrunning的监控，如果threadsrunning过大就会暂停校验，这部分监控逻辑是跟网络监控一起五、数据校验工具使用介绍
　　本工具借鉴pttablechecksum工具思路改写，可以检查随意两个mysql（支持mysqlsql语法的数据库）节点的数据一致性。
　　本工具仅供学习使用，如需检查线上的数据，请充分测试1、校验逻辑
　　基于主键以一个块遍历数据表，比对checksum的值，块的大小可通过参数指定。（1）获取该表的第一个数据块的查询SQL。（2）将两个目标节点的数据块的checksum的值，记录到临时文件，file1file2。（3）比对file1file2是否一致。不一致：重复（2）的操作，至多连续20次，还不一致会将该SQL记录到table目录
　　一致：跳到（4）
　　file1为空：表示该表遍历完成，直接跳到（5）（4）获取该表的下一个数据块的查询SQL。（5）检查通过就跳到（7），检查不通过调到（6）。（6）读取table目录校验不通过的SQL进行再次校验。
　　本次校验通过也视为数据一致
　　如果校验不通过，会将不一致的部分记录到diff目录（7）该表校验任务结束。2、功能介绍检查随意两个几点的数据一致性支持表结构的校验支持并发检查，基于表的并发支持指定时间，可以规避业务高峰期支持网络监控，如果网络超过阈值可以暂停校验不支持无主键（非空唯一键）的表不支持联合主键达到四个字段及以上的表3、安装教程（1）下载gitclonehttps：gitee。commoshancheckdataformysql。gitcdcheckdataformysql（2）配置编辑配置文件cdpathcheckdataformysqlvimconfcheck。conf
　　请结合实际情况根据注释提示进行相关配置修改工作路径sedisworkdir。workdircheckdataformysqlpathgstart。sh将这里的checkdataformysqlpath改成checkdataformysql的家目录的绝对路径4、使用说明（1）目录介绍moshandatagitcheckdataformysqltreeL2。confcheck。conffuncfcheckdiffformysql。shfcheckdiffforrow。shflogging。shfswitchtime。shlogmanager。shREADME。en。mdREADME。mdstart。sh3directories，9filesmoshandatagitcheckdataformysqlconf配置文件的目录，check。conf是配置文件log日志目录start。sh主程序manager。sh网络监控脚本，任务状态的管理脚本func目录是存放脚本的目录fcheckdiffformysql。sh校验数据块的脚本
　　fcheckdiffforrow。sh校验数据行，这个脚本是将fcheckdiffformysql。sh校验不通过的结果做进一步校验（2）帮助手册主程序moshandatagitcheckdataformysqlbashstart。shUsage：start。sh〔tchecktable〕需要检查的表列表，默认整库〔Tskipchecktable〕不需要检查的表，默认不过滤〔dcheckdb〕需要检查的库，默认是除了系统库以外的所有库〔Dskipcheckdb〕不需要检查的库，默认不过滤〔wthreads〕最大并发数〔llimittime〕哪些时间段允许跑校验，默认是所有时间，如需限制可以使用该参数进行限制，多个时间用英文逗号隔开15，1015表示1点到5点（包含1点和5点），或者10点到15点可以跑，需要注意都是闭区间的1，5，10，15表示1点，5点，10点，15点可以跑〔ftrue〕是否执行check操作，默认是false，只有为true的时候才会check〔h〕帮助信息moshandatagitcheckdataformysql
　　可以根据需求进行参数使用，如需规避业务高峰期在低峰执行校验任务，请使用l参数指定执行时间，如l15表示凌晨1点到5点执行校验任务，如果当天六点前没校验完成，会等到次日凌晨1点继续校验任务管理脚本moshandatagitcheckdataformysqlbashmanager。shhUsage：manager。sh〔astartstopcontinuepause〕监控任务的管理动作，数据校验任务的管理动作start：启动网络监控stoppausecontinue：连同校验脚本一起停掉暂停继续〔teth0〕网卡设备名，默认是eth0〔n50〕网卡流量超过这个百分比就暂停，等网卡流量小于这个就继续，默认是50〔h〕帮助信息moshandatagitcheckdataformysql可以根据实际网卡信息针对该网卡进行监控，当流量达到指定的阈值就会暂时暂停数据校验。这个脚本主要是针对使用了中间件，比如mycat（mysql到mycat）。或者是tidb（tikv到tidb），这种情况下回占用较多网络带宽。
　　该脚本必须要求在整个工具的家目录下执行（3）常用命令参考管理脚本相关bashmanager。shastartteth0n30启动eth0网卡的流量监控，流量达到30就暂停数据校验
　　bashmanager。shapause暂停监控及暂停数据校验任务
　　bashmanager。shacontinue继续监控及继续数据校验
　　bashmanager。shastop停止监控及停止数据校验主程序相关
　　bashstart。shftrueddbatestttest1l05仅校验dbatest库下的test表，且在0点到5点执行校验任务（4）测试用例校验通过场景每次执行校验任务的时候强制要清空log目录，所以请做好校验结果的备份执行校验任务的时候强烈建议开启screen有网卡监控需求，执行监控脚本时也强烈建议单独开启screen进行监控
　　第一步：先开启一个screen监控网络moshandatagitcheckdataformysqlscreenSchecknet3306moshandatagitcheckdataformysqlbashmanager。shastart〔2022011811：55：34〕〔1000Mbs〕〔RX：2MBS〕〔TX：2MBS〕〔2022011811：55：35〕〔1000Mbs〕〔RX：2MBS〕〔TX：4MBS〕〔2022011811：55：36〕〔1000Mbs〕〔RX：2MBS〕〔TX：2MBS〕〔2022011811：55：37〕〔1000Mbs〕〔RX：2MBS〕〔TX：3MBS〕〔2022011811：55：38〕〔1000Mbs〕〔RX：1MBS〕〔TX：2MBS〕〔2022011811：55：39〕〔1000Mbs〕〔RX：1MBS〕〔TX：2MBS〕〔2022011811：55：41〕〔1000Mbs〕〔RX：1MBS〕〔TX：2MBS〕〔2022011811：55：42〕〔1000Mbs〕〔RX：2MBS〕〔TX：8MBS〕
　　第二步：新开启一个screen执行校验任务moshandatagitcheckdataformysqlscreenScheckdata3306moshandatagitcheckdataformysqlbashstart。shddbatdbatest1ftrue〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔start。shstart。sh〕〔fprepare：130〕〔本次数据一致性检查开始〕〔2022011720：32：19〕〔警告〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：185〕〔本次数据一致性检查将检查如下库：〔dba〕〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：203〕〔正在检查dba库〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：249〕〔dba。dbatest1〕〔表结构一致〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：491〕〔dba。dbatest1〕〔1，1〕〔00d00h00m00s〕〔9。09，（0：0）1〕〔数据一致〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：491〕〔dba。dbatest1〕〔2，11〕〔00d00h00m00s〕〔100。00，（0：0）1〕〔数据一致〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：504〕〔dba。dbatest1〕〔检查完毕〕〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：242〕〔本次数据一致性检查完成〕〔通过〕moshandatagitcheckdataformysql
　　检查结束后会提示检查通过，否则就是检查不通过，如下面的用例。（5）测试用例校验不通过场景执行校验任务的时候强烈建议开启screenmoshandatagitcheckdataformysqlscreenScheckdata3306moshandatagitcheckdataformysqlbashstart。shddbatest1ftrue〔2022011720：32：09〕〔成功〕〔192。168。1。1〕〔start。shstart。sh〕〔fprepare：130〕〔本次数据一致性检查开始〕〔2022011720：32：09〕〔警告〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：185〕〔本次数据一致性检查将检查如下库：〔dbatest1〕〕〔2022011720：32：09〕〔成功〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：203〕〔正在检查dbatest1库〕〔2022011720：32：09〕〔警告〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：242〕〔dbatest1。dbatest〕〔表结构不一致〕〔atimename〕〔跳过该表的检查〕〔2022011720：32：09〕〔错误〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：232〕〔本次数据一致性检查完成〕〔不通过〕〔2022011720：32：09〕〔错误〕〔192。168。1。1〕〔start。shstart。sh〕〔fmain：237〕〔dbatest1。dbatest：tablestructureerr〕moshandatagitcheckdataformysql5、测试结果解读moshandatagitcheckdataformysqllsltotal444rwrr1rootroot450389Jan1811：56info。logdrwxrxrx2rootroot194Jan1811：56listdrwxrxrx2rootroot6Jan1811：56md5drwxrxrx6rootroot72Jan1811：56pridrwxrxrx5rootroot42Jan1811：52resrwrr1rootroot65Jan1811：56skip。logmoshandatagitcheckdataformysql
　　（1）info。log文件
　　校验的日志，会将数据库的数据是否一致一一记录，如下是一行日志记录。〔2022011720：32：19〕〔成功〕〔192。168。1。1〕〔funcfcheckdiffformysql。sh〕〔fcheckdiffformysql：491〕〔dba。dbatest1〕〔2，11〕〔00d00h00m00s〕〔100。00，（0：0）1〕〔数据一致〕〔2022011720：32：19〕第一段是记录日志的时间
　　〔成功〕第二段是日志状态
　　〔192。168。1。1〕第三段是产生日志的机器ip
　　〔funcfcheckdiffformysql。sh〕第四段是哪个文件产生的日志
　　〔fcheckdiffformysql：491〕第五段是哪个函数：行号产生的日志
　　〔dba。dbatest1〕第六段是针对哪个db哪个表产生的日志
　　〔2，11〕第七段是数据块的左右闭区间
　　〔00d00h00m00s〕第八段是针对该表的数据校验总执行的时间
　　〔100。00，（0：0）1〕第九段是执行进度，其中小括号部分表示：（校验通过的表个数：校验不通过的表个数）总共需要校验的表的个数
　　〔数据一致〕第十段是数据一致状态。
　　（2）list目录rwrr1rootroot77Jan1811：52dbaing。listrwrr1rootroot77Jan1811：56dba。list
　　这个目录会针对每个db记录两个文件，一个是已经校验通过的表，另一个是正在校验的表。
　　（3）md5目录
　　保存数据块的checksum临时目录，可以忽略
　　（4）pri目录
　　这个目录会针对每个db都创建一个目录，然后记录每个表当前校验的数据块的最后一行数据的pk（pklist）值
　　（5）res目录
　　这个目录是记录校验结果的目录，会有三个子目录drwxrxrx2rootroot6Jan1811：52diffdrwxrxrx2rootroot6Jan1811：52rowdrwxrxrx2rootroot6Jan1811：56tabletable：fcheckdiffformysql。sh脚本会将校验不通过的数据块的SQL记录在这里。这个目录会按db创建目录，将记录校验不通过的数据块的SQL语句格式如下：tabledbtable。log
　　row：fcheckdiffforrow。sh脚本会读取table目录的SQL语句进行再次校验，然后产生的的临时文件存在row目录，可以忽略
　　diff：fcheckdiffforrow。sh脚本会读取table目录的SQL语句进行再次校验，然后产生的再次校验不通过的部分存记录到这个目录，格式如下：diffdb。table。num。diff
　　这是table目录下记录某个数据块不一致的一个例子settxisolationREPEATABLEREAD；setinnodblockwaittimeout1；SELECT127d04065afd91d587bbb19bc16037a6：mobilebind，COUNT（）AScnt，COALESCE（LOWER（CONV（BITXOR（CAST（CRC32（CONCATWS（，id，uid，count，score，timestamp，info，mobileinfo，del，CONCAT（ISNULL（id），ISNULL（uid），ISNULL（count），ISNULL（score），ISNULL（timestamp），ISNULL（info），ISNULL（mobileinfo），ISNULL（del））））ASUNSIGNED）），10，16）），0）AScrcFROM（selectfromtdbspamoltp。mobilebindwhere11andid667930554orderbyidlimit10000）a如果校验某个数据块发现两个节点数据不一致会记录这个SQL
　　一来是方便fcheckdiffforrow。sh脚本再次校验
　　二来是方便用户再次确认是真的不一致还是因为这是热数据，在校验的时候正在频繁被修改
　　这是diff目录下记录某个数据行不一致的一个例子7974c7974667930554216424914958665955841009488951345727972752022011016：01：302022011110：45：04｛dlvBoid：7877725947093058957｝667930554667930554116424914958665955841009488951345727972752022011016：01：302022011016：32：01｛dlvBoid：7877725947093058957｝667930554
　　同一个主键，如果数据不一致会以这样的格式记录到diff目录
　　（6）skip。log文件
　　检查不通过在log目录都会生成一个skip。log文件，里面记录了哪些表被跳过检查及跳过原因，如果检查通过就不会有这个文件。moshandatagitcheckdataformysqllsllogskip。logrwrr1rootroot37Jan1720：35logskip。logmoshandatagitcheckdataformysqlcatlogskip。logdbatest1。dbatest：tablestructureerrmoshandatagitcheckdataformysql六、写在最后
　　本工具是参考了pttablechecksum工具的一些思路并结合自身经验进行改写，尚有很多不足之处，仅做学习交流之用，如有线上环境使用需求，请在测试环境充分测试。