percona-toolkit使用教程

1：慢日志查询

[root@test_dx modify]# wget percona.com/get/pt-query-digest

[root@test_dx modify]# file pt-query-digest
pt-query-digest: a perl script text executable
[root@test_dx modify]# ll  pt-query-digest
-rw-r--r-- 1 root root 499727 09-02 00:01 pt-query-digest
[root@test_dx modify]# chmod u+x pt-query-digest
使用：
直接上就行了，简单粗暴也没有问题。
事实上，这个工具确实有点简单粗暴，如果slow log够大的话，会消耗相当多的cpu和内存，
所以最好把slow log和pt-query-digest放到其它的server上面运行。

[root@test_dx modify]# ./pt-query-digest  dxdb1-slow.log  >digest.log

这是过滤单个数据库的慢日志

pt-query-digest --filter '($event->{db} || "") =~ m/mydb/i' slowlog

mydb 换成你的库名

说明：=~ 念做 “does match" ，是匹配的意思

看一下digest.log的内容。
第一部分是摘要：



# 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz
# CURRENT DATE: Thu DEC 29 13:22:42 2011
# Hostname: test_dx
# Files: dxdb1-slow.log
# Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________
# TIME range: 2011-09-10 04:03:19 TO 2011-12-29 05:02:51
# Attribute          total     MIN     MAX     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# EXEC TIME          5657s      2s     33s      7s     23s      6s      5s
# LOCK TIME            33s       0     19s    43ms    98us   715ms    38us
# ROWS sent        323.38k       0 107.36k  426.73    0.99   6.35k       0
# ROWS examine     323.39k       0 107.36k  426.74       0   6.35k       0
# Query SIZE       217.95k      38     562  287.61  420.77   81.78  284.79
从这份摘要中可以看出，dxdb1-slow.log中共有776条SQL语句， 去掉重复(至于怎样才算是重复，下文会有介绍)，有11条SQL。
然后又有很多的Attribute和很多的值，比如total(总和)，min最小值, max最大值, avg平均值,
95%(*)，stddev（不知道是啥，标准差？）和median中位数。
中位数的概念就是把这些值从小到大排序，位置位于最中间的那个数。

在这些值中，最有意义的恐怕就是95%了，与中位数类似，它也是把所有值从小到大排列，位置位于95%的那个数。
它过滤了一些非常大的值，因此更有统计学上的意义，其实统计学上更喜欢用四分位数(1/4, 1/2, 3/4), 中位数也是四分位数之一。

另外，根据SQL语句的不同，可能会有更多的Attribute显示出来。 比如Tmp tables, Tmp disk tbl，Tmp tbl size之类的。

继续看第二部分：

# Profile
# Rank Query ID           Response time Calls R/Call  V/M   Item
# ==== ================== ============= ===== ======= ===== ==============
#    1 0x00239D37BCCD1A15 27.2769 50.5%     2 13.6385  2.08 INSERT SELECT t salaries
#    2 0x353AEDED7F4994AE 10.5201 19.5%     8  1.3150  0.05 INSERT salaries
#    3 0xD745177803E00425  6.8374 12.7%     4  1.7094  0.04 INSERT titles
#    4 0xAC215D9D2C7DD498  5.8919 10.9%     2  2.9460  1.09 SELECT salaries

#    5 0x67A347A2812914DF  3.4780  6.4%     1  3.4780  0.00 SELECT salaries

 

 

Rank:分析的所有查询语句的排名，默认按查询时间降序排序，可以通过–order-by指定排序方式

Query ID：查询语句的指纹，去掉了多余空格、和文本字符

Response:总的响应时间
time:该查询在本次分析中占用的时间比
Calls:执行次数

R/Call:平均每次执行的响应时间

V/M：响应时间Variance-to-mean的比率，参考：http://en.wikipedia.org/wiki/Index_of_dispersion

Item:查询对象

 

这一部分显示了最慢的七种类型的SQL语句。

我这里最慢的是INSERT INTO test_show …… 共有547条语句，虽然每次插入的数据都是不同的，但也被归于同一类型的语句了。

第三部分最重要了。
以排名第七的SQL为例。

# Query 7: 0.00 QPS, 0.00x concurrency, ID 0x40B69AB7631E4445 at byte 203205
# Scores: Apdex = 0.15 [1.0]*, V/M = 3.48
# Query_time sparkline: |      ^_|
# TIME range: 2011-11-24 05:02:15 TO 2011-12-10 05:02:43
# Attribute    pct   total     MIN     MAX     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# COUNT          1      13
# EXEC TIME      1     85s      2s     20s      7s     10s      5s      5s
# LOCK TIME      0     1ms       0   118us    80us   108us    29us    86us
# ROWS sent      0      12       0       1    0.92    0.99    0.26    0.99
# ROWS examine   0      16       0       3    1.23    2.90    1.09    0.99
# Query SIZE     0   1.16k      91      91      91      91       0      91
# String:
# DATABASES    test
# Hosts        dx_web_1
# Users        test_user
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ###################
# TABLES
#    SHOW TABLE STATUS FROM `test` LIKE 'test_show'G
#    SHOW CREATE TABLE `test`.`test_show`G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS modify___c FROM test_show WHERE  ip=1303055780 AND web_id=5 AND ad_id = 4G
从上面可以看出，共有13条语句，[95%]Exec time是10s，时间长得比较离谱了。
数据库为test，主机为dx_web_1，用户名为test_user,
然后是query time的分布图，这个图太恶心了，不过也可以看得出来大部分是处于1-10s之间的， 还有一些超过10秒了。

