pt-table-checksum


pt-table-checksum是社区开源产品。但是它不是MySQL自带的工具,需要我们自己进行安装。下面就说下安装过程

安装

版本信息:xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82)
MySQL版本:Server version: 8.0.27
os:Red Hat Enterprise Linux Server release 7.9 (Maipo)

下载安装包

下载地址:https://www.percona.com/

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

version:选择最新的
software:选择rpm,就选RHELx。如果是二进制,就选 Linux-Generic.
这里我们下载rpm包,选择rpm安装。

rpm安装

直接rpm安装,会有如下报错:

[root@test28 local]# rpm -ivh percona-toolkit-3.3.1-1.el7.x86_64.rpm 
warning: percona-toolkit-3.3.1-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:
        perl(IO::Socket::SSL) is needed by percona-toolkit-3.3.1-1.el7.x86_64

显示有包依赖关系,为了避免包依赖,我们选择yum localinstall来安装,自动安装依赖包。
配置本地yum源:

[root@test28 local]# cat /etc/yum.repos.d/base.repo 
[base]
name=base
baseurl=file:///mnt/       
gpgcheck=0
enabled=1  

挂载镜像:

[root@test28 local]# mount /dev/sr0 /mnt/

安装:

[root@test28 local]# yum localinstall percona-toolkit-3.3.1-1.el7.x86_64.rpm 
Loaded plugins: product-id, search-disabled-repos, subscription-manager

This system is not registered with an entitlement server. You can use subscription-manager to register.

Examining percona-toolkit-3.3.1-1.el7.x86_64.rpm: percona-toolkit-3.3.1-1.el7.x86_64
Marking percona-toolkit-3.3.1-1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package percona-toolkit.x86_64 0:3.3.1-1.el7 will be installed
--> Processing Dependency: perl(IO::Socket::SSL) for package: percona-toolkit-3.3.1-1.el7.x86_64
c6-media                                                                                                                                                          | 2.8 kB  00:00:00     
--> Running transaction check
---> Package perl-IO-Socket-SSL.noarch 0:1.94-7.el7 will be installed
--> Processing Dependency: perl-Net-SSLeay >= 1.55-5 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Processing Dependency: perl(Net::SSLeay) >= 1.21 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Processing Dependency: perl(IO::Socket::IP) >= 0.20 for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Processing Dependency: perl(Net::SSLeay) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Processing Dependency: perl(Net::LibIDN) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Processing Dependency: perl(Mozilla::CA) for package: perl-IO-Socket-SSL-1.94-7.el7.noarch
--> Running transaction check
---> Package perl-IO-Socket-IP.noarch 0:0.21-5.el7 will be installed
---> Package perl-Mozilla-CA.noarch 0:20130114-5.el7 will be installed
---> Package perl-Net-LibIDN.x86_64 0:0.12-15.el7 will be installed
---> Package perl-Net-SSLeay.x86_64 0:1.55-6.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=========================================================================================================================================================================================
 Package                                     Arch                            Version                                  Repository                                                    Size
=========================================================================================================================================================================================
Installing:
 percona-toolkit                             x86_64                          3.3.1-1.el7                              /percona-toolkit-3.3.1-1.el7.x86_64                           61 M
Installing for dependencies:
 perl-IO-Socket-IP                           noarch                          0.21-5.el7                               c6-media                                                      36 k
 perl-IO-Socket-SSL                          noarch                          1.94-7.el7                               c6-media                                                     114 k
 perl-Mozilla-CA                             noarch                          20130114-5.el7                           c6-media                                                      11 k
 perl-Net-LibIDN                             x86_64                          0.12-15.el7                              c6-media                                                      28 k
 perl-Net-SSLeay                             x86_64                          1.55-6.el7                               c6-media                                                     286 k

Transaction Summary
=========================================================================================================================================================================================
Install  1 Package (+5 Dependent packages)

