percona-toolkit工具包的安装使用《mysql 》

原创

liuyunshengsir 2023-01-10 00:06:02 博主文章分类：mysql ©著作权

文章标签 mysql 数据库服务器 SSL ci 文章分类 OpenStack 云计算

©著作权归作者所有：来自51CTO博客作者liuyunshengsir的原创作品，请联系作者获取转载授权，否则将追究法律责任

安装percona-toolkit工具包

yum install percona-toolkit

安装后，可以通过下面的命令确认是否安装成功：

如果命令提示可以正常显示，则说明pt工具已经正常安装和使用了。

pt-query-digest --help

pt-table-checksum --help

有的32个命令，可以分为7大类

percona-toolkit工具包的安装使用《mysql 》_mysql

percona-toolkit工具包的安装使用《mysql 》_数据库_02

percona-toolkit工具包的安装使用《mysql 》_服务器_03

实践

比较主从数据库不一致的地方

[root@781c38071bbd ~]#  pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums   --create-replicate-table --databases=db01 h=172.17.0.3,u=user0001,p='User0001!',P=3306
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
06-22T03:24:41      0      0        4          0       1       0   0.162 db01.tb_user
06-22T03:24:42      0      0        1          0       1       0   0.105 db01.tb_user_copy1

比较从主数据库不一致的地方

[root@5ab7eea3751f ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=db01 h=172.17.0.2,u=user0001,p='User0001!',P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-table-checksum line 332.
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-table-checksum line 332.
06-22T03:35:39 Skipping table db01.sbtest1 because it has problems on these replicas:
Table db01.sbtest1 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest10 because it has problems on these replicas:
Table db01.sbtest10 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest11 because it has problems on these replicas:
Table db01.sbtest11 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest12 because it has problems on these replicas:
Table db01.sbtest12 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest13 because it has problems on these replicas:
Table db01.sbtest13 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Error checksumming table db01.sbtest13: Error getting row count estimate of table db01.sbtest13 on replica 5ab7eea3751f: DBD::mysql::db selectrow_hashref failed: Table 'db01.sbtest13' doesn't exist [for Statement "EXPLAIN SELECT * FROM `db01`.`sbtest13` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6931.

TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
06-22T03:35:39 1 0 0 0 0 0 0.004 db01.sbtest13
06-22T03:35:39 Skipping table db01.sbtest14 because it has problems on these replicas:
Table db01.sbtest14 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest15 because it has problems on these replicas:
Table db01.sbtest15 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest16 because it has problems on these replicas:
Table db01.sbtest16 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest2 because it has problems on these replicas:
Table db01.sbtest2 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest3 because it has problems on these replicas:
Table db01.sbtest3 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest4 because it has problems on these replicas:
Table db01.sbtest4 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest5 because it has problems on these replicas:
Table db01.sbtest5 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest6 because it has problems on these replicas:
Table db01.sbtest6 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest7 because it has problems on these replicas:
Table db01.sbtest7 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest8 because it has problems on these replicas:
Table db01.sbtest8 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
06-22T03:35:39 Skipping table db01.sbtest9 because it has problems on these replicas:
Table db01.sbtest9 does not exist on replica 5ab7eea3751f
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.

同步数据

[root@781c38071bbd ~]# pt-table-sync --databases=db02 --execute h=172.17.0.2,u=user0001,p=User0001! h=172.17.0.3 --no-bin-log --verbose
# Syncing h=172.17.0.3,p=...,u=user0001
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      8      0 Chunk     06:46:26 06:46:26 2    db02.tbl