最后是几条SQL语句， 是pt-query-digest生成的，这些语句有助于分析问题。

事实上，pt-query-digest的功能远远不止于此，除了直接分析slow query log并生成报告，
它还可以filter, replay, transform queries for mysql,postgresql, memcached!
去看官方文档吧。

其它文档：

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_long_query_time

http://www.percona.com/doc/percona-server/5.1/diagnostics/slow_extended.html

------------------------------------------------------------------------------------------------------
pt-show-grants --only aus4_dev       查看某个用户的grants

传统方式中，如果要查看mysql中某一用户的权限，一般是这样操作的:

mysql> SELECT host FROM mysql.USER WHERE USER='aus4_dev';

mysql> SHOW GRANTS FOR aus4_dev@HOST;
如果一个用户对应着很多host，那么就要多次执行show grants 语句，确实有点烦。

有了pt-show-grants ，生活就会变得简单多了。

[scabral@dev1.db ~]$ bin/pt-show-grants --only aus4_dev
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.1.52-log at 2012-03-01 08:52:01
-- Grants for 'aus4_dev'@'10.0.0.1'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.1' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';
-- Grants for 'aus4_dev'@'10.0.0.2'
GRANT USAGE ON *.* TO 'aus4_dev'@'10.0.0.2' IDENTIFIED BY PASSWORD '*1234567890ABCDEF1234567890ABCDEF12345678';
GRANT ALL PRIVILEGES ON `aus4_dev`.* TO 'aus4_dev'@'10.0.0.2';
除了–only之外，还有–ignore。

2：主从不一致校验及修改(注意所有操作都在主库上执行)

要求：主从的表上必须有主键

          要同步的表必须在同步的范围内

 

pt-table-checksum --nocheck-replication-filters --databases=percona --replicate=test.checksums --create-replicate-table --host=192.168.1.88 --port 3306 -uroot -p123456

 

pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=123456 h=192.168.1.90 --database=percona  --port=3306  --print

 

pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=123456 h=192.168.1.90 --database=percona  --port=3306  --execute

参数说明：

--nocheck-replication-filters ：不检查复制过滤器，建议启用。

--databases来指定需要检查的数据库，如果不指定的话则是全库检查。

--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。

--replicate-check-only :只显示不同步的信息。

--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。

--databases= ：指定需要被检查的数据库，多个则用逗号隔开。

--tables= ：指定需要被检查的表，多个用逗号隔开

h=192.168.1.20 ：Master的地址

u=root ：用户名

p=123456：密码

P=5535 ：端口

 

自动化脚本

 

cat checksum.sh

#!/bin/sh

export PATH=$PATH:/application/mariadb10/bin

master_ip=192.168.1.88

slave_ip=192.168.1.90

ssh 192.168.1.90 "/data0/script/delete.sh"

for dbs in `mysql -e "show databases"|grep -Evi "data|infor|per|mysql|test"`

do

  pt-table-checksum --nocheck-replication-filters --databases=$dbs --replicate=percona.checksums --create-replicate-table --host=$master_ip --port 3306 -uroot -p123456

 

  pt-table-sync --execute --sync-to-master --charset=utf8 --user=root --password=123456 h=$slave_ip --database=$dbs  --port=3306  --execute

done

 

percona-toolkit使用教程

一、 percona-toolkit简介

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

 检查master和slave数据的一致性

 有效地对记录进行归档

 查找重复的索引

 对服务器信息进行汇总

 分析来自日志和tcpdump的查询

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

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

二、 percona-toolkit工具包安装 1. 软件包下载 访问http://www.percona.com/software/percona-toolkit/下载最新版本的Percona Toolkit 或者通过如下命令行来获取最新的版本： wget percona.com/get/percona-toolkit.tar.gz wget percona.com/get/percona-toolkit.rpm 我这里选择直接从网站上找到最新版本下载： wget http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1-1.noarch.rpm wget http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1.tar.gz

从http://pkgs.repoforge.org/perl-TermReadKey/下载最新的TermReadKey包 wget http://pkgs.repoforge.org/perl-TermReadKey/perl-TermReadKey-2.30-1.el5.rf.x86_64.rpm

2. 软件包安装

我的环境是Centos 5.5 64 BIT

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 install && echo 0

三、 percona-toolkit的使用

根据percona-toolkit的工具类型可以总结出下面五个类别，方便大家进行学习和实践，下面就针对这些不同的类别来分别介绍这些工具的用法。

(一) 开发类工具

1. pt-duplicate-key-checker

 功能介绍：

功能为从mysql表中找出重复的索引和外键，这个工具会将重复的索引和外键都列出来，并生成了删除重复索引的语句，非常方便

 用法介绍： pt-duplicate-key-checker [OPTION...] [DSN] 包含比较多的选项，具体的可以通过命令pt-duplicate-key-checker --help来查看具体支持那些选项，我这里就不一一列举了。DNS为数据库或者表。

 使用示例：

