使用 pt-table-checksum 进行不一致数据检查
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the master host. The tool’s “EXIT STATUS” is non-zero if any differences are found, or if any warnings or errors occur. This tool is focused on finding data differences efficiently. If any data is different, you can resolve the problem with pt-table-sync.
检查示例
[mysql@replication-check-vm ~]$ pt-table-checksum --nocheck-replication-filters --nocheck-binlog-format --replicate=test.checksum --tables abc_test_db.users h=replication-check-vm,u=root --ask-pass
Enter MySQL password:
Checksumming abc_test_db.users: 9% 05:00 remain
Checksumming abc_test_db.users: 18% 04:42 remain
Checksumming abc_test_db.users: 29% 03:48 remain
Checksumming abc_test_db.users: 37% 03:26 remain
Checksumming abc_test_db.users: 45% 03:03 remain
Checksumming abc_test_db.users: 51% 02:57 remain
Checksumming abc_test_db.users: 56% 02:51 remain
Checksumming abc_test_db.users: 61% 02:36 remain
Checksumming abc_test_db.users: 65% 02:30 remain
Checksumming abc_test_db.users: 69% 02:18 remain
Checksumming abc_test_db.users: 74% 01:59 remain
Checksumming abc_test_db.users: 80% 01:34 remain
Checksumming abc_test_db.users: 85% 01:11 remain
Checksumming abc_test_db.users: 90% 00:47 remain
Checksumming abc_test_db.users: 94% 00:25 remain
Checksumming abc_test_db.users: 99% 00:01 remain
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-19T22:02:30 0 103 14879748 154 0 594.225 abc_test_db.users
[mysql@replication-check-vm ~]$
Option | Comment |
---|---|
--nocheck-replication-filters | 如果有复制过滤就不进行检查 |
--nocheck-binlog-format | 不对binlog_format进行检查 |
--replicate | 将结果存入指定的表中 |
--tables | 指定要检查的表,可以是一个列表,使用逗号分割 |
--databases | 指定要检查的库,可以是一个列表,使用逗号分割 |
h=xxx,u=xxx | DSN选项,h代表host,u代表用户名,使用逗号分割 |
--ask-pass | 使用提示密码的方式连接数据库,而不是使用DSN指定,更安全 |
Note: 此时的DSN要指定master,并且首先要解决对于数据库的读写权限问题 Tip: 可以一次指定多个表进行检查,中间使用逗号分隔
[mysql@replication-check-vm ~]$ pt-table-checksum --nocheck-replication-filters --nocheck-binlog-format --replicate=test.checksum --tables user_key_db.user_schema,user_key_db.log_records h=replication-check-vm,u=root --ask-pass
Enter MySQL password:
Checksumming user_key_db.log_records: 3% 14:14 remain
Checksumming user_key_db.log_records: 7% 13:18 remain
Checksumming user_key_db.log_records: 10% 13:07 remain
Checksumming user_key_db.log_records: 13% 12:51 remain
Checksumming user_key_db.log_records: 17% 12:18 remain
Checksumming user_key_db.log_records: 20% 11:53 remain
Checksumming user_key_db.log_records: 24% 11:13 remain
Checksumming user_key_db.log_records: 27% 10:30 remain
Checksumming user_key_db.log_records: 31% 09:53 remain
Checksumming user_key_db.log_records: 35% 09:21 remain
Checksumming user_key_db.log_records: 38% 08:49 remain
Checksumming user_key_db.log_records: 42% 08:16 remain
Checksumming user_key_db.log_records: 45% 07:48 remain
Checksumming user_key_db.log_records: 48% 07:29 remain
Checksumming user_key_db.log_records: 51% 07:11 remain
Checksumming user_key_db.log_records: 54% 06:45 remain
Checksumming user_key_db.log_records: 57% 06:16 remain
Checksumming user_key_db.log_records: 60% 05:55 remain
Checksumming user_key_db.log_records: 63% 05:36 remain
Checksumming user_key_db.log_records: 66% 05:13 remain
Checksumming user_key_db.log_records: 68% 04:50 remain
Checksumming user_key_db.log_records: 71% 04:22 remain
Checksumming user_key_db.log_records: 74% 03:55 remain
Checksumming user_key_db.log_records: 78% 03:23 remain
Checksumming user_key_db.log_records: 81% 02:53 remain
Checksumming user_key_db.log_records: 84% 02:25 remain
Checksumming user_key_db.log_records: 87% 01:55 remain
Checksumming user_key_db.log_records: 90% 01:26 remain
Checksumming user_key_db.log_records: 93% 00:58 remain
Checksumming user_key_db.log_records: 97% 00:26 remain
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-19T23:01:39 0 20 57822684 448 0 943.371 user_key_db.log_records
Checksumming user_key_db.user_schema: 7% 06:04 remain
Checksumming user_key_db.user_schema: 14% 06:20 remain
Checksumming user_key_db.user_schema: 18% 06:50 remain
Checksumming user_key_db.user_schema: 24% 06:21 remain
Checksumming user_key_db.user_schema: 30% 05:58 remain
Checksumming user_key_db.user_schema: 35% 05:30 remain
Checksumming user_key_db.user_schema: 42% 04:55 remain
Checksumming user_key_db.user_schema: 46% 04:37 remain
Checksumming user_key_db.user_schema: 52% 04:12 remain
Checksumming user_key_db.user_schema: 57% 03:45 remain
Checksumming user_key_db.user_schema: 62% 03:22 remain
Checksumming user_key_db.user_schema: 66% 03:06 remain
Checksumming user_key_db.user_schema: 70% 02:44 remain
Checksumming user_key_db.user_schema: 74% 02:28 remain
Checksumming user_key_db.user_schema: 77% 02:12 remain
Checksumming user_key_db.user_schema: 80% 01:57 remain
Checksumming user_key_db.user_schema: 83% 01:43 remain
Checksumming user_key_db.user_schema: 86% 01:24 remain
Checksumming user_key_db.user_schema: 90% 00:58 remain
Checksumming user_key_db.user_schema: 96% 00:22 remain
11-19T23:12:05 0 133 13877382 212 0 626.470 user_key_db.user_schema
[mysql@replication-check-vm ~]$
本文系转载,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文系转载,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。