Mysql复制数据一致性检查1

franket

发布于 2022-04-23 18:54:09

2480

发布于 2022-04-23 18:54:09

文章被收录于专栏：技术杂记

检查数据不一致

使用 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 删除。

python