查看test数据库的重复索引和外键使用情况使用如下命令 pt-duplicate-key-checker --host=localhost --user=root --password=zhang@123 --databases=test

2. pt-online-schema-change

 功能介绍：

功能为在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作，注意执行这个工具的时候必须做好备份，操作之前最好详细读一下官方文档http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html。 工作原理是创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

 用法介绍： pt-online-schema-change [OPTIONS] DSN

options可以自行查看help，DNS为你要操作的数据库和表。

这里有两个参数需要介绍一下： --dry-run 这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute 这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。这一举措是为了让使用这充分了解了这个工具的原理，同时阅读了官方文档。

 

依赖条件:操作的表必须有主键否则 报如下错误。

pt-online-schema-change -u root -h 10.250.7.50  -p yang --alter='add column vid int ' --execute D=houyi,t=ga

Cannot connect to D=houyi,h=127.0.0.1,p=...,u=root

Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

 使用示例：

范例1：在线更改表的的引擎，这个尤其在整理innodb表的时候非常有用，示例如下： pt-online-schema-change --user=root --password=zhang@123 --host=localhost --lock-wait-time=120 --alter="ENGINE=InnoDB" D=test,t=oss_pvinfo2 --execute 从下面的日志中可以看出它的执行过程： Altering `test`.`oss_pvinfo2`... Creating new table... Created new table test._oss_pvinfo2_new OK. Altering new table... Altered `test`.`_oss_pvinfo2_new` OK. Creating triggers... Created triggers OK. Copying approximately 995696 rows... Copied rows OK. Swapping tables... Swapped original and new tables OK. Dropping old table... Dropped old table `test`.`_oss_pvinfo2_old` OK. Dropping triggers... Dropped triggers OK. Successfully altered `test`.`oss_pvinfo2`.

 

pt-online-schema-change --user=root --password=123456 --host=localhost  --alter="ENGINE=InnoDB" D=glpi_1,t=glpi_logs --execute       

范例2：大表添加字段的，语句如下:

pt-online-schema-change --user=root --password=zhang@123 --host=localhost --lock-wait-time=120 --alter="ADD COLUMN domain_id INT" D=test,t=oss_pvinfo2 --execute

范例3：大表删除字段，语句如下

pt-online-schema-change --user=root --password=123456  --socket=/home/mysql/mysql_5621/tmp/mysql.sock --charset=utf-8  --alter="drop COLUMN url" D=test,t=t1 --execute

3. pt-query-advisor

 功能介绍：

根据一些规则分析查询语句，对可能的问题提出建议，这些评判规则大家可以看一下官网的链接：http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html，这里就不详细列举了。那些查询语句可以来自慢查询文件、general日志文件或者使用pt-query-digest截获的查询语句。目前这个版本有bug，当日志文件非常大的时候会需要很长时间甚至进入死循环。

 用法介绍： pt-query-advisor /path/to/slow-query.log

pt-query-advisor --type genlog mysql.log pt-query-digest --type tcpdump.txt --print --no-report | pt-query-advisor

 使用示例：

分析一个语句的例子： pt-query-advisor --query "select * from aaa" 分析general log中的查询语句的例子： pt-query-advisor /data/dbdata/general.log 分析慢查询中的查询语句的例子： pt-query-advisor /data/dbdata/localhost-slow.log

4. pt-show-grants

 功能介绍：

规范化和打印mysql权限，让你在复制、比较mysql权限以及进行版本控制的时候更有效率！

 用法介绍： pt-show-grants [OPTION...] [DSN] 选项自行用help查看，DSN选项也请查看help，选项区分大小写。

 使用示例：

查看指定mysql的所有用户权限： pt-show-grants --host='localhost' --user='root' --password='zhang@123' 查看执行数据库的权限： pt-show-grants --host='localhost' --user='root' --password='zhang@123' --database='hostsops' 查看每个用户权限生成revoke收回权限的语句： pt-show-grants --host='localhost' --user='root' --password='zhang@123' --revoke

5. pt-upgrade

 功能介绍：

在多台服务器上执行查询，并比较有什么不同！这在升级服务器的时候非常有用，可以先安装并导数据到新的服务器上，然后使用这个工具跑一下sql看看有什么不同，可以找出不同版本之间的差异。

 用法介绍： pt-upgrade [OPTION...] DSN [DSN...] [FILE] 比较文件中每一个查询语句在两个主机上执行的结果，并检查在每个服务器上执行的结果、错误和警告。

 使用示例：

只查看某个sql在两个服务器的运行结果范例： pt-upgrade h='localhost' h=192.168.3.92 --user=root --password=zhang@123 --query="select * from user_data.collect_data limit 5" 查看文件中的对应sql在两个服务器的运行结果范例：

pt-upgrade h='localhost' h=192.168.3.92 --user=root --password=zhang@123 aaa.sql 查看慢查询中的对应的查询SQL在两个服务器的运行结果范例： pt-upgrade h='localhost' h=192.168.3.92 --user=root --password=zhang@123 slow.log 此外还可以执行compare的类型，主要包含三个query_times,results,warnings，比如下面的例子，只比较sql的执行时间 pt-upgrade h=192.168.3.91 h=192.168.3.92 --user=root --password=zhang@123 --query="select * from user_data.collect_data" --compare query_times