Total size: 61 M
Total download size: 476 k
Installed size: 62 M
Is this ok [y/d/N]: y
Downloading packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                      24 MB/s | 476 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : perl-Mozilla-CA-20130114-5.el7.noarch                                                                                                                                  1/6 
  Installing : perl-Net-LibIDN-0.12-15.el7.x86_64                                                                                                                                     2/6 
  Installing : perl-Net-SSLeay-1.55-6.el7.x86_64                                                                                                                                      3/6 
  Installing : perl-IO-Socket-IP-0.21-5.el7.noarch                                                                                                                                    4/6 
  Installing : perl-IO-Socket-SSL-1.94-7.el7.noarch                                                                                                                                   5/6 
  Installing : percona-toolkit-3.3.1-1.el7.x86_64                                                                                                                                     6/6 
  Verifying  : perl-IO-Socket-IP-0.21-5.el7.noarch                                                                                                                                    1/6 
  Verifying  : perl-Net-SSLeay-1.55-6.el7.x86_64                                                                                                                                      2/6 
  Verifying  : percona-toolkit-3.3.1-1.el7.x86_64                                                                                                                                     3/6 
  Verifying  : perl-Net-LibIDN-0.12-15.el7.x86_64                                                                                                                                     4/6 
  Verifying  : perl-IO-Socket-SSL-1.94-7.el7.noarch                                                                                                                                   5/6 
  Verifying  : perl-Mozilla-CA-20130114-5.el7.noarch                                                                                                                                  6/6 

Installed:
  percona-toolkit.x86_64 0:3.3.1-1.el7                                                                                                                                                    

Dependency Installed:
  perl-IO-Socket-IP.noarch 0:0.21-5.el7        perl-IO-Socket-SSL.noarch 0:1.94-7.el7        perl-Mozilla-CA.noarch 0:20130114-5.el7        perl-Net-LibIDN.x86_64 0:0.12-15.el7       
  perl-Net-SSLeay.x86_64 0:1.55-6.el7         

Complete!

查看安装位置:
rpm -qa|grep percona
rpm -ql percona-toolkit-3.3.1-1.el7.x86_64

[root@test28 local]# rpm -qa|grep percona
percona-xtrabackup-80-8.0.26-18.1.el7.x86_64
percona-toolkit-3.3.1-1.el7.x86_64
[root@test28 local]# rpm -ql percona-toolkit-3.3.1-1.el7.x86_64
/usr/bin/pt-align
/usr/bin/pt-archiver
/usr/bin/pt-config-diff
/usr/bin/pt-deadlock-logger
/usr/bin/pt-diskstats
/usr/bin/pt-duplicate-key-checker
/usr/bin/pt-fifo-split
/usr/bin/pt-find
/usr/bin/pt-fingerprint
/usr/bin/pt-fk-error-logger
/usr/bin/pt-heartbeat
/usr/bin/pt-index-usage
/usr/bin/pt-ioprofile
/usr/bin/pt-k8s-debug-collector
/usr/bin/pt-kill
/usr/bin/pt-mext
/usr/bin/pt-mongodb-query-digest
/usr/bin/pt-mongodb-summary
/usr/bin/pt-mysql-summary
/usr/bin/pt-online-schema-change
/usr/bin/pt-pg-summary
/usr/bin/pt-pmp
/usr/bin/pt-query-digest
/usr/bin/pt-secure-collect
/usr/bin/pt-show-grants
/usr/bin/pt-sift
/usr/bin/pt-slave-delay
/usr/bin/pt-slave-find
/usr/bin/pt-slave-restart
/usr/bin/pt-stalk
/usr/bin/pt-summary
/usr/bin/pt-table-checksum
/usr/bin/pt-table-sync
/usr/bin/pt-table-usage
/usr/bin/pt-upgrade
/usr/bin/pt-variable-advisor
/usr/bin/pt-visual-explain
/usr/share/doc/percona-toolkit-3.3.1
/usr/share/doc/percona-toolkit-3.3.1/COPYING
/usr/share/doc/percona-toolkit-3.3.1/Changelog
/usr/share/doc/percona-toolkit-3.3.1/INSTALL
/usr/share/doc/percona-toolkit-3.3.1/README.md
/usr/share/man/man1/percona-toolkit.1p.gz
/usr/share/man/man1/pt-align.1p.gz
/usr/share/man/man1/pt-archiver.1p.gz
/usr/share/man/man1/pt-config-diff.1p.gz
/usr/share/man/man1/pt-deadlock-logger.1p.gz
/usr/share/man/man1/pt-diskstats.1p.gz
/usr/share/man/man1/pt-duplicate-key-checker.1p.gz
/usr/share/man/man1/pt-fifo-split.1p.gz
/usr/share/man/man1/pt-find.1p.gz
/usr/share/man/man1/pt-fingerprint.1p.gz
/usr/share/man/man1/pt-fk-error-logger.1p.gz
/usr/share/man/man1/pt-heartbeat.1p.gz
/usr/share/man/man1/pt-index-usage.1p.gz
/usr/share/man/man1/pt-ioprofile.1p.gz
/usr/share/man/man1/pt-k8s-debug-collector.1p.gz
/usr/share/man/man1/pt-kill.1p.gz
/usr/share/man/man1/pt-mext.1p.gz
/usr/share/man/man1/pt-mysql-summary.1p.gz
/usr/share/man/man1/pt-online-schema-change.1p.gz
/usr/share/man/man1/pt-pg-summary.1p.gz
/usr/share/man/man1/pt-pmp.1p.gz
/usr/share/man/man1/pt-query-digest.1p.gz
/usr/share/man/man1/pt-secure-collect.1p.gz
/usr/share/man/man1/pt-show-grants.1p.gz
/usr/share/man/man1/pt-sift.1p.gz
/usr/share/man/man1/pt-slave-delay.1p.gz
/usr/share/man/man1/pt-slave-find.1p.gz
/usr/share/man/man1/pt-slave-restart.1p.gz
/usr/share/man/man1/pt-stalk.1p.gz
/usr/share/man/man1/pt-summary.1p.gz
/usr/share/man/man1/pt-table-checksum.1p.gz
/usr/share/man/man1/pt-table-sync.1p.gz
/usr/share/man/man1/pt-table-usage.1p.gz
/usr/share/man/man1/pt-upgrade.1p.gz
/usr/share/man/man1/pt-variable-advisor.1p.gz
/usr/share/man/man1/pt-visual-explain.1p.gz
[root@test28 bin]# yum provides pt-table-sync
Loaded plugins: product-id, search-disabled-repos, subscription-manager

