percona-toolkit工具包的安装和初步使用

时间：2021-09-26 18:46:41


一、percona-toolkit工具简介

    percona-toolkit简称“PT工具”，是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务，这些任务包括：

检查master和slave数据的一致性

有效地对记录进行归档

查找重复的索引

对服务器信息进行汇总

分析来自日志和tcpdump的查询

当系统出问题的时候收集重要的系统信息

    percona-toolkit源自Maatkit 和Aspersa工具，这两个工具是管理mysql的最有名的工具，现在Maatkit工具已经不维护了，请大家还是使用percona-toolkit吧！这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类，作为一个优秀的DBA，里面有的工具非常有用，如果能掌握并加以灵活应用，将能极大的提高工作效率。


 二、percona-toolkit安装步骤与过程

    我的环境是CentOS 6.5系统+ MySQL 5.5.35，可以按照下面的步骤安装pt工具。

    1.检查和安装与Perl相关的模块

    PT工具是使用Perl语言编写和执行的，所以需要系统中有Perl环境。

    依赖包检查命令为：

rpm -qa perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL

    如果有依赖包确实，可以使用下面的命令安装：

yum install perl-DBI

yum install perl-DBD-MySQL

yum install perl-Time-HiRes

yum install perl-IO-Socket-SSL


    2.percona-toolkit工具的下载和安装

pt工具目前最新版本为 2.2.12 ，可以通过下面的连接下载：

http://download.csdn.net/detail/yumushui/8379565

也可以可以在官方下载最新版本：

wget percona.com/get/percona-toolkit.tar.gz

wget percona.com/get/percona-toolkit.rpm


   A. percona-toolkit的rpm安装方式

rpm -ivh perl-TermReadKey-2.30-1.el5.rf.x86_64.rpm

rpm -ivh percona-toolkit-2.1.1-1.noarch.rpm

注意：需要安装Term::ReadKey 包，否则会报perl(Term::ReadKey) >= 2.10 is needed by percona-toolkit-2.1.1-1.noarch错误


   B. percona-toolkit的编译安装方式

tar xzvf percona-toolkit-2.1.1.tar.gz

cd percona-toolkit-2.1.1

perl Makefile.PL

make

make test

make install


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

# pt-query-digest --help

# pt-table-checksum --help

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


******************************

下面是我在一台服务器上的具体安装过程：

# wget percona.com/get/percona-toolkit.tar.gz 
--2014-09-16 16:55:46-- http://percona.com/get/percona-toolkit.tar.gz 
Resolving percona.com... 74.121.199.234 
Connecting to percona.com|74.121.199.234|:80... connected. 
HTTP request sent, awaiting response... 302 Found 
Location: http://www.percona.com/get/percona-toolkit.tar.gz [following] 
--2014-09-16 16:55:49-- http://www.percona.com/get/percona-toolkit.tar.gz 
Resolving www.percona.com... 74.121.199.234 
Reusing existing connection to percona.com:80. 
HTTP request sent, awaiting response... 302 Found 
Location: http://www.percona.com/downloads/percona-toolkit/2.2.10/tarball/percona-toolkit-2.2.10.tar.gz [following] 
--2014-09-16 16:55:57-- http://www.percona.com/downloads/percona-toolkit/2.2.10/tarball/percona-toolkit-2.2.10.tar.gz 
Reusing existing connection to percona.com:80. 
HTTP request sent, awaiting response... 200 OK 
Length: 1389083 (1.3M) [application/x-gzip] 
Saving to: “percona-toolkit-2.2.10.tar.gz” 

100%[==========================================================================================>] 1,389,083 253K/s in 
8.0s 

2014-09-16 16:56:05 (170 KB/s) - “percona-toolkit-2.2.10.tar.gz” saved [1389083/1389083] 