(二) 性能类工具

1. pt-index-usage

 功能介绍：

从log文件中读取插叙语句，并用explain分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。

 用法介绍： pt-index-usage [OPTION...] [FILE...] 可以直接从慢查询中获取sql，FILE文件中的sql格式必须和慢查询中个是一致，如果不是一直需要用pt-query-digest转换一下。也可以不生成报告直接保存到数据库中，具体的见后面的示例

 使用示例：

从满查询中的sql查看索引使用情况范例： pt-index-usage /data/dbdata/localhost-slow.log --host=localhost --user=root --password=zhang@123 将分析结果保存到数据库范例： pt-index-usage /data/dbdata/localhost-slow.log --host=localhost --user=root --password=zhang@123 --no-report --create-save-results-database 使用--create-save-results-database会自动生成数据库和表来保存结果。

2. pt-pmp

 功能介绍：

为查询程序执行聚合的GDB堆栈跟踪，先进性堆栈跟踪，然后将跟踪信息汇总。

 用法介绍： pt-pmp [OPTIONS] [FILES]

 使用示例： pt-pmp -p 21933 pt-pmp -b /usr/local/mysql/bin/mysqld_safe

3. pt-visual-explain

 功能介绍：

格式化explain出来的执行计划按照tree方式输出，方便阅读。

 用法介绍： pt-visual-explain [OPTION...] [FILE...] option请参阅官方网站，这里不一一例举！

 使用示例：

查看包含explain结果的aaa文件的范例： pt-visual-explain aaa 查看包含查询语句的aaa文件的范例： pt-visual-explain --connect aaa --user=root --password=zhang@123 通过管道直接查看explain输出结果的范例： mysql -uroot -pzhang@123 -e "explain select email from test.collect_data where id=101992419" |pt-visual-explain

(三) 配置类工具

1. pt-config-diff

 功能介绍：

比较mysql配置文件和服务器参数

 用法介绍： pt-config-diff [OPTION...] CONFIG CONFIG [CONFIG...] CONFIG可以是文件也可以是数据源名称，最少必须指定两个配置文件源，就像unix下面的diff命令一样，如果配置完全一样就不会输出任何东西。

 使用示例：

范例1：查看本地和远程服务器的配置文件差异： pt-config-diff h=localhost h=192.168.3.92 --user=root --password=zhang@123 比较出来内容如下： 22 config differences Variable localhost.localdomain localhost.localdomain ========================= ===================== ===================== binlog_cache_size 8388608 2097152 have_ndbcluster DISABLED NO innodb_additional_mem_... 16777216 33554432 innodb_buffer_pool_size 1677721600 1073741824

范例2：比较本地配置文件和远程服务器的差异： pt-config-diff /etc/my.cnf h=192.168.3.92 --user=root --password=zhang@123 比较出来内容如下： 12 config differences Variable /etc/my.cnf localhost.localdomain

========================= =========== ===================== binlog_cache_size 8388608 2097152 binlog_format mixed MIXED

范例3：比较本地两个配置文件的差异： pt-config-diff /usr/local/mysql/share/mysql/my-large.cnf /usr/local/mysql/share/mysql/my-medium.cnf

2. pt-mysql-summary

 功能介绍：

精细地对mysql的配置和sataus信息进行汇总，汇总后你直接看一眼就能看明白。

 用法介绍： pt-mysql-summary [OPTIONS] [-- MYSQL OPTIONS] 工作原理：连接mysql后查询出status和配置信息保存到临时目录中，然后用awk和其他的脚本工具进行格式化。OPTIONS可以查阅官网的相关页面。

 使用示例：

范例1：汇总本地mysql服务器的status和配置信息： pt-mysql-summary -- --user=root --password=zhang@123 --host=localhost 范例2：汇总本地mysql服务器192.168.3.92的status和配置信息： pt-mysql-summary -- --user=root --password=zhang@123 --host=192.168.3.92

3. pt-variable-advisor

 功能介绍：

分析mysql的参数变量，并对可能存在的问题提出建议

 用法介绍： pt-variable-advisor [OPTION...] [DSN] 原理：根据预先定义的规则检查show variables中的配置错误的设置和值。

 使用示例：

范例1：从localhost获取变量值 pt-variable-advisor --user=root --password=zhang@123 localhost 范例2：从指定的文件中读取配置，这个有格式要求 pt-variable-advisor --user=root --password=zhang@123 --source-of-variables my.cnf

(四) 监控类工具

1. pt-deadlock-logger

 功能介绍：

提取和记录mysql死锁的相关信息

 用法介绍： pt-deadlock-logger [OPTION...] SOURCE_DSN

收集和保存mysql上最近的死锁信息，可以直接打印死锁信息和存储死锁信息到数据库中，死锁信息包括发生死锁的服务器、最近发生死锁的时间、死锁线程id、死锁的事务id、发生死锁时事务执行了多长时间等等非常多的信息。详情见下面的示例。

 使用示例：

范例1：打印本地mysql的死锁信息 pt-deadlock-logger --user=root --password=zhang@123 h=localhost –print 范例2：将本地的mysql死锁信息记录到数据库的表中，也打印出来 pt-deadlock-logger --user=root --password=zhang@123 h=localhost --print D=test,t=deadlocks

