master 10.0.254.211 
slave 10.0.254.212
数据库:test_pt
用户名、密码:pt
权限:
DROP USER 'pt';DROP USER 'pt';
CREATE USER 'pt'@'%' IDENTIFIED BY 'pt';
CREATE USER 'pt'@'localhost' IDENTIFIED BY 'pt';
GRANT ALL ON *.* TO 'pt'@'%';
GRANT ALL ON *.* TO 'pt'@'localhost';
FLUSH PRIVILEGES;

调试：在所有命令前加 PTDEBUG=1 ，则会输出命令执行过程中的debug日志

pt-heartbeat

#  h=10.0.254.211,u=pt,p=pt,P=3306 等价于 --host=10.0.254.211 --user=pt --password=pt --port=3306

-- master start update
pt-heartbeat h=10.0.254.211,u=pt,p=pt,P=3306 -D test_pt --create-table --update --daemonize

-- slave do check。只比较一次,适用于二次开发,报警
pt-heartbeat h=10.0.254.212,u=pt,p=pt,P=3306 -D test_pt --check

-- slave start monitor,每隔interval时长比较一次
pt-heartbeat h=10.0.254.212,u=pt,p=pt,P=3306 -D test_pt --monitor

# 停止pt-heartbeat
-- stop
pt-heartbeat h=10.0.254.211,u=pt,p=pt,P=3306 --stop --update
pt-heartbeat h=10.0.254.212,u=pt,p=pt,P=3306 --stop --monitor
注意: 如果没有指定update|monitor|check，则会生成sentinel文件(默认/tmp/pt-heartbeat-sentinel)，会阻止pt-hearbeat以daemonize方式运行

pt-kill

 # 每10s检查一次, kill非root用户状态属于(Query|Execute)的执行时间超过5分钟的query,并不kill连接，日志输出log-dsn指定的表中
 pt-kill h=10.0.254.211,u=pt,p=pt,P=3306 --interval 10 --busy-time 5m --kill-query --ignore-user=root \
    --log-dsn=h=10.0.254.211,u=pt,p=pt,P=3306,D=test_pt,t=kill_log --match-command \(Query\|Execute\) --daemonize

pt-online-schema-change
        pt-online-schema-change h=10.0.254.212,u=pt,p=pt,P=3309,D=test,t=t1 --alter-foreign-keys-method auto --alter "add column c2 int" --nodrop-old-table --print  --execute
        # DDL中不需要包含alter table xx 内容
同步d1库的ttb表，下面3种方式操作效果是相同的，不会检查复制过滤配置
# 根据3308(SLAVE)查找master(3307,工具自己查找)，比较差异，然后输出
pt-table-sync --sync-to-master h=127.0.0.1,u=pt,p=pt,P=3308,D=d1,t=ttb --print 
pt-table-sync --sync-to-master --replicate=test_pt.checksums  h=127.0.0.1,u=pt,p=pt,P=3308,D=d1,t=ttb --print 
# 比较3307与3308的差异，并打印
pt-table-sync --replicate=test_pt.checksums h=127.0.0.1,u=pt,p=pt,P=3307,D=d1,t=ttb h=127.0.0.1,u=pt,p=pt,P=3308,D=d1,t=ttb --print 

# 真正进行同步时将 --print  换为 --execute 即可
if DSN has a t part, sync only that table:
   if 1 DSN:
      if --sync-to-master:
         The DSN is a slave.  Connect to its master and sync.
   if more than 1 DSN:
      The first DSN is the source.  Sync each DSN in turn.
else if --replicate:
   if --sync-to-master:
      The DSN is a slave.  Connect to its master, find records
      of differences, and fix.
   else:
      The DSN is the master.  Find slaves and connect to each,
      find records of differences, and fix.
else:
   if only 1 DSN and --sync-to-master:
      The DSN is a slave.  Connect to its master, find tables and
      filter with --databases etc, and sync each table to the master.
   else:
      find tables, filtering with --databases etc, and sync each
      DSN to the first.
      

pt-query-digest

参数默认值
--group-by fingerprint 
--order-by Query_time:sum
--limit 95%:20. 设定比例或数量

pt-query-digest slow.log

# 450ms user time, 20ms system time, 22.37M rss, 175.84M vsz

# Hostname: XXXXXXXXX

# Files: slow.log

# Overall: 712 total, 44 unique, 0.00 QPS, 0.00x concurrency _____________

