[root@172-16-3-189 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
[root@172-16-3-189 we_ops_admin]# 3309.sh
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 223
Server version: 5.6.20-68.0-log Percona Server (GPL), Release 68.0, Revision 656
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from aa;
ERROR 1046 (3D000): No database selected
mysql> use ceshi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from aa;
+------+------+----+
| aa | bb | id |
+------+------+----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 5 | 2 | 5 |
+------+------+----+
3 rows in set (0.00 sec)
2）--execute执行修复语句
1 [root@172-16-3-190 we_ops_admin]# pt-table-sync --sync-to-master --replicate=ceshi.checksums -h172.16.3.190 --user=checksums --password=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 h=172.16.3.189,u=checksums,p=checksums --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309 --execute
3）验证master和slave数据不一致性是否修复，经检验数据一致
----master上表aa数据
mysql> select * from ceshi.aa;
+------+------+----+
| aa | bb | id |
+------+------+----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 5 | 2 | 5 |
+------+------+----+
3 rows in set (0.00 sec)
----slave上表aa数据
mysql> select * from ceshi.aa;
+------+------+----+
| aa | bb | id |
+------+------+----+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 5 | 2 | 5 |
+------+------+----+
3 rows in set (0.00 sec)
再次利用工具运行，检测master-slave数据一致性
1 [root@172-16-3-190 we_ops_admin]# /usr/bin/pt-table-checksum --create-replicate-table --replicate=ceshi.checksums --nocheck-replication-filters --nocheck-binlog-format --recursion-method=processlist --databases=ceshi --user=checksums --password=checksums -h172.16.3.190 --socket=/opt/app/mysql_3309/tmp/mysql.sock --port=3309
2 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
3 01-30T14:50:43 0 0 3 1 0 0.038 ceshi.aa
5、总结
1、由上面的模拟结果和pt工具差异的检测过程，我们可以得出结论pt-table-checksum 3.0.4存在bug，binlog_format格式非statement格式检测不出来差异。
2、对于数据一致性要求较高的业务，尽量从源头上避免随机函数的应用。
3、设置binlog_format的模式为row模式，能有效避免随机函数带来主从数据不一致的故障，但是这样会产生大量的binlog日志，占用磁盘空间。