2. pt-fk-error-logger

 功能介绍：

提取和记录mysql外键错误信息

 用法介绍： pt-fk-error-logger [OPTION...] SOURCE_DSN 通过SHOW INNODB STATUS提取和保存mysql数据库最近发生的外键错误信息。可以通过参数控制直接打印错误信息或者将错误信息存储到数据库的表中。

 使用示例：

我在服务器上运行的时候一直报如下错误： Use of uninitialized value in concatenation (.) or string at /usr/bin/pt-fk-error-logger line 2045 我怀疑是这个程序有问题，回头换一个版本试一下或者调试一下那个程序。

3. pt-mext

 功能介绍：

并行查看SHOW GLOBAL STATUS的多个样本的信息。

 用法介绍： pt-mext [OPTIONS] -- COMMAND 原理：pt-mext执行你指定的COMMAND，并每次读取一行结果，把空行分割的内容保存到一个一个的临时文件中，最后结合这些临时文件并行查看结果。

 使用示例：

范例1：每隔10s执行一次SHOW GLOBAL STATUS，并将结果合并到一起查看 pt-mext -- mysqladmin ext -uroot -pzhang@123 -i10 -c3

4. pt-query-digest

 功能介绍：

分析查询执行日志，并产生一个查询报告，为MySQL、PostgreSQL、 memcached过滤、重放或者转换语句。

 用法介绍： pt-query-digest [OPTION...] [FILE] 解析和分析mysql日志文件

 使用示例：

范例1：分析本地的慢查询文件 pt-query-digest --user=root --password=zhang@123 /data/dbdata/localhost-slow.log 范例2：重新回顾满查询日志，并将结果保存到query_review中，注意query_review表的表结构必须先建好，表结构如下： CREATE TABLE query_review ( checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY, fingerprint TEXT NOT NULL, sample TEXT NOT NULL, first_seen DATETIME, last_seen DATETIME, reviewed_by VARCHAR(20), reviewed_on DATETIME, comments TEXT ); 命令如下： pt-query-digest --user=root --password=zhang@123 --review h=localhost,D=test,t=query_review /data/dbdata/localhost-slow.log

5. pt-trend

 功能介绍：

居于一组时间序列的数据点做统计。

 用法介绍： pt-trend [OPTION...] [FILE ...] 读取一个慢查询日志，并输出统计信息。也可以指定多个文件。如果不指定文件的话直接从标准输入中读取信息。

 使用示例：

范例1：读取本地慢查询日志并输出统计信息 pt-trend /data/dbdata/localhost-slow.log 这里输出的信息没有说明，有点看不明白！

(五) 复制类工具

1. pt-heartbeat

 功能介绍：

监控mysql复制延迟

 用法介绍： pt-heartbeat [OPTION...] [DSN] --update|--monitor|--check|--stop 测量复制落后主mysql或者主PostgreSQL多少时间，你可以使用这个脚本去更新主或者监控复制，具体用法见后面的示例.

原理：pt-heartbeat通过真实的复制数据来确认mysql和postgresql

复制延迟，这个避免了对复制机制的依赖，从而能得出准确的落后复制时间，包含两部分：第一部分在主上pt-heartbeat的--update线程会在指定的时间间隔更新一个时间戳，第二部分是pt-heartbeat的--monitor线程或者--check线程连接到从上检查复制的心跳记录（前面更新的时间戳），并和当前系统时间进行比较，得出时间的差异。

你可以手工创建heartbeat表或者添加--create-table参数，推荐使用MEMORY引擎。表结构为： CREATE TABLE heartbeat ( ts varchar(26) NOT NULL, server_id int unsigned NOT NULL PRIMARY KEY, file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS );

 使用示例：

范例1： 建表

pt-heartbeat --user=root --password=123456 -S /home/mysql/mysql_5535/tmp/mysql.sock -D test --master-server-id=21 --create-table --update  

范例 2：更新主库上的heartbeat

pt-heartbeat --user=root --password=123456 -S /home/mysql/mysql_5535/tmp/mysql.sock -D test --master-server-id=21 --update &

范例 3： 将主库上的update使用守护进程方式调度

pt-heartbeat --user=root --password=123456 -S /home/mysql/mysql_5535/tmp/mysql.sock -D test --master-server-id=21 --update --daemonize

范例 4： 修改主库上的更新间隔为2s

pt-heartbeat --user=root --password=123456 -S /home/mysql/mysql_5535/tmp/mysql.sock -D test --master-server-id=21 --update --daemonize --interval=2

范例5： 使用守护进程监控从库并输出日志

pt-heartbeat --user=root --password=123456 -S /home/mysql/mysql_5535/tmp/mysql.sock -D test --master-server-id=21 --monitor --print-master-server-id --daemonize --log=/tmp/slave-lag.log

范例6： 停止主库上的pt-heartbeat守护进程

pt-heartbeat --stop

2. pt-slave-delay

 功能介绍：

设置从服务器落后于主服务器指定时间。

 用法介绍：

pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]

