• MySQL主从一致性校验之pt-table-checksum

    背景

    当我开始决定使用这个工具进行主从数据一致性校验的时候, 我存在以下几点疑虑

    1. 使用它时是否会对线上数据库服务器的负载造成影响, 它会占用多少资源。

    2. 使用它时是否会将对应的库或表锁住, 影响用户的访问。

    3. 它是否会改变主库的数据来达成数据一致性, 我不希望主库的数据有改变,即使它是错误的。

    4. 他的速度有多快, 会不会需要很长的时间。

    5. 如果使用过程中出现失败, 或者我把他kill掉, 会导致什么后果。

    6. 在他进行checksum的时候, 有可能还有其他更新操作在进行, 这样是如何保证一致性的呢?

    所以接下来我将围绕这几点问题, 逐一进行验证和试验,希望可以有一个好的体验。

    安装配置

    1. 安装percona-toolkit:

    2. 授权一个用户来做主从数据的校验,在主库执行,直接同步到从库

    工作原理

    本来以为只是个脚本,可以看看源码就捋清楚了,但是显然我高估了自己的代码阅读能力也低估了这个脚本的复杂性。粗略一看,他竟然有13000 多行代码,虽然有很大一部分是注释,但本人对perl语言基本是0基础,所以辗转决定去网上搜了一下,先拿来充饥,后续有时间有需求了,再自己钻研源代码。

    percona的官方文档里面说道, pt-table-checksum工具设计的最初目的比较明确简洁, 他并没有支持各种复杂的checksum技术。pt-table-checksum只是校验,所以它把checksum结果存储到统计表,然后把执行过的sql语句记录到binlog中,任务就算完成。语句级的复制把计算逻辑传递到从库,并在从库执行相同的计算。

    所以pt-table-checksum的算法本身并不在意从库的延迟,不会影响计算结果的正确性,(但是我们还是会检测延迟,因为延迟太多会影响业务,所以总是要加–max-lag来限流)。而且他在同一时间只针对一张表做checksum, 所以他并不会消耗大量的内存资源,官方数据显示在具有数千个库表的实例上,以及上亿的行数下可以正常执行。而且它在检测过程中以块为单位, 对于大的表可以区分为多个块, 从而避免锁表( 根据唯一索引将表切分为块)检测时会自动判断复制延迟、 master的负载, 超过阀值后会自动将检测暂停,并调整块的大小。  他默认每个块的大小为1000行, 而每个块的目标处理时间为0.5s

    为了保证主数据库服务的安全,该工具实现了许多保护措施:

    1. 自动设置 innodb_lock_wait_timeout 为1s,避免引起锁等待。
    2. 默认当数据库有25个以上的并发查询时,pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值。
    3. 当用 Ctrl+C 停止任务后,工具会正常的完成当前 chunk 检测,下次使用 --resume选项启动可以恢复继续下一个 chunk。

    注: 可能有人会不理解为什么主从延迟不会影响pt-table-checksum的检测。 因为binlog是顺序执行的, 从库虽然延迟了, 但是等过一会追上来的时候, 他的执行的状态和主库那会执行的状态是一样的。

    我们先说说 pt-table-checksum 工具工作的具体过程吧:

    1. 基础环境设置与参数检查,主要包括: 

    • 将replicate table 添加至 ignore tables 。
    • 链接到主库,修改 SQL_MODE = ONLY_FULL_GROUP_BY。
    • binlog_format 检查,如果你的不是statement模式,则它会尝试改变,改变失败的话会报错:

      Failed to $sql: $EVAL_ERROR\n”

      . “This tool requires binlog_format=STATEMENT, ”

      . “but the current binlog_format is set to ”

      .”$original_binlog_format and an error occurred while ”

      . “attempting to change it.  If running MySQL 5.1.29 or newer, ”

      . “setting binlog_format requires the SUPER privilege.  ”

      . “You will need to manually set binlog_format to ‘STATEMENT’ ”

      . “before running this tool.\n”

    • 尝试修改session级别的各级级别为RR  ,RC的话会报错:

    If the –replicate table is InnoDB and the default server “

                . “transaction isolation level is not REPEATABLE-READ then “

                . “checksumming may fail with errors such as \”Binary logging not “

                . “possible. Message: Transaction level ‘READ-COMMITTED’ in “

                . “InnoDB is not safe for binlog mode ‘STATEMENT’\”.  In that “

                . “case you will need to manually set the transaction isolation “

                . “level to REPEATABLE-READ.

    2. 查找出对应的从库,并进行一系列信息检测

    • 判断以什么方式获取slave信息,根据—recursion-method参数。
    • 再次确认主从上的复制模式是否都是statement ,否则将报错(之前只判断了主库上的)
    • 判断check-slave-lag参数,看你是否指定了要检查某些slave的lag延迟,判断replicate-check参数确定是否进行数据校验,判断 check-replication-filters 参数是否存在,如果你的db存在数据库过滤规则设置,并且没有指定 —nocheck-replication-filters则会报错。
    • 检查replicas table是否存在,不存在的话,检查是否能创建,如果不能则报错
    • 查找主库或者从库是否有复制过滤规则
    • 检查是否有–resume参数,如果有则尝试继续接着上次检测结果进行。

    3. 开始checksum计算

    • 开始获取表,一个个的计算
    • 如果是表的第一个chunk,那么chunk-size一般为1000;除非你设定了chunk-size参数,如果不是表的第一个chunk,那么采用19步中分析出的结果。
    • 检查表结构,进行数据类型转换等,生成checksum的sql语句。
    • 根据表上的索引和数据的分布,选择最合适的split表的方法。
    • 开始checksum表的第一个chunk。
    • 默认在chunk一个表之前,先删除上次这个表相关的计算结果。除非指定了–resume参数。
    • 先根据explain的结果,获取判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了,为了不影响线上性能,这个chunk将被忽略。
    • 把要checksum的行加上for update锁,并计算,在主库通过replace into语句将计算方式同步到从库上去。
    • 把计算结果直接更新在主库的master_crc master_count列中,这部分代码我贴出来方便理解。

    • 在主库执行replace into 语句,更新 this_cnt, this_crc等字段,顺着主从复制同步到从库。
    • 等待从库追上主库。如果发现延迟最大的从库延迟超过max-lag秒或者主库的max-load超过某个阈值,pt工具在这里将暂停。(注意,也就是说每次检测完一个chunk,就会在主库更新master_crc master_count等字段,并且等待从库同步上来)。
    • 等待从库执行完这个chunk的checksum,生成汇总的统计结果。每个表汇总并统计一次(注:也就是说,只有等一个表的chunk被checksum完了,才会实现一次统计,我们在使用的时候就会发现,每个表它会输出一个结果),我把这部分的代码也粘出来方便理解。

    • 循环每个表,直到结束。

    重要选项

    此部分内容只是简单记录,详情最好查阅官网,很详细全面:官网地址

    1、安全选项

    –check-replication-filters 是否检查复制过滤规则

    –check-slave-tables 检查是否所有从库都有被检查的表和列

    –chunk-size-limit 每个chunk最大不能超过这个大小,超过就忽略它

    –recursion-method  指定复制检查的方式,默认情况下使用SHOW PROCESSLIST,如果非标准的3306端口,就使用SHOW SLAVE HOSTS的方式,推荐使用dsn方式,手动指定.

    2、限速选项

    –check-interval 多久检查一次主从延迟、主库负载是否达到上限

    –check-slave-lag 是否只检查这个从库的延迟

    –max-lag 最大延迟,超过这个就等待 –max-load 最大负载,超过这个就等待

    –max-load 最大负载,超过这个就等待

    3、过滤选项

    –databases 只检查某些库

    –tables 只检查某些表 这些过滤选项在修复不一致数据后,检查修复效果很有用。

    4、其他选项

    –resume 因某种原因中断,下次接着执行,不用从头开始

    –chunk-time 每个chunk被计算的时间,一般默认为0.5秒

    –explain 只做执行计划,并不真正执行

    具体操作

    参照之前的安装配置小节进行安装配置,然后即可根据自己的情况进行主从校验。 比如我当前有如下主从结构需要检测主从不一致的情况。

    %e5%b1%8f%e5%b9%95%e5%bf%ab%e7%85%a7-2017-04-18-%e4%b8%8a%e5%8d%8810-26-29

    在如上架构中,我故意在四个数据库实例上新建了一个名为db1的数据库,并且新建了一张名为ceshi的表,且在四个实例中各插入了不同的数据,模拟出四个实例互不相同的数据场景

    比如我想在Master上使用pt-table-checksum工具检测主库与三个从库的不一致的地方,看起来很简单啊,执行如下语句:

    哇,不到1s就完成了,而且成功检测出了主从不一致,DIFFS = 1 ,于是我兴高采烈的去查看每个从库里面的结果:

    咦?   不对啊,我发现和主库直连的10.65.2.124 和 10.65.2.125 的检测结果是正常的,this_crc 都不等于 master_crc,但是奇怪的是126的结果竟然和124的一模一样,这不应该啊。 据我们上面的原理来看,他应该是在主库上执行了replace into 语句然后顺着主从复制同步下去,那么126的this_crc结果也应该是和其他的都不一样才对啊。

    到这里我不禁疑惑,源代码是不会骗人的,那么肯定就是我配置的问题。于是我决定分析并找出原因:

    首先,我在master上面打开binlog查看当我执行pt-table-checksum 的时候发生了什么:

    首先,会尝试创建记录主从同步的库表 xxxxxxx.checksums 。

    然后,因为我没有 –resume,所以在checksum计算cash表之前,会先delete之前的结果。

    然后,通过replace into 语句将计算结果插入checksums表。

    最后,更新这个表的 master_crc 、master_cnt 两列。

    这和我们想象中的一模一样,没有什么问题啊,别急,我们接着分析10.65.2.124 的binlog日志:

    这下发现问题了,在124记录binlog的时候,他把replace into语句替换成了insert into 了,难怪126会和它的结果一抹一样,我很快意识到这可能是因为主从复制的模式是ROW模式导致的,于是我逐一在数据库实例上执行了修改复制模式为statement:

    set global binlog_format = “statement”;

    再次测试,还是不行!!! 这我就郁闷了,最后折腾了半天发现原来是我没有重新启动主从复制,所以复制模式的修改并没有生效,于是我在各个从库执行了如下操作:

    stop slave;   start slave;

    再次测试,终于成功了  !!!!! 每个实例都检测出了和主库不一样的this_crc。


    嘘,终于长舒了一口气,上面我是为了测试采取验证和解决问题的,其实针对上面的情况我可以采用DSN模式更简单直接,下面来介绍一下。

    首先,我在主库上创建了一个DSN表:

    然后再主库上以指定DSN的方式执行即可:

    这样的话,即使我不改变各个实例上的主从复制模式,只要我使用 –no-check-binlog-format 参数即可成功检测所有从库的主从不一致信息了。

    结果查询

    检测结果:

    TS            :完成检查的时间。

    ERRORS        :检查时候发生错误和警告的数量。

    DIFFS         :检测到有不同的chunk的数目。

    ROWS          :表的行数。

    CHUNKS        :被划分到表中的块的数目。

    SKIPPED       :由于错误或警告或过大,则跳过块的数目。

    TIME          :执行的时间。

    TABLE         :被检查的表名。

    检测完成之后会将检测结果自动存储在数据库中(默认是percona.checksums表,可通过replicate参数指定)。

    这上面就记录了从库与主库的一些crc值得记录,所以我们可以用下面的SQL语句去查看有哪些主从不一致的表;

    select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc);

    简单总结

    本次试验过程中,遇到问题的时候没有静下心来逐一测试排查,而是不断的无脑测试,导致浪费了很多时间。 在此应该敲响警钟,遇到问题应该冷静思考,沉着判断,计划性的动手解决,而不是胡乱的无脑测试。

    现在再来回顾我在最开始的那个问题:

    1. 本次测试我的表里有接近两万行数据, 不多, 几乎瞬间完成。 每个chunk只用了不到0.1s,对系统负载几乎没造成什么负担。 虽然这样的结论比较粗糙,但还是能看出来这个工具对服务器负载的影响挺低的。   并且他本身支持限速,限负载。
    2. 它确实是会用X锁将对应的chunk锁住,但是影响比较小,不会造成表锁以及死锁。 也就是说他的锁级别是chunk粒度的写锁。 每次锁住大概控制在0.5s以内。
    3. 他默认不会改变主库的数据, 他的原理是以主库的数据为准,进行一系列数据校验。
    4. 速度比我想象中的还是快一些。
    5. 如果使用过程中失败,不会对线上的数据造成影响。 而且下次可以继续接着检测。
    6. 通过对chunk加锁来保证一致性。

     

    参考:

    https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-checksum.html

    http://www.nettedfish.com/blog/2013/06/04/check-replication-consistency-by-pt-table-checksum/