# perl Makefile.PL 
Checking if your kit is complete... 
Looks good 
Writing Makefile for percona-toolkit 
[root@nagios percona-toolkit-2.2.10]# make 
cp bin/pt-mysql-summary blib/script/pt-mysql-summary 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mysql-summary 
cp bin/pt-pmp blib/script/pt-pmp 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-pmp 
cp bin/pt-kill blib/script/pt-kill 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-kill 
cp bin/pt-online-schema-change blib/script/pt-online-schema-change 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-online-schema-change 
cp bin/pt-align blib/script/pt-align 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-align 
cp bin/pt-heartbeat blib/script/pt-heartbeat 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-heartbeat 
cp bin/pt-table-sync blib/script/pt-table-sync 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-sync 
cp bin/pt-upgrade blib/script/pt-upgrade 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-upgrade 
cp bin/pt-table-usage blib/script/pt-table-usage 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-usage 
cp bin/pt-slave-delay blib/script/pt-slave-delay 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-slave-delay 
cp bin/pt-sift blib/script/pt-sift 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-sift 
cp bin/pt-fifo-split blib/script/pt-fifo-split 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fifo-split 
cp bin/pt-slave-find blib/script/pt-slave-find 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-slave-find 
cp bin/pt-ioprofile blib/script/pt-ioprofile 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-ioprofile 
cp bin/pt-diskstats blib/script/pt-diskstats 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-diskstats 
cp bin/pt-find blib/script/pt-find 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-find 
cp bin/pt-archiver blib/script/pt-archiver 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-archiver 
cp bin/pt-deadlock-logger blib/script/pt-deadlock-logger 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-deadlock-logger 
cp bin/pt-fingerprint blib/script/pt-fingerprint 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fingerprint 
cp bin/pt-visual-explain blib/script/pt-visual-explain 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-visual-explain 
cp bin/pt-variable-advisor blib/script/pt-variable-advisor 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-variable-advisor 
cp bin/pt-mext blib/script/pt-mext 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-mext 
cp bin/pt-index-usage blib/script/pt-index-usage 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-index-usage 
cp bin/pt-slave-restart blib/script/pt-slave-restart 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-slave-restart 
cp bin/pt-summary blib/script/pt-summary 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-summary 
cp bin/pt-duplicate-key-checker blib/script/pt-duplicate-key-checker 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-duplicate-key-checker 
cp bin/pt-fk-error-logger blib/script/pt-fk-error-logger 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-fk-error-logger 
cp bin/pt-table-checksum blib/script/pt-table-checksum 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-table-checksum 
cp bin/pt-query-digest blib/script/pt-query-digest 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-query-digest 
cp bin/pt-show-grants blib/script/pt-show-grants 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-show-grants 
cp bin/pt-config-diff blib/script/pt-config-diff 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-config-diff 
cp bin/pt-stalk blib/script/pt-stalk 
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pt-stalk 
Manifying blib/man1/pt-mysql-summary.1p 
Manifying blib/man1/pt-kill.1p 
Manifying blib/man1/pt-online-schema-change.1p 
Manifying blib/man1/pt-table-sync.1p 
Manifying blib/man1/pt-upgrade.1p 
Manifying blib/man1/pt-table-usage.1p 
Manifying blib/man1/pt-fifo-split.1p 
Manifying blib/man1/pt-slave-find.1p 
Manifying blib/man1/pt-ioprofile.1p 
Manifying blib/man1/pt-find.1p 
Manifying blib/man1/pt-archiver.1p 
Manifying blib/man1/pt-deadlock-logger.1p 
Manifying blib/man1/pt-fingerprint.1p 
Manifying blib/man1/pt-mext.1p 
Manifying blib/man1/pt-slave-restart.1p 
Manifying blib/man1/pt-summary.1p 
Manifying blib/man1/pt-fk-error-logger.1p 
Manifying blib/man1/pt-table-checksum.1p 
Manifying blib/man1/pt-query-digest.1p 
Manifying blib/man1/pt-show-grants.1p 
Manifying blib/man1/percona-toolkit.1p 
Manifying blib/man1/pt-pmp.1p 
Manifying blib/man1/pt-align.1p 
Manifying blib/man1/pt-heartbeat.1p 
Manifying blib/man1/pt-slave-delay.1p 
Manifying blib/man1/pt-sift.1p 
Manifying blib/man1/pt-diskstats.1p 
Manifying blib/man1/pt-visual-explain.1p 
Manifying blib/man1/pt-variable-advisor.1p 
Manifying blib/man1/pt-index-usage.1p 
Manifying blib/man1/pt-duplicate-key-checker.1p 
Manifying blib/man1/pt-config-diff.1p 
Manifying blib/man1/pt-stalk.1p 
[root@nagios percona-toolkit-2.2.10]# 
# 
[root@nagios percona-toolkit-2.2.10]# make test 
No tests defined for percona-toolkit extension. 
[root@nagios percona-toolkit-2.2.10]# 
[root@nagios percona-toolkit-2.2.10]# make install 
Installing /usr/local/share/man/man1/pt-summary.1p 
Installing /usr/local/share/man/man1/pt-ioprofile.1p 
Installing /usr/local/share/man/man1/pt-query-digest.1p 
Installing /usr/local/share/man/man1/pt-sift.1p 
Installing /usr/local/share/man/man1/pt-align.1p 
Installing /usr/local/share/man/man1/pt-find.1p 
Installing /usr/local/share/man/man1/pt-table-checksum.1p 
Installing /usr/local/share/man/man1/pt-slave-restart.1p 
Installing /usr/local/share/man/man1/pt-duplicate-key-checker.1p 
Installing /usr/local/share/man/man1/pt-stalk.1p 
Installing /usr/local/share/man/man1/pt-fk-error-logger.1p 
Installing /usr/local/share/man/man1/pt-config-diff.1p 
Installing /usr/local/share/man/man1/pt-table-usage.1p 
Installing /usr/local/share/man/man1/pt-diskstats.1p 
Installing /usr/local/share/man/man1/pt-visual-explain.1p 
Installing /usr/local/share/man/man1/pt-show-grants.1p 
Installing /usr/local/share/man/man1/pt-kill.1p 
Installing /usr/local/share/man/man1/pt-archiver.1p 
Installing /usr/local/share/man/man1/pt-upgrade.1p 
Installing /usr/local/share/man/man1/pt-mext.1p 
Installing /usr/local/share/man/man1/pt-slave-delay.1p 
Installing /usr/local/share/man/man1/pt-heartbeat.1p 
Installing /usr/local/share/man/man1/pt-mysql-summary.1p 
Installing /usr/local/share/man/man1/pt-online-schema-change.1p 
Installing /usr/local/share/man/man1/pt-fifo-split.1p 
Installing /usr/local/share/man/man1/pt-variable-advisor.1p 
Installing /usr/local/share/man/man1/pt-slave-find.1p 
Installing /usr/local/share/man/man1/pt-fingerprint.1p 
Installing /usr/local/share/man/man1/percona-toolkit.1p 
Installing /usr/local/share/man/man1/pt-pmp.1p 
Installing /usr/local/share/man/man1/pt-deadlock-logger.1p 
Installing /usr/local/share/man/man1/pt-index-usage.1p 
Installing /usr/local/share/man/man1/pt-table-sync.1p 
Installing /usr/local/bin/pt-variable-advisor 
Installing /usr/local/bin/pt-visual-explain 
Installing /usr/local/bin/pt-slave-restart 
Installing /usr/local/bin/pt-align 
Installing /usr/local/bin/pt-fk-error-logger 
Installing /usr/local/bin/pt-duplicate-key-checker 
Installing /usr/local/bin/pt-heartbeat 
Installing /usr/local/bin/pt-table-usage 
Installing /usr/local/bin/pt-upgrade 
Installing /usr/local/bin/pt-archiver 
Installing /usr/local/bin/pt-find 
Installing /usr/local/bin/pt-kill 
Installing /usr/local/bin/pt-ioprofile 
Installing /usr/local/bin/pt-query-digest 
Installing /usr/local/bin/pt-slave-delay 
Installing /usr/local/bin/pt-fingerprint 
Installing /usr/local/bin/pt-table-checksum 
Installing /usr/local/bin/pt-summary 
Installing /usr/local/bin/pt-pmp 
Installing /usr/local/bin/pt-fifo-split 
Installing /usr/local/bin/pt-online-schema-change 
Installing /usr/local/bin/pt-slave-find 
Installing /usr/local/bin/pt-table-sync 
Installing /usr/local/bin/pt-stalk 
Installing /usr/local/bin/pt-diskstats 
Installing /usr/local/bin/pt-sift 
Installing /usr/local/bin/pt-show-grants 
Installing /usr/local/bin/pt-mysql-summary 
Installing /usr/local/bin/pt-config-diff 
Installing /usr/local/bin/pt-index-usage 
Installing /usr/local/bin/pt-deadlock-logger 
Installing /usr/local/bin/pt-mext 
Appending installation info to /usr/lib64/perl5/perllocal.pod 
[root@nagios percona-toolkit-2.2.10]# 
[root@nagios percona-toolkit-2.2.10]#




 三、percona-toolkit命令的整体分类和作用概述