原理：通过启动和停止复制sql线程来设置从落后于主指定时间。默认是基于从上relay日志的二进制日志的位置来判断，因此不需要连接到主服务器，如果IO进程不落后主服务器太多的话，这个检查方式工作很好，如果网络通畅的话，一般IO线程落后主通常都是毫秒级别。一般是通过--delay and --delay"+"--interval来控制。--interval是指定检查是否启动或者停止从上sql线程的频繁度，默认的是1分钟检查一次。

 使用示例：

范例1：使从落后主1分钟，并每隔1分钟检测一次，运行10分钟 pt-slave-delay --user=root --password=zhang@123 --delay 1m --run-time 10m --host=192.168.3.92 如果不加--run-time参数会一直执行。 范例2：使从落后主1分钟，并每隔15秒钟检测一次，运行10分钟 pt-slave-delay --user=root --password=zhang@123 --delay 1m --interval 15s --run-time 10m --host=192.168.3.92 运行结果如下： 2012-05-20T16:34:50 slave running 0 seconds behind 2012-05-20T16:34:50 STOP SLAVE until 2012-05-20T16:35:50 at master position mysql-bin.000032/4392054 2012-05-20T16:35:05 slave stopped at master position mysql-bin.000032/4397124 2012-05-20T16:35:20 slave stopped at master position mysql-bin.000032/4402194 2012-05-20T16:35:35 slave stopped at master position mysql-bin.000032/4407264 2012-05-20T16:35:50 no new binlog events 2012-05-20T16:36:05 START SLAVE until master 2012-05-20T16:35:05 mysql-bin.000032/4397124

3. pt-slave-find

 功能介绍：

查找和打印mysql所有从服务器复制层级关系

 用法介绍： pt-slave-find [OPTION...] MASTER-HOST 原理:连接mysql主服务器并查找其所有的从，然后打印出所有从服务器的层级关系。

 使用示例：

范例1：查找主服务器为192.168.3.135的mysql有所有从的层级关系： pt-slave-find --user=root --password=zhang@123 --host=192.168.3.135

4. pt-slave-restart

 功能介绍：

监视mysql复制错误，并尝试重启mysql复制当复制停止的时候

 用法介绍： pt-slave-restart [OPTION...] [DSN] 监视一个或者多个mysql复制错误，当从停止的时候尝试重新启动复制。你可以指定跳过的错误并运行从到指定的日志位置。

 使用示例：

范例1：监视192.168.3.92的从，跳过1个错误 pt-slave-restart --user=root --password=zhang@123 --host=192.168.3.92 --skip-count=1 范例2：监视192.168.3.92的从，跳过错误代码为1062的错误。 pt-slave-restart --user=root --password=zhang@123 --host=192.168.3.92 --error-numbers=1062

5. pt-table-checksum

 功能介绍：

检查mysql复制一致性

 用法介绍： pt-table-checksum [OPTION...] [DSN] 工作原理：pt-table-checksum在主上执行检查语句在线检查mysql复制的一致性，生成replace语句，然后通过复制传递到从，再通过update更新master_src的值。通过检测从上this_src和master_src的值从而判断复制是否一致。

注意：使用的时候选择业务地峰的时候运行，因为运行的时候会造成表的部分记录锁定。使用--max-load来指定最大的负载情况，如果达到那个负载这个暂停运行。如果发现有不一致的数据，可以使用pt-table-sync工具来修复。 注意：和1.0版本不同，新版本的pt-table-checksum只需要在master上执行即可。 通过 –explain参数再结合二进制日志就可以看出脚本的工作原理，如我的test库有一个名字为zhang的表，我们通过抓取二进制日志来查看脚本的原理： REPLACE INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'test', 'zhang', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`zhang` /*checksum table*/; UPDATE `test`.`checksums` SET chunk_time = '0.000563', master_crc = '31012777', master_cnt = '4' WHERE db = 'test' AND tbl = 'zhang' AND chunk = '1' 从这里可以很明显的看出原理了，前面已经说了，这里就不赘述了。

 使用示例：

范例1：比较test数据库同步是否一致，结果显示所有的表。 pt-table-checksum --nocheck-replication-filters --databases=test --re

plicate=test.checksums --create-replicate-table --host=192.168.3.135 --port 3306 -uroot -pzhang@123 参数说明：第一次运行的时候需要添加--create-replicate-table参数，如果不加这个就需要手工运行添加表结构的SQL,表结构SQL如下： CREATE TABLE checksums ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB; 之所以使用--nocheck-replication-filters参数是因为我的my.cnf配置了replicate-ignore-db和replicate-wild-do-table等参数。另外需要特别注意执行的checksums所在的数据库必须是同步的数据库。我刚开始使用的时候摸索的很久，官网也没有范例。呵呵！ 结果如下： TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 05-23T16:19:29 0 1 2 1 0 0.006 test.aaa 05-23T16:19:29 0 0 1 1 0 0.017 test.bbb 05-23T16:19:29 0 0 0 1 0 0.007 test.category_part 05-23T16:19:31 0 0 233617 6 0 1.887 test.collect_data 05-23T16:19:34 0 0 250346 5 0 2.709 test.effective_user 05-23T16:19:34 0 1 1 1 0 0.008 test.heartbeat 05-23T16:19:39 0 0 1000000 11 0 5.353 test.oss_pvinfo2 从结果中，我们可以看到test.aaa和test.heartbeat表的DIFFS不为0，那么就是这两个表不同步了。

