生产环境使用 pt-table-checksum 检查MySQL数据一致性

公司数据中心从托管机房迁移到阿里云，需要对mysql迁移（replication）后的数据一致性进行校验，但又不能对生产环境使用造成影响，pt-table-checksum 成为了绝佳也是唯一的检查工具。

<code>pt-table-checksum</code> 是 percona-toolkit 的组件之一，用于检测mysql主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。

<code>pt-table-checksum</code> 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，<code>pt-table-checksum</code> 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。

为了减少对数据库的干预，<code>pt-table-checksum</code>还会自动侦测并连接到从库，当然如果失败，可以指定<code>--recursion-method</code>选项来告诉从库在哪里。它的易用性还体现在，复制若有延迟，在从库 checksum 会暂停直到赶上主库的计算时间点（也通过选项<code>--</code>设定一个可容忍的延迟最大值，超过这个值也认为不一致）。

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

自动设置 <code>innodb_lock_wait_timeout</code> 为1s，避免引起

默认当数据库有25个以上的并发查询时，<code>pt-table-checksum</code>会暂停。可以设置 <code>--max-load</code> 选项来设置这个阀值

当用 ctrl+c 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 <code>--resume</code> 选项启动可以恢复继续下一个 chunk

1\. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效。 3\. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。 5\. 开始获取表，一个个的计算。 6\. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么采用19步中分析出的结果。 7\. 检查表结构，进行数据类型转换等，生成checksum的sql语句。 8\. 根据表上的索引和数据的分布，选择最合适的split表的方法。 9\. 开始checksum表。 10\. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–resume。 14\. 根据explain的结果，判断chunk的size是否超过了你定义的chunk-size的上限。如果超过了，为了不影响线上性能，这个chunk将被忽略。 15\. 把要checksum的行加上for update锁，并计算。 17-18\. 把计算结果存储到master_crc master_count列中。 19\. 调整下一个chunk的大小。 20\. 等待从库追上主库。如果没有延迟备份的从库在运行，最好检查所有的从库，如果发现延迟最大的从库延迟超过max-lag秒，pt工具在这里将暂停。 21\. 如果发现主库的max-load超过某个阈值，pt工具在这里将暂停。 22\. 继续下一个chunk，直到这个table被chunk完毕。 23-24\. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。 25-26\. 循环每个表，直到结束。

校验结束后，在每个从库上，执行如下的sql语句即可看到是否有主从不一致发生：

<code>--replicate-check</code>：执行完 checksum 查询在percona.checksums表中，不一定马上查看结果呀 —— yes则马上比较chunk的crc32值并输出diffs列，否则不输出。默认yes，如果指定为<code>--noreplicate-check</code>，一般后续使用下面的<code>--replicate-check-only</code>去输出diff结果。

<code>--replicate-check-only</code>：不在主从库做 checksum 查询，只在原有 <code>percona.checksums</code> 表中查询结果，并输出数据不一致的信息。周期性的检测一致性时可能用到。

<code>--nocheck-binlog-format</code>：不检测日志格式。这个选项对于 row 模式的复制很重要，因为<code>pt-table-checksum</code>会在 master和slave 上设置<code>binlog_format=statement</code>（确保从库也会执行 checksum sql），mysql限制从库是无法设置的，所以假如行复制从库，再作为主库复制出新从库时（a-&gt;b-&gt;c），b的checksums数据将无法传输。（没验证）

<code>--replicate=</code> 指定 checksum 计算结果存到哪个库表里，如果没有指定，默认是 percona.checksums 。

但是我们检查使用的mysql用户一般是没有 create table 权限的，所以你可能需要先手动创建：

生产环境中数据库用户权限一般都是有严格管理的，假如连接用户是<code>repl_user</code>（即直接用复制用户来检查），它应该额外赋予对其它库的 select ，lock tables 权限，如果后续要用 pt-table-sync 就就需要写权限了。对percona库有写权限：

注：

为了减少不必要的麻烦，确保你的 repl_user@'xxx' 用户能同时登陆主库和从库

<code>--create-replicate-table</code> 选项会自动创建 percona.checksums 表，但也意味着赋予额外的 <code>create table</code>权限给 percona_tk@'xxx' 用户。默认yes

process用于自动发现从库信息，super权限用于set binlog_format。

<code>--no-check-replication-filters</code> 表示不需要检查 master 配置里是否指定了 filter。 默认会检查，如果配置了 filter，如 replicate_do_db,replicate-wild-ignore-table,binlog_ignore_db 等，在从库checksum就与遇到表不存在而报错退出，所以官方默认是yes（<code>--check-replication-filters</code>）但我们实际在检测中时指定<code>--databases=</code>，所以就不存在这个问题，干脆不检测

<code>--empty-replicate-table</code>：每个表checksum开始前，清空它之前的检测数据（不影响其它表的checksum数据），默认yes。当然如果使用<code>--resume</code>启动检测数据不会清空。