在最新的 pt 2.2.12 版本安装完毕后，一共有如下命令：

# pwd 
/usr/local/percona-toolkit-2.2.12/bin 
# 
# ll 
total 5352 
-rwxrwxr-x 1 1000 1000 40852 Nov 11 21:36 pt-align 
-rwxrwxr-x 1 1000 1000 247292 Nov 11 21:36 pt-archiver 
-rwxrwxr-x 1 1000 1000 166888 Nov 11 21:36 pt-config-diff 
-rwxrwxr-x 1 1000 1000 164033 Nov 11 21:36 pt-deadlock-logger 
-rwxrwxr-x 1 1000 1000 163722 Nov 11 21:36 pt-diskstats 
-rwxrwxr-x 1 1000 1000 166921 Nov 11 21:36 pt-duplicate-key-checker 
-rwxrwxr-x 1 1000 1000 49262 Nov 11 21:36 pt-fifo-split 
-rwxrwxr-x 1 1000 1000 148347 Nov 11 21:36 pt-find 
-rwxrwxr-x 1 1000 1000 66409 Nov 11 21:36 pt-fingerprint 
-rwxrwxr-x 1 1000 1000 131003 Nov 11 21:36 pt-fk-error-logger 
-rwxrwxr-x 1 1000 1000 190538 Nov 11 21:36 pt-heartbeat 
-rwxrwxr-x 1 1000 1000 224641 Nov 11 21:36 pt-index-usage 
-rwxrwxr-x 1 1000 1000 32276 Nov 11 21:36 pt-ioprofile 
-rwxrwxr-x 1 1000 1000 245236 Nov 11 21:36 pt-kill 
-rwxrwxr-x 1 1000 1000 21684 Nov 11 21:36 pt-mext 
-rwxrwxr-x 1 1000 1000 100494 Nov 11 21:36 pt-mysql-summary 
-rwxrwxr-x 1 1000 1000 368714 Nov 11 21:36 pt-online-schema-change 
-rwxrwxr-x 1 1000 1000 24525 Nov 11 21:36 pt-pmp 
-rwxrwxr-x 1 1000 1000 516529 Nov 11 21:36 pt-query-digest 
-rwxrwxr-x 1 1000 1000 72384 Nov 11 21:36 pt-show-grants 
-rwxrwxr-x 1 1000 1000 37651 Nov 11 21:36 pt-sift 
-rwxrwxr-x 1 1000 1000 144190 Nov 11 21:36 pt-slave-delay 
-rwxrwxr-x 1 1000 1000 125951 Nov 11 21:36 pt-slave-find 
-rwxrwxr-x 1 1000 1000 178193 Nov 11 21:36 pt-slave-restart 
-rwxrwxr-x 1 1000 1000 69227 Nov 11 21:36 pt-stalk 
-rwxrwxr-x 1 1000 1000 89799 Nov 11 21:36 pt-summary 
-rwxrwxr-x 1 1000 1000 409966 Nov 11 21:36 pt-table-checksum 
-rwxrwxr-x 1 1000 1000 394568 Nov 11 21:36 pt-table-sync 
-rwxrwxr-x 1 1000 1000 222447 Nov 11 21:36 pt-table-usage 
-rwxrwxr-x 1 1000 1000 328098 Nov 11 21:36 pt-upgrade 
-rwxrwxr-x 1 1000 1000 175665 Nov 11 21:36 pt-variable-advisor 
-rwxrwxr-x 1 1000 1000 101492 Nov 11 21:36 pt-visual-explain 
# 
# ls | wc 
32 32 455


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