范例2：比较test数据库同步是否一致，结果只显示数据不一致的表（添加--replicate-check-only参数即可）。 pt-table-checksum --nocheck-replication-filters --databases=test --replicate=test.checksums --replicate-check-only --lock-wait-timeout=120 --host=192.168.3.135 --port 3306 --user=root --password=zhang@123 结果如下： Differences on localhost.localdomain

TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY test.aaa 1 1 1 test.heartbeat 1 0 1 从结果可以看出，只显示了两个不同步的表。

6. pt-table-sync

 功能介绍：

高效同步mysql表的数据

 用法介绍： pt-table-sync [OPTION...] DSN [DSN...] 原理：总是在主上执行数据的更改，再同步到从上，不会直接更改成从的数据，在主上执行更改是基于主上现在的数据，不会更改主上的数据。注意使用之前先备份你的数据，避免造成数据的丢失.执行execute之前最好先换成--print或--dry-run查看一下会变更哪些数据。

 使用示例：

范例1：同步3.135的test库的aaa表到192.168.3.92，在执行之前可以用--execute参数换成--print来查看会变更什么东西，后面那个主机必须是master，否则会报错推出。 pt-table-sync --execute --user=root --password=zhang@123 h=192.168.3.135,D=test,t=aaa h=192.168.3.92

范例2：将主的test数据库同步到192.168.3.92，使从上具有一样的数据。 pt-table-sync --execute --sync-to-master --user=root --password=zhang@123 h=192.168.3.92 --database test

范例3：只同步指定的表 pt-table-sync --execute --sync-to-master --user=root --password=zhang@123 h=192.168.3.92 D=test,t=aaa

范例4：根据pt-table-checksum的结果进行数据同步 pt-table-sync --execute --replicate test.checksums --user=root --password=zhang@123 h=192.168.3.135

范例5：根据pt-table-checksum使从的数据和主的数据一致 pt-table-sync --execute --replicate test.checksums --user=root --password=zhang@123 --sync-to-master h=192.168.3.92 D=test,t=aaa

(六) 系统类工具

1. pt-diskstats

 功能介绍：

是一个对GUN/LINUX的交互式监控工具

 用法介绍： pt-diskstats [OPTION...] [FILES] 为GUN/LINUX打印磁盘io统计信息，和iostat有点像，但是这个工具是交互式并且比iostat更详细。可以分析从远程机器收集的数据。

 使用示例：

范例1：查看本机所有的磁盘的状态情况： pt-diskstats 范例2：只查看本机sda2磁盘的状态情况 pt-diskstats --devices-regex sda2

2. pt-fifo-split

 功能介绍：

模拟切割文件并通过管道传递给先入先出队列而不用真正的切割文件

 用法介绍： pt-fifo-split [options] [FILE ...]

pt-fifo-split读取大文件中的数据并打印到fifo文件，每次达到指定行数就往fifo文件中打印一个EOF字符，读取完成以后，关闭掉fifo文件并移走，然后重建fifo文件，打印更多的行。这样可以保证你每次读取的时候都能读取到制定的行数直到读取完成。注意此工具只能工作在类unix操作系统。这个程序对大文件的数据导入数据库非常有用，具体的可以查看http://www.mysqlperformanceblog.com/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/。

 使用示例：

范例1：一个每次读取一百万行记录的范例： pt-fifo-split --lines 1000000 hugefile.txt while [ -e /tmp/pt-fifo-split ]; do cat /tmp/pt-fifo-split; done 范例2：一个每次读取一百万行，指定fifo文件为/tmp/my-fifo，并使用load data命令导入到mysql中： pt-fifo-split infile.txt --fifo /tmp/my-fifo --lines 1000000 while [ -e /tmp/my-fifo ]; do mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile '/tmp/my-fifo' into table load_test fields terminated by ' ' lines terminated by ' ' (col1, col2);" sleep 1; done

3. pt-summary

 功能介绍：

友好地收集和显示系统信息概况，此工具并不是一个调优或者诊断工具，这个工具会产生一个很容易进行比较和发送邮件的报告。

 用法介绍： pt-summary 原理：此工具会运行和多命令去收集系统状态和配置信息，先保存到临时目录的文件中去，然后运行一些unix命令对这些结果做格式化，最好是用root用户或者有权限的用户运行此命令。

 使用示例：

范例1：查看本地系统信息概况 pt-summary

4. pt-stalk

 功能介绍：

出现问题的时候收集mysql的用于诊断的数据

 用法介绍： pt-stalk [OPTIONS] [-- MYSQL OPTIONS] pt-stalk等待触发条件触发，然后收集数据帮助错误诊断，它被设计成使用root权限运行的守护进程，因此你可以诊断那些你不能直接观察的间歇性问题。默认的诊断触发条件为SHOW GLOBAL STATUS。也可以指定processlist为诊断触发条件 ，使用--function参数指定。

 使用示例：

