安装percona-toolkit工具包
yum install percona-toolkit
安装后,可通过以下命令确认安装是否成功:
如果命令提示可以正常显示,则表示pt工具已正常安装和使用。
pt-query-digest --help pt-table-checksum --help
有的32个命令可7类
实践
比较主要来自数据库不一致的地方
[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 todisable 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