通过yum安装percona-toolkit
[root@node01 ~]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@node01 ~]# rpm -ivh percona-release-latest.noarch.rpm
[root@node01 ~]# yum install -y percona-toolkit
[mysql@node01 ~]$ pt-table-checksum --version
pt-table-checksum 3.5.4
从库只读,从库与主库的数据保持一致
[mysql@node01 ~]$ pt-table-checksum --user=root --ask-pass --host=192.168.1.101 --port=3306 --databases=tpcc10 \
--replicate=tpcc10.checksums --nocheck-replication-filters --nocheck-binlog-format --noversion-check
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
08-04T12:37:34 0 0 300000 0 38 0 19.408 tpcc10.customer
08-04T12:37:35 0 0 100 0 1 0 0.397 tpcc10.district
08-04T12:37:46 0 0 300000 0 20 0 11.456 tpcc10.history
08-04T12:37:50 0 0 100000 0 8 0 3.560 tpcc10.item
08-04T12:37:51 0 0 90000 0 5 0 1.661 tpcc10.new_orders
08-04T12:39:02 0 0 3000331 0 115 0 71.013 tpcc10.order_line
08-04T12:39:10 0 0 300000 0 14 0 7.468 tpcc10.orders
08-04T12:39:40 0 0 1000000 0 56 0 30.119 tpcc10.stock
08-04T12:39:40 0 0 10 0 1 0 0.365 tpcc10.warehouse
在从库执行DML事务模拟与主库数据不一致
[mysql@node02 ~]$ mysql -uroot -pabcd.1234 -e 'set global super_read_only=0'
[mysql@node02 ~]$ tpcc-mysql/tpcc_start -h node02 -P 3306 -d tpcc10 -u tpcc -p tpcc -w 10 -c 10 -l 60
校验从库与主库的数据是否一致,因为在从库执行事务导致与主库多个表数据不一致
[mysql@node01 ~]$ pt-table-checksum --user=root --ask-pass --host=192.168.1.101 --port=3306 --databases=tpcc10 \
--replicate=tpcc10.checksums --nocheck-replication-filters --nocheck-binlog-format --noversion-check
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
08-04T12:43:27 0 28 300000 0 30 0 17.378 tpcc10.customer
08-04T12:43:27 0 1 100 0 1 0 0.418 tpcc10.district
08-04T12:43:36 0 1 300000 479 17 0 9.015 tpcc10.history
08-04T12:43:42 0 0 100000 0 6 0 5.469 tpcc10.item
08-04T12:43:45 0 4 90000 5 5 0 3.456 tpcc10.new_orders
08-04T12:44:50 0 105 3000331 53 109 0 64.864 tpcc10.order_line
08-04T12:44:59 0 13 300000 5 14 0 8.469 tpcc10.orders
08-04T12:45:40 0 67 1000000 0 69 0 41.395 tpcc10.stock
08-04T12:45:41 0 1 10 0 1 0 0.442 tpcc10.warehouse
通过pt-table-sync同步从库与主库数据不一致的表
[root@node01 ~]# pt-table-sync --version
pt-table-sync 3.5.2
打印同步从库与主库数据不一致的表的sql语句
[mysql@node01 ~]$ pt-table-sync --print --replicate=tpcc10.checksums \
h=192.168.1.101,u=root,p=abcd.1234 h=192.168.1.102,u=root,p=abcd.1234 >diffs.sql
执行同步从库与主库数据不一致的表的sql语句
[mysql@node01 ~]$ pt-table-sync --execute --replicate=tpcc10.checksums \
h=192.168.1.101,u=root,p=abcd.1234 h=192.168.1.102,u=root,p=abcd.1234 --noforeign-key-checks
再次校验从库与主库的表数据是否一致,全部表数据一致
[mysql@node01 ~]$ pt-table-checksum --user=root --ask-pass --host=192.168.1.101 --port=3306 --databases=tpcc10 \
--replicate=tpcc10.checksums --nocheck-replication-filters --nocheck-binlog-format --noversion-check
Enter MySQL password:
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
08-04T13:32:08 0 0 300000 0 26 0 11.439 tpcc10.customer
08-04T13:32:08 0 0 100 0 1 0 0.415 tpcc10.district
08-04T13:32:15 0 0 300000 0 15 0 7.024 tpcc10.history
08-04T13:32:18 0 0 100000 0 8 0 3.136 tpcc10.item
08-04T13:32:20 0 0 90000 0 5 0 1.650 tpcc10.new_orders
08-04T13:33:12 0 0 3000331 0 85 0 52.574 tpcc10.order_line
08-04T13:33:20 0 0 300000 0 14 0 7.445 tpcc10.orders
08-04T13:33:52 0 0 1000000 0 50 0 31.991 tpcc10.stock
08-04T13:33:52 0 0 10 0 1 0 0.427 tpcc10.warehouse
标签:主库,8.0,pt,--,08,tpcc10,table,04T12
From: https://blog.51cto.com/dbprofessional/7244150