范例1：指定诊断触发条件为status，同时运行语句超过20的时候触发，收集的数据存放在/tmp/test目录下： pt-stalk --function status --variable Threads_running --threshold 20 --dest /tmp/test -- -uroot -pzhang@123 -h192.168.3.135 范例2：指定诊断触发条件为processlist，超过20个状态为statistics触发，收集的数据存放在/tmp/test目录下： pt-stalk --function processlist --variable State --match statistics --threshold 20 --dest /tmp/test -- -uroot -pzhang@123 -h192.168.3.135 贴一下达到触发条件以后收集的信息： 2012_06_04_17_31_49-df 2012_06_04_17_31_49-disk-space 2012_06_04_17_31_49-diskstats 2012_06_04_17_31_49-hostname 2012_06_04_17_31_49-innodbstatus1 2012_06_04_17_31_49-innodbstatus2 2012_06_04_17_31_49-interrupts 2012_06_04_17_31_49-log_error 2012_06_04_17_31_49-lsof 2012_06_04_17_31_49-meminfo 2012_06_04_17_31_49-mutex-status1 2012_06_04_17_31_49-mysqladmin 2012_06_04_17_31_49-netstat 2012_06_04_17_31_49-netstat_s 2012_06_04_17_31_49-opentables1 2012_06_04_17_31_49-opentables2 2012_06_04_17_31_49-output 2012_06_04_17_31_49-pmap 2012_06_04_17_31_49-processlist 2012_06_04_17_31_49-procstat

2012_06_04_17_31_49-procvmstat 2012_06_04_17_31_49-ps 2012_06_04_17_31_49-slabinfo 2012_06_04_17_31_49-sysctl 2012_06_04_17_31_49-top 2012_06_04_17_31_49-trigger 2012_06_04_17_31_49-variables 2012_06_04_17_31_49-vmstat 2012_06_04_17_31_49-vmstat-overall

(七) 实用类工具

1. pt-archiver

 功能介绍：

将mysql数据库中表的记录归档到另外一个表或者文件，也可以直接进行记录的删除操作。

 用法介绍： pt-archiver [OPTION...] --source DSN --where WHERE 这个工具只是归档旧的数据，不会对线上数据的OLTP查询造成太大影响，你可以将数据插入另外一台服务器的其他表中，也可以写入到一个文件中，方便使用load data infile命令导入数据。另外你还可以用它来执行delete操作。这个工具默认的会删除源中的数据。使用的时候请注意。

 使用示例：

范例1：将192.168.3.135上的sanmao库的oss_log表id小于100000的记录转移到192.168.3.92上的sanmao库，并归档到oss_log_archive_20120605.log文件中: pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --dest h=192.168.3.92,D=sanmao,t=oss_log --file '/var/log/oss_log_archive_20120605.log' --where "id<=100000" --commit-each 范例2：将192.168.3.135上的sanmao库的oss_log小于160000的记录归档到oss_log_archive_20120607.log文件中: pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --file '/var/log/oss_log_archive_20120607.log' --where "id<=160000" --commit-each 范例3：删除192.168.3.135上的sanmao库的oss_log表中id小于167050的记录： pt-archiver --source h=192.168.3.135,D=sanmao,t=oss_log --user=root --password=zhang@123 --purge --where 'id<=167050' 注意：如果是字符集是utf8的话，需要在my.cnf中的[client]下面添加default-character-set = utf8，否则导出的文件内容中文会乱码。

2. pt-find

 功能介绍：

查找mysql表并执行指定的命令，和gnu的find命令类似。

 用法介绍： pt-find [OPTION...] [DATABASE...] 默认动作是打印数据库名和表名

 使用示例：

范例1：查找192.168.3.135中1天以前创建的InnoDB的表 ,并打印。 pt-find --ctime +1 --host=192.168.3.135 --engine InnoDB --user=root --password=zhang@123 范例2：查找192.168.3.135中1天以前更改过的数据库名字匹配%hostsops%的并且引擎为MYISAM的表，并将表的引擎更改为InnoDB引擎。 pt-find --mtime +1 --dblike hostsops --engine MyISAM --host=192.168.3.135 --user=root --password=zhang@123 --exec "ALTER TABLE %D.%N ENGINE=InnoDB" 范例3：查找192.168.3.135中aaa库和zhang库中的空表，并删除。 pt-find --empty aaa zhang --host=192.168.3.135 --user=root --password=zhang@123 --exec-plus "DROP TABLE %s" 范例4：查找192.168.3.135中超过100M的表： pt-find --tablesize +100M --host=192.168.3.135 --user=root --password=zhang@123

3. pt-kill

 功能介绍：

Kill掉符合指定条件mysql语句

 用法介绍： pt-kill [OPTIONS] 假如没有指定文件的话pt-kill连接到mysql并通过SHOW PROCESSLIST找到指定的语句，反之pt-kill从包含SHOW PROCESSLIST结果的文件中读取mysql语句

 使用示例：

范例1：查找192.168.3.135服务器运行时间超过60s的语句，并打印 pt-kill --busy-time 60 --print --host=192.168.3.135 --user=root --password=zhang@123 范例2：查找192.168.3.135服务器运行时间超过60s的语句，并kill pt-kill --busy-time 60 --kill --host=192.168.3.135 --user=root --password=zhang@123 范例3：从proccesslist文件中查找执行时间超过60s的语句 mysql -uroot -pzhang@123 -h192.168.3.135 -e "show processlist" > processlist.txt pt-kill --test-matching processlist.txt --busy-time 60 --print