# Time range: 2013-11-01 05:02:10 to 2014-02-12 05:14:07

# Profile

# Rank Query ID           Response time  Calls R/Call  V/M   Item

# ==== ================== ============== ===== ======= ===== =============

#    1 0x9BAA051B20C73D0A 757.1584 18.1%   154  4.9166  0.08 具体sql

#    2 0xDBFAB1DBA39455C1 376.6429  9.0%    79  4.7676  1.26 具体sql

#   ..........................

#   13 0xC4E13AFAD3A6C8B3  79.1628  1.9%    20  3.9581  1.38 具体sql

Query 1: 0.00 QPS, 0.00x concurrency, ID 0x9BAA051B20C73D0A at byte 73666(慢查询日志中的偏移量)

# This item is included in the report because it matches --limit.

# Scores: V/M = 0.08

# Time range: 2013-11-29 05:00:05 to 2014-02-12 05:14:07

# Attribute    pct   total     min     max     avg     95%  stddev  median

# ============ === ======= ======= ======= ======= ======= ======= =======

# Count         21     154

# Exec time     18    757s      4s      9s      5s      5s   646ms      5s

# Lock time      0    10ms    50us   120us    66us   103us    15us    60us

# Rows sent      0     154       1       1       1       1       0       1

# Rows examine  77 350.23M   1.64M   2.72M   2.27M   2.62M 306.61k   2.26M

# Query size    50  44.67k     297     297     297     297       0     297

# Query_time distribution

#   1us

#  10us

# 100us

#   1ms

#  10ms

# 100ms

#    1s  ################################################################

#  10s+

#  具体sql

# 查看具体sql在慢查询日志中的详细内容

tail -c + slow.log | head

          # review
          pt-query-digest --review h=10.0.254.212,u=pt,p=pt,P=3306,D=test_pt,t=pt_query_review
             --history h=10.0.254.212,u=pt,p=pt,D=test_pt,t=pt_query_review_history
            --progress time,5 --group-by fingerprint --type slowlog --limit 100% --filter '过滤规则' slow.log

-- filter 使用
$event->{arg} 语句分类: 

SELECT statements:

--filter '$event->{arg} =~ m/^select/i'

INSERT/UPDATE/DELETE statements:

--filter '$event->{arg} =~ m/^(insert|update|delete)/i'

call statements:

--filter '$event->{arg} =~ m/^call/i'

other:

--filter '$event->{arg} !~ m/^(select|insert|update|delete|call)/i'

Host/IP matches domain.com

–filter '($event->{host} || $event->{ip} || "") =~ m/domain.com/' 

User matches john

–filter '($event->{user} || "") =~ m/john/'

More than 1 warning

–filter '($event->{Warning_count} || 0) > 1'

Query does full table scan or full join

–filter '(($event->{Full_scan} || "") eq  "Yes" ) || (($event->{Full_join} || "") eq  "Yes" )'

Query was not served from query cache

–filter '($event->{QC_Hit} || "") eq  "No" '

Query is 1 MB or larger

–filter '$event->{bytes} >= 1048576'

            
          # review-history分析sql

SET @begindt:='2013-1-1',@enddt:='2014-2-1';
SELECT his.`checksum` AS 'checksum'

  , LEFT(his.sample,40) AS 'snippet'

  , ROUND(SUM(`Rows_examined_sum`)/SUM(`Rows_sent_sum`),2) AS 'index_ratio'

  , ROUND(SUM(`Query_time_sum`)/SUM(ts_cnt),2) AS 'query_time_avg'

  , ROUND(SUM(`Rows_sent_sum`)/SUM(ts_cnt),2) AS 'rows_sent_avg'

  , ROUND(SUM(Query_time_sum)/SUM(ts_cnt),2) AS 'Query_time_sum'

  , ROUND(SUM(`Lock_time_sum`),2) AS 'Lock_time_sum'

  , SUM(ts_cnt) AS 'ts_cnt'

  , SUM(`Rows_sent_sum`) AS 'Rows_sent_sum'

  , SUM(`Rows_examined_sum`) AS 'Rows_examined_sum'

FROM `pt_query_review` AS fact 
INNER JOIN `pt_query_review_history` AS his ON fact.checksum=his.checksum
WHERE his.ts_min BETWEEN @begindt AND @enddt
GROUP BY `checksum`
ORDER BY Query_time_sum DESC
LIMIT 100;

pt-stalk
    http://blog.itpub.net/26250550/viewspace-2059636/