好久没更新了，今儿来个~
在mysql工作中接触最多的就是mysql replication，mysql在复制方面还是会有一些常规问题，比如主库宕机或者从库宕机有可能会导致复制中断，通常需要进行人为修复，或者很多时候需要把一个从库提升为主库，但对从库和主库的数据一致性不能保证一样。这种情况下就需要使用percona-toolkit工具的pt-table-checksum组件来检查主从数据的一致性；如果发现不一致的数据，可以通过pt-table-sync修复；还可以通过pt-heartbeat监控主从复制延迟。
1. 工具介绍
percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，这些任务包括：
1）检查master和slave数据的一致性
2）有效地对记录进行归档
3）查找重复的索引
4）对服务器信息进行汇总
5）分析来自日志和tcpdump的查询
6）当系统出问题的时候收集重要的系统信息
percona-toolkit源自Maatkit和Aspersa工具，这两个工具是管理mysql的最有名的工具。不过，现在Maatkit工具已经不维护了，所以以后推荐还是使用percona-toolkit工具！
这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类，作为一个优秀的DBA，里面有的工具非常有用，如果能掌握并加以灵活应用，将能极大的提高工作效率。
percona-toolkit工具中最主要的三个组件分别是：
1）pt-table-checksum 负责监测mysql主从数据一致性
2）pt-table-sync 负责当主从数据不一致时修复数据，让它们保存数据的一致性
3）pt-heartbeat 负责监控mysql主从同步延迟
下面就对这三个组件的使用做一记录。
2. 工具安装
软件下载并在主库服务器上安装：percona-toolkit-3.0.10-1.el7.x86_64.rpm
由于软件包有依赖关系，建议通过yum安装：
[root@master-server src]# yum install -y percona-toolkit-3.0.10-1.el7.x86_64.rpm
安装后，percona-toolkit工具的各个组件命令就有了（输入pt-，按TAB键就会显示）。
3. pt-table-checksum用法
pt-table-checksum 是 Percona-Toolkit的组件之一，用于检测MySQL主、从库的数据是否一致。其原理是在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以块为单位计算，可以避免锁表。检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响。
3.1使用方法
pt-table-checksum 默认情况下可以应对绝大部分场景，官方说，即使上千个库、上万亿的行，它依然可以很好的工作，这源自于设计很简单，一次检查一个表，不需要太多的内存和多余的操作；必要时，pt-table-checksum 会根据服务器负载动态改变 chunk 大小，减少从库的延迟。
为了保证主数据库服务的安全，该工具实现了许多保护措施：
1）自动设置 innodb_lock_wait_timeout 为1s。
2）默认当数据库有25个以上的并发查询时，pt-table-checksum会暂停。可以设置 --max-load 选项来设置这个阀值。
3）当用 Ctrl+C 停止任务后，工具会正常的完成当前 chunk 检测，下次使用 --resume 选项启动可以恢复继续下一个 chunk。
pt-table-checksum [OPTIONS] [DSN]
pt-table-checksum：在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误。注意：第一次运行的时候需要加上--create-replicate-table参数，生成checksums表，如果不加这个参数，那么就需要在对应库下手工添加这张表了,表结构SQL如下：
CREATE TABLE checksums ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB; |
常用参数解释
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。 --no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。 --replicate-check-only :只显示不同步的信息。 --replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 --databases= ：指定需要被检查的数据库，多个则用逗号隔开。 --tables= ：指定需要被检查的表，多个用逗号隔开 --function=s ： Hash function for checksums (FNV1A_64, MURMUR_HASH, SHA1, MD5, CRC32, etc) h= ：Master的地址 u= ：用户名 p=：密码 P= ：端口
|
最重要的一点就是：要在主库上授权，能让主库ip访问。这一点不能忘记！（实验证明从库上可以不授权，但最好还是从库也授权）
注意：
1）根据测试，需要一个即能登录主库，也能登录从库的账号；
2）只能指定一个host，必须为主库的IP；
3）在检查时会向表加S锁；
4）运行之前需要从库的同步IO和SQL进程是YES状态。
3.2测试参考
（本文例子中：192.168.1.80是主库ip，192.168.1.81是从库ip）
在主库执行授权（一定要对主库ip授权，授权的用户名和密码可以自行定义，不过要保证这个权限能同时登陆主库和从库）
mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'check'@'192.168.1.80' identified by '123456'; mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'check'@'192.168.1.81' identified by '123456'; mysql> flush privileges;
|
如下，在主库上执行的一个检查主从数据一致性的命令（别忘了第一次运行的时候需要添加--create-replicate-table参数，后续再运行时就不需要加了）：下面命令中的192.168.1.80是主库ip，检查的是test库下的goods表的数据（当然，命令中也可以不跟表，直接检查某整个库的数据；如下去掉--tables=goods表，直接检查test库的数据）。
执行检查命令：
[mysql@localhost ~]$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --function=MD5 --replicate=test.checksums --databases=test h=192.168.1.80,u=check,p=123456,P=3306
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 07-02T14:59:33 0 0 1000000 0 21 0 9.611 test.goods
|
结果参数解释
TS ：完成检查的时间。 ERRORS ：检查时候发生错误和警告的数量。 DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。 ROWS ：表的行数。 CHUNKS ：被划分到表中的块的数目。 SKIPPED ：由于错误或警告或过大，则跳过块的数目。 TIME ：执行的时间。 TABLE ：被检查的表名。
|
4. pt-table-sync用法
如果通过pt-table-checksum 检查找到了不一致的数据表，那么如何同步数据呢？即如何修复MySQL主从不一致的数据，让他们保持一致性呢？这时候可以利用另外一个工具pt-table-sync。
4.1使用方法
pt-table-sync: 高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。
假如上面检查数据时发现主从不一致：
[mysql@localhost ~]$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --function=MD5 --replicate=test.checksums --databases=test tables=goods h=192.168.1.80,u=check,p=123456,P=3306
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 07-02T15:12:41 0 1 1000000 0 20 0 9.694 test.goods
|
现在DIFFS为1可知主从数据不一致，需要修复！通过—print打印出需要修复的语句，命令如下：
[mysql@localhost ~]$ pt-table-sync --replicate=test.checksums --function=MD5 h=192.168.1.80,u=check,p=123456 h=192.168.1.81,u=check,p=123456 --print
REPLACE INTO `test`.`goods`(`id`, `name1`, `name2`, `name3`, `in_time`) VALUES ('100', 'ceshishuju-name1-100', 'ceshishuju-name2-100', 'ceshishuju-name3-100', '2018-07-02 11:16:38') *percona-toolkit src_db:test src_tbl:goods src_dsn:h=192.168.1.80,p=...,u=check dst_db:test dst_tbl:goods dst_dsn:h=192.168.1.81,p=...,u=check lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:26185 user:mysql host:localhost.localdomain*/; |
通过--execute参数进行修复，命令如下：
[mysql@localhost ~]$ pt-table-sync --replicate=test.checksums --function=MD5 h=192.168.1.80,u=check,p=123456 h=192.168.1.81,u=check,p=123456 --execute 如上修复后，再次检查，发现主从库数据已经一致了！ [mysql@localhost ~]$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --function=MD5 --replicate=test.checksums --databases=test h=192.168.1.80,u=check,p=123456,P=3306 TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 07-02T15:41:47 0 0 1000000 0 20 0 9.433 test.goods 07-02T15:41:57 0 0 1000000 0 19 0 9.752 test.goods_01 07-02T15:41:57 0 0 1 0 1 0 0.015 test.t1
|
参数解释：
--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。 --databases= : 指定执行同步的数据库。 --tables= ：指定执行同步的表，多个用逗号隔开。 --sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。 --function=s ： Hash function for checksums (FNV1A_64, MURMUR_HASH, SHA1, MD5, CRC32, etc) h= ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。 u= ：帐号。 p= ：密码。 --print ：打印，但不执行命令。 --execute ：执行命令。
|
建议:
修复数据的时候，最好还是用--print打印出来的好，这样就可以知道那些数据有问题，可以人为的干预下。不然直接执行了，出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。
5. pt-heartbeat用法
对于MySQL数据库主从复制延迟的监控，可以借助percona的有力武器pt-heartbeat来实现。pt-heartbeat的工作原理通过使用时间戳方式在主库上更新特定表，然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟。具体流程：
1）在主上创建一张heartbeat表，按照一定的时间频率更新该表的字段（把时间更新进去）。监控操作运行后，heartbeat表能促使主从同步。
2）连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。
5.1使用方法
（主从和从库上都可以执行监控操作）：
pt-heartbeat [OPTIONS] [DSN] --update|--monitor|--check|--stop 注意：需要指定的参数至少有 --stop，--update，--monitor，--check。 其中--update，--monitor和--check是互斥的，--daemonize和--check也是互斥。 --ask-pass 隐式输入MySQL密码 --charset 字符集设置 --check 检查从的延迟，检查一次就退出，除非指定了--recurse会递归的检查所有的从服务器。 --check-read-only 如果从服务器开启了只读模式，该工具会跳过任何插入。 --create-table 在主上创建心跳监控的表，如果该表不存在，可以自己手动建立，建议存储引擎改成memory。通过更新该表知道主从延迟的差距。 --daemonize 执行时，放入到后台执行 --user=-u， 连接数据库的帐号 --database=-D， 连接数据库的名称 --host=-h， 连接的数据库地址 --password=-p， 连接数据库的密码 --port=-P， 连接数据库的端口 --socket=-S， 连接数据库的套接字文件 --file [--file=output.txt]打印--monitor最新的记录到指定的文件，很好的防止满屏幕都是数据的烦恼。 --frames [--frames=1m,2m,3m]在--monitor里输出的[]里的记录段，默认是1m,5m,15m。可以指定1个，如：--frames=1s，多个用逗号隔开。可用单位有秒（s）、分钟（m）、小时（h）、天（d）。 --interval 检查、更新的间隔时间。默认是见是1s。最小的单位是0.01s，最大精度为小数点后两位，因此0.015将调整至0.02。 --log 开启daemonized模式的所有日志将会被打印到制定的文件中。 --monitor 持续监控从的延迟情况。通过--interval指定的间隔时间，打印出从的延迟信息，通过--file则可以把这些信息打印到指定的文件。 --master-server-id指定主的server_id，若没有指定则该工具会连到主上查找其server_id。 --print-master-server-id在--monitor和--check 模式下，指定该参数则打印出主的server_id。 --recurse 多级复制的检查深度。模式M-S-S...不是最后的一个从都需要开启log_slave_updates，这样才能检查到。 --recursion-method 指定复制检查的方式,默认为processlist,hosts。 --update 更新主上的心跳表。 --replace 使用--replace代替--update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行。 --stop 停止运行该工具（--daemonize），在/tmp/目录下创建一个“pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize）。 --table 指定心跳表名，默认heartbeat。
|
5.2测试参考
master：192.168.1.80
slave：192.168.1.81
同步的库：test
主从库都能使用check账号、密码123456登录
先操作针对test库的检查，其他同步的库的检查操作类似！
更新主库上的heartbeat,--interval=1表示1秒钟更新一次（注意这个启动操作要在主库服务器上执行）。
[root@master-server ~]# pt-heartbeat --user=check --ask-pass --host=192.168.1.80 --create-table -D test --interval=1 --update --replace --daemonize Enter password:
|
在主库运行监测同步延迟：
[mysql@localhost ~]$ pt-heartbeat -D test --table=heartbeat --monitor --host=192.168.1.80 --user=check --password=123456 --master-server-id=240 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] 0.00s [ 0.00s, 0.00s, 0.00s ] .........
|
解释：0表示从没有延迟。[ 0.00s, 0.00s, 0.00s ]表示1分钟，5分钟，15分钟内的平均值。可以通过--frames设置。也可以将主库的server-id打印出来（--print-master-server-id）。
上面的监测命令会一直在运行状态中，可以使用--check监测一次就退出，使用了--check，就不能使用—monit。
关闭heartbeat更新进程：直接kill掉进程pid
[root@master-server ~]# ps -ef|grep heartbeat root 15152 1 0 19:49 ? 00:00:02 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.80 --create-table -D test --interval=1 --update --replace --daemonize root 15310 1 0 19:59 ? 00:00:01 perl /usr/bin/pt-heartbeat -D test --table=heartbeat --monitor --host=192.168.1.80 --user=check --password=123456 --log=/opt/master-slave.txt --daemonize root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat [root@master-server ~]# kill -9 15152 [root@master-server ~]# ps -ef|grep heartbeat root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat
|
附录：
1.运行pt-table-checksum时报错如下：
[mysql@localhost ~]$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --function=MD5 --replicate=test.checksums --databases=test h=192.168.1.80,u=check,p=123456,P=3306
报错：
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/pt-table-checksum line 787.
解决：
缺少必要的运行包，通过yum安装即可：yum install -y perl-Digest-MD5
2. 运行pt-table-checksum时报错如下：
报错：
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information
解决：
上面的提示信息很清楚，因为找不到从库，所以执行失败，提示用参数--recursion-method 可以指定模式解决。其实是因为从库关闭了，打开从库问题解决。
3.报错信息如下：
报错
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.
解决：
表中没有唯一索引或则主键，创建相应索引。