工具类别

工具命令

工具作用

备注

开发类

pt-duplicate-key-checker

列出并删除重复的索引和外键

pt-online-schema-change

在线修改表结构

pt-query-advisor

分析查询语句，并给出建议，有bug

已废弃

pt-show-grants

规范化和打印权限

pt-upgrade

在多个服务器上执行查询，并比较不同

性能类

pt-index-usage

分析日志中索引使用情况，并出报告

pt-pmp

为查询结果跟踪，并汇总跟踪结果

pt-visual-explain

格式化执行计划

pt-table-usage

分析日志中查询并分析表使用情况

pt 2.2新增命令

配置类

pt-config-diff

比较配置文件和参数

pt-mysql-summary

对mysql配置和status进行汇总

pt-variable-advisor

分析参数，并提出建议

监控类

pt-deadlock-logger

提取和记录mysql死锁信息

pt-fk-error-logger

提取和记录外键信息

pt-mext

并行查看status样本信息

pt-query-digest

分析查询日志，并产生报告

常用命令

pt-trend

按照时间段读取slow日志信息

已废弃

复制类

pt-heartbeat

监控mysql复制延迟

pt-slave-delay

设定从落后主的时间

pt-slave-find

查找和打印所有mysql复制层级关系

pt-slave-restart

监控salve错误，并尝试重启salve

pt-table-checksum

校验主从复制一致性

pt-table-sync

高效同步表数据

系统类

pt-diskstats

查看系统磁盘状态

pt-fifo-split

模拟切割文件并输出

pt-summary

收集和显示系统概况

pt-stalk

出现问题时，收集诊断数据

pt-sift

浏览由pt-stalk创建的文件

pt 2.2新增命令

pt-ioprofile

查询进程IO并打印一个IO活动表

pt 2.2新增命令

实用类

pt-archiver

将表数据归档到另一个表或文件中

pt-find

查找表并执行命令

pt-kill

Kill掉符合条件的sql

 常用命令

pt-align

对齐其他工具的输出

pt 2.2新增命令

pt-fingerprint

将查询转成密文

pt 2.2新增命令

上面是pt工具各个命令的基本功能介绍，可以使用 command --help 来查看每个命令的具体作用和使用方法；

有的命令也可以使用 man command 命令查询相关命令详细信息。

目前使用的比较多的命令是： pt-query-digest ，pt-kill等命令。

标签：

相关文章