This system is not registered with an entitlement server. You can use subscription-manager to register.

c6-media                                                                                                                                                                        5230/5230
c6-media/filelists                                                                                                                                                 | 3.1 MB  00:00:00     
percona-toolkit-3.3.1-1.el7.x86_64 : Advanced MySQL and system command-line tools
Repo        : @/percona-toolkit-3.3.1-1.el7.x86_64
Matched from:
Filename    : /usr/bin/pt-table-sync

就在yum的默认安装位置。
pt打头的都是安装好的工具:

[root@test28 bin]# ls -ltr pt*
-rwxr-xr-x. 1 root root     3891 Jul  6  2016 ptaskset
-rwxr-xr-x. 1 root root    66720 Mar 16  2019 ptx
-rwxr-xr-x. 1 root root   437836 Apr 23  2021 pt-online-schema-change
-rwxr-xr-x. 1 root root   108113 Apr 23  2021 pt-mysql-summary
-rwxr-xr-x. 1 root root   257732 Apr 23  2021 pt-kill
-rwxr-xr-x. 1 root root   103460 Apr 23  2021 pt-visual-explain
-rwxr-xr-x. 1 root root   179330 Apr 23  2021 pt-variable-advisor
-rwxr-xr-x. 1 root root   334032 Apr 23  2021 pt-upgrade
-rwxr-xr-x. 1 root root   248658 Apr 23  2021 pt-table-usage
-rwxr-xr-x. 1 root root   406401 Apr 23  2021 pt-table-sync
-rwxr-xr-x. 1 root root   461940 Apr 23  2021 pt-table-checksum
-rwxr-xr-x. 1 root root    90817 Apr 23  2021 pt-summary
-rwxr-xr-x. 1 root root    76186 Apr 23  2021 pt-stalk
-rwxr-xr-x. 1 root root   186580 Apr 23  2021 pt-slave-restart
-rwxr-xr-x. 1 root root   133040 Apr 23  2021 pt-slave-find
-rwxr-xr-x. 1 root root   147869 Apr 23  2021 pt-slave-delay
-rwxr-xr-x. 1 root root    37784 Apr 23  2021 pt-sift
-rwxr-xr-x. 1 root root    79159 Apr 23  2021 pt-show-grants
-rwxr-xr-x. 1 root root   529350 Apr 23  2021 pt-query-digest
-rwxr-xr-x. 1 root root    24598 Apr 23  2021 pt-pmp
-rwxr-xr-x. 1 root root    21913 Apr 23  2021 pt-mext
-rwxr-xr-x. 1 root root    32405 Apr 23  2021 pt-ioprofile
-rwxr-xr-x. 1 root root   229128 Apr 23  2021 pt-index-usage
-rwxr-xr-x. 1 root root   225530 Apr 23  2021 pt-heartbeat
-rwxr-xr-x. 1 root root   135870 Apr 23  2021 pt-fk-error-logger
-rwxr-xr-x. 1 root root    67304 Apr 23  2021 pt-fingerprint
-rwxr-xr-x. 1 root root   152724 Apr 23  2021 pt-find
-rwxr-xr-x. 1 root root    50157 Apr 23  2021 pt-fifo-split
-rwxr-xr-x. 1 root root   172014 Apr 23  2021 pt-duplicate-key-checker
-rwxr-xr-x. 1 root root   166450 Apr 23  2021 pt-diskstats
-rwxr-xr-x. 1 root root   168891 Apr 23  2021 pt-deadlock-logger
-rwxr-xr-x. 1 root root   171696 Apr 23  2021 pt-config-diff
-rwxr-xr-x. 1 root root   272712 Apr 23  2021 pt-archiver
-rwxr-xr-x. 1 root root    41747 Apr 23  2021 pt-align
-rwxr-xr-x. 1 root root  4795128 Apr 23  2021 pt-secure-collect
-rwxr-xr-x. 1 root root  6728024 Apr 23  2021 pt-pg-summary
-rwxr-xr-x. 1 root root 12591128 Apr 23  2021 pt-mongodb-summary
-rwxr-xr-x. 1 root root 12212984 Apr 23  2021 pt-mongodb-query-digest
-rwxr-xr-x. 1 root root 13534872 Apr 23  2021 pt-k8s-debug-collector