当启用<code>--noempty-replicate-table</code>即不清空时，不计算计算chunk,只计算。

<code>--databases=</code>，<code>-d</code>：要检查的数据库，逗号分隔。用脚趾头想也知道 <code>--databases-regex</code> 正则匹配要检测的数据库，<code>--ignore-databases[-regex]</code>忽略检查的库。filter选项。

<code>--tables=</code>，<code>-t</code>：要检查的表，逗号分隔。如果要检查的表分布在不同的db中，可以用<code>--tables=dbname1.table1,dbnamd2.table2</code>的形式。同理有<code>--tables-regex</code>，<code>--ignore-tables</code>，<code>--ignore-tables-regex</code>。<code>--replicate</code>指定的checksum表始终会被过滤。

<code>--recursion-method</code>：发现从库的方式。pt-table-checksum 默认可以在主库的 <code>processlist</code> 中找到从库复制进程，从而识别出有哪些从库，但如果使用是非标准3306端口，会导致找不到从库信息。此时就会自动采用<code>host</code>方式，但需要提前在从库 my.cnf 里面配置<code>report_host</code>、<code>report_port</code>信息，如：

最终极的办法是<code>dsn</code>，dsn指定的是某个表（如 percona.dsns ），表行记录是改主库的（多个）从库的连接信息。适用以下任一情形：

主库不能自动发现从库

不想在从库添加额外配置（因为要重启）

主从检测连接用户信息不一样

多个从库时只想验证指定从库的一致

我比较倾向使用dsn的方式。这个dsns表只需要在执行 <code>pt-table-checksum</code> 命令的服务器上能够访问到就行。这里纠正一个认识，网上很多人说 pt-table-checksum 要在主库上执行，其实不是的，我的mysql实例比较多，只需在某一台服务器上安装percona-toolkit，这台服务能够同时访问主库和从库就行了。具体用法见后面实例。

场景：

标准端口3306，只检查某一个库的关键表

一主一从，binlog不是row模式

同网段复制，percona_tk@'192.168.5.%' 具备该有的权限：

这是最简单的方式，把要连接和检查的信息交代就行了：

如果是首次运行，会在主库自动创建 percona.checksums 表。

输出结果：

ts ：完成检查的时间戳。

errors ：检查时候发生错误和警告的数量。

diffs ：不一致的chunk数量。当指定 <code>--no-replicate-check</code> 即检查完但不立即输出结果时，会一直为0；当指定 <code>--replicate-check-only</code> 即不检查只从checksums表中计算crc32，且只显示不一致的信息（毕竟输出的大部分应该是一致的，容易造成干扰）。

rows ：比对的表行数。

chunks ：被划分到表中的块的数目。

skipped ：由于错误或警告或过大，则跳过块的数目。

time ：执行的时间。

table ：被检查的表名

非标准端口13306，只检查以 d_ts 开头的所有库

一主二从，binlog是row模式，其中一从在阿里云ecs上，主库是无法直接访问该从库的

检测用的账号因为不是%，所以不一样

以下是我环境的情况

master_host:13306 主库

replica_host:3306 从库

ptcheck_host pt-table-checksum所在服务器

dsn_dbhost，记录从库（连接）dsns的数据库

最优的方式就是dsn指定从库了。在从库或从库同网段主机里装上 percona-toolkit。

在dsn_dbhost 数据库实例上创建dsns表：

如果有多个实例要检查，可以创建多个类似的dsns表。上面的percona_tk用户只是用来访问dsn库。插入从库信息：

dsns记录 dsn 列格式如 <code>h=replica_host,u=repl_user,p=repl_pass</code>

在 ptcheck_host 上执行检查命令：

选项的意思就不多说了。

检测完如果一致，其实是求个心安，特别是在做数据迁移的时候。如果不一致，那就需要借助 <code>pt-table-sync</code> 工具了，不作介绍。

diffs cannot be detected because no slaves were found

不能自动找到从库，确认processlist或host或dsns方式用对了。

cannot connect to h=slave1.*.com,p=...,u=percona_user

可以在<code>pt-table-checksum</code>命令前加<code>ptdebug=1</code>来看详细的执行过程，如端口、用户名、权限错误。

waiting for the --replicate table to replicate to xxx

问题出在 percona.checksums 表在从库不存在，根本原因是没有从主库同步过来，所以看一下从库是否延迟严重。

pausing because threads_running=25

反复打印出类似上面停止检查的信息。这是因为当前数据库正在运行的线程数大于默认25，pt-table-checksum 为了减少对库的压力暂停检查了。等数据库压力过了就好了，或者也可以直接 ctrl+c 终端，下一次加上<code>--resume</code>继续执行，或者加大<code>--max-load=</code>值。

字符集问题