数据校验

初试

创建连接用户:

root@localhost 11:57:  [(none)]> create user pt identified by 'pt';
Query OK, 0 rows affected (0.02 sec)

root@localhost 11:58:  [(none)]>  grant all privileges on *.* to pt;
Query OK, 0 rows affected (0.01 sec)

连接测试

[root@test28 logs]# pt-table-checksum --recursion-method="processlist" \
> --replicate="zhuo.checksums" \
> --host='192.168.33.28' \
> --port=3306 \
> --user='pt' \
> --password='pt' \
> --databases=sbtest \
> --no-check-binlog-format
01-20T12:00:59 DBI connect(';host=192.168.33.28;port=3306;mysql_read_default_group=client','pt',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/bin/pt-table-checksum line 1623.

报错:Authentication plugin ‘caching_sha2_password’ cannot be loaded 密码验证方式不兼容。
前面也经常遇到,修改此用户的密码验证方式为:mysql_native_password 即可。
修改如下:

root@localhost 12:04:  [(none)]> ALTER USER 'pt'@'%' IDENTIFIED WITH mysql_native_password BY 'pt';
Query OK, 0 rows affected (0.00 sec)

root@localhost 12:06:  [(none)]> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| pt               | %         | mysql_native_password |
| repl             | %         | caching_sha2_password |
| wahaha           | %         | caching_sha2_password |
| zhuo             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
9 rows in set (0.00 sec)

也可以以后直接创建用户的时候,直接修改密码验证方式,如下:

root@localhost 12:06:  [(none)]> create user test identified WITH mysql_native_password by 'test' ;
Query OK, 0 rows affected (0.01 sec)

root@localhost 12:06:  [(none)]> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| pt               | %         | mysql_native_password |
| repl             | %         | caching_sha2_password |
| test             | %         | mysql_native_password |
| wahaha           | %         | caching_sha2_password |
| zhuo             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
9 rows in set (0.00 sec)
[root@test28 logs]# pt-table-checksum --recursion-method="processlist" --replicate="zhuo.checksums" --host='192.168.33.28' --port=3306 --user='pt' --password='pt' --databases=sbtest --n
o-check-binlog-format
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
01-20T12:07:15      0      0  1098156          0      15       0   5.856 sbtest.sbtest1
01-20T12:07:20      0      0  1099426          0       1       0   5.216 sbtest.sbtest10
01-20T12:07:26      0      0  1100876          0       1       0   5.454 sbtest.sbtest2
01-20T12:07:31      0      0  1099857          0       1       0   4.912 sbtest.sbtest3
01-20T12:07:36      0      0  1099689          0       1       0   5.210 sbtest.sbtest4
01-20T12:07:41      0      0  1100036          0       1       0   5.080 sbtest.sbtest5
01-20T12:07:47      0      0  1098960          0       1       0   5.517 sbtest.sbtest6
01-20T12:07:52      0      0  1098998          0       1       0   5.452 sbtest.sbtest7
01-20T12:07:57      0      0  1099965          0       1       0   5.001 sbtest.sbtest8
01-20T12:08:02      0      0  1098637          0       1       0   5.037 sbtest.sbtest9

报错:
Diffs cannot be detected because no slaves were found.
主库找不到从库。让添加参数:–recursion-method
通过
[root@test28 ~]# pt-table-checksum --help
参数如下解释:
–recursion-method=a Preferred recursion method for discovering
replicas. pt-table-checksum performs several
REPLICA CHECKS before and while running (
default processlist,hosts)
默认是processlist和hosts在主库查询slave的信息。

[root@test28 ~]# pt-table-checksum --replicate="zhuo.checksums" --host='192.168.33.28' --port=3306 --user='pt' --password='pt' --databases=sbtest --no-check-binlog-format               
Checking if all tables can be checksummed ...
Starting checksum ...
Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
01-21T00:33:31      0      0    10000          0       4       0   0.110 sbtest.sbtest1
01-21T00:33:31      0      0    10000          0       1       0   0.067 sbtest.sbtest10
01-21T00:33:32      0      0    10000          0       1       0   0.067 sbtest.sbtest2
01-21T00:33:32      0      0    10000          0       1       0   0.066 sbtest.sbtest3
01-21T00:33:32      0      0    10000          0       1       0   0.070 sbtest.sbtest4
01-21T00:33:32      0      0    10000          0       1       0   0.067 sbtest.sbtest5
01-21T00:33:32      0      0    10000          0       1       0   0.067 sbtest.sbtest6
01-21T00:33:32      0      0    10000          0       1       0   0.067 sbtest.sbtest7
01-21T00:33:32      0      0    10000          0       1       0   0.067 sbtest.sbtest8
01-21T00:33:32      0      0    10000          0       1       0   0.068 sbtest.sbtest9

还是报相同错误,我们手动在主库查询,看能否查询到从库的信息。
主库查询:

root@localhost 00:28:  [(none)]> show processlist;
+-----+-----------------+--------------+------+-------------+--------+-----------------------------------------------------------------+------------------+
| Id  | User            | Host         | db   | Command     | Time   | State                                                           | Info             |
+-----+-----------------+--------------+------+-------------+--------+-----------------------------------------------------------------+------------------+
|   5 | event_scheduler | localhost    | NULL | Daemon      | 646933 | Waiting on empty queue                                          | NULL             |
| 922 | repl            | test28:38170 | NULL | Binlog Dump |   2010 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 925 | root            | localhost    | NULL | Query       |      0 | init                                                            | show processlist |
+-----+-----------------+--------------+------+-------------+--------+-----------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
root@localhost 00:28:  [(none)]> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
|    283310 | 192.168.33.28 | 3310 |      3306 | 6bbd5c37-761e-11ec-b67a-0050569e5ca3 |
+-----------+---------------+------+-----------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

是可以查询到的。
网上有些说的,当在主库show查询不到从库的信息的时候,需要在从库的my.cnf里面添加从库自己的信息,即:
report_host = MASTER_HOST
report_port = 13306
具体为:
[mysqld]
report-host=192.168.33.28
report-port =3306
重启从库。亲测,无效。
他们这种都是针对主库show查询不到从库信息的方案,我们此处是能查询到的,所以不适用。

看到如下原因:
pt-table-checksum 默认可以在主库的 processlist 中找到从库复制进程,从而识别出有哪些从库,但如果使用是非标准3306端口,会导致找不到从库信息。意思show processlist只能检测使用3306默认端口的从库。所以此处我们检测不到从库。

看到一种万能方法:
最终极的办法是dsn,dsn指定的是某个表(如 percona.dsns ),表行记录是改主库的(多个)从库的连接信息。适用以下任一情形:
主库不能自动发现从库
不想在从库添加额外配置(因为要重启)
主从检测连接用户信息不一样
多个从库时只想验证指定从库的一致
我比较倾向使用DSN的方式。这个dsns表只需要在执行 pt-table-checksum 命令的服务器上能够访问到就行。

dsn

默认情况下,pt-table-checksum使用的从库连接参数,是从主库的连接参数继承而来的,包括用户名(–user)、密码(–password)、端口(–port)等。 在本次校验中,没有显式指定从库的用户名(–slave-user),密码(–slave-password),及端口。 所以,在没有显式指定从库的连接参数的情况下,默认是,主库和从库的用户名、密码、端口是相同的;但是在我的测试环境中,主从环境的端口是不同的。所以导致pt-table-checksum虽然搜索到了从库,但是无法连接从库(Cannot connect to h=192.168.0.18,p=…,u=pt_table_check),紧接着报没有发现从库的错误。 由于pt-table-checksum没有参数可以用来指定从库的端口,所以使用dsn方式来指定从库的连接信息(–recursion-method dsn=D=percona,t=dsns,h=host,P=3309,u=username,p=passwd)。

dsn方法:dsn是参数–recursion-method的一个参数值。即DATA SOURCE NAME,数据源名称。DSN包含从库的各个连接参数(user、password、port等),由逗号分隔的多个option=value字符串组成。 如:h=host,P=3309,u=username,p=passwd。 dsn方法是指将从库的DSN信息存储在表(DSN表)里,然后将该表和DSN信息赋值给dsn,作为–recursion-method的参数值。 格式如:–recursion-method dsn=D=percona,t=dsns,h=host,P=3309,u=username,p=passwd。

当指定pt-table-checksum的–recursion-method参数值为dsn时,它只会连接和检测这些指定的从库。当从库的MySQL用户名、密码、端口与主库不相同时,也可以通过dsn的方式来指定。

主库创建表:
CREATE TABLE zhuo.dsns (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);

master 23:36:59 [zhuo]>CREATE TABLE zhuo.dsns (
    -> id int(11) NOT NULL AUTO_INCREMENT,
    -> parent_id int(11) DEFAULT NULL,
    -> dsn varchar(255) NOT NULL,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.04 sec)

插入从库信息:

master 23:47:31 [zhuo]>INSERT INTO zhuo.dsns(dsn) VALUES ("h=192.168.33.28,P=3310,u=pt,p=pt");
Query OK, 1 row affected (0.01 sec)
root@localhost 00:52:  [(none)]> select * from zhuo.dsns;
+----+-----------+----------------------------------+
| id | parent_id | dsn                              |
+----+-----------+----------------------------------+
|  1 |      NULL | h=192.168.33.28,P=3310,u=pt,p=pt |
+----+-----------+----------------------------------+
1 row in set (0.00 sec)

进行校验:
将dsns表和DSN信息赋值给dsn,作为–recursion-method的参数值。 格式如:–recursion-method dsn=t=zhuo.dsns,h=host,P=3310,u=username,p=passwd。
完整的命令如下:
pt-table-checksum --replicate=“zhuo.checksums”
–host=‘192.168.33.28’
–port=3306
–user=‘pt’
–password=‘pt’
–databases=sbtest
–no-check-binlog-format
–recursion-method dsn=t=zhuo.dsns,h=192.168.33.28,P=3310,u=pt,p=pt

[root@test28 ~]# pt-table-checksum --replicate="zhuo.checksums" \
> --host='192.168.33.28' \
> --port=3306 \
> --user='pt' \
> --password='pt' \
> --databases=sbtest \
> --no-check-binlog-format \
> --recursion-method dsn=t=zhuo.dsns,h=192.168.33.28,P=3310,u=pt,p=pt
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
01-21T00:54:40      0      1    10000          1       4       0   0.507 sbtest.sbtest1
01-21T00:54:41      0      0    10000          0       1       0   0.434 sbtest.sbtest10
01-21T00:54:41      0      0    10000          0       1       0   0.433 sbtest.sbtest2
01-21T00:54:42      0      0    10000          0       1       0   0.438 sbtest.sbtest3
01-21T00:54:42      0      0    10000          0       1       0   0.436 sbtest.sbtest4
01-21T00:54:43      0      0    10000          0       1       0   0.439 sbtest.sbtest5
01-21T00:54:43      0      0    10000          0       1       0   0.437 sbtest.sbtest6
01-21T00:54:43      0      0    10000          0       1       0   0.440 sbtest.sbtest7
01-21T00:54:44      0      0    10000          0       1       0   0.436 sbtest.sbtest8
01-21T00:54:44      0      1    10000          1       1       0   0.433 sbtest.sbtest9

解释

解释:
TS :完成检查的时间。
ERRORS :检查时候发生错误和警告的数量。
DIFFS :0表示一致,1表示不一致。当指定–no-replicate-check时,会一直为0,当指定–replicate-check-only会显示不同的信息。
ROWS :表的行数。
CHUNKS :被划分到表中的块的数目。
SKIPPED :由于错误或警告或过大,则跳过块的数目。
TIME :执行的时间。
TABLE :被检查的表名。
DIFF_ROWS:
重点关注DIFFS和DIFF_ROWS.

参考:
https://www.cnblogs.com/xuanzhi201111/p/4180638.html
https://blog.51cto.com/moerjinrong/2352317
https://my.oschina.net/u/4397864/blog/4046102
https://www.cnblogs.com/kevingrace/p/6261091.html

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值