mysql瑞士军刀–pt工具

桃扇骨 2022-02-22 16:06 250阅读 0赞

**Percona-Toolkits**

1.  Percona-toolkit 简介

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

检查master和slave数据的一致性

有效地对记录进行归档

查找重复的索引

对服务器信息进行汇总

分析来自日志和tcpdump的查询

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

根据工具类型划分：开发、性能、配置、监控、复制、系统、实用 这7种类型。

可谓是  mysql 的 瑞士军刀~~

二．**percona-toolkit**的使用

（一**）****. **开发类工具

1.  pt-duplicate-key-checker

功能：

检查mysql中 冗余的索引。

用法：

pt-duplicate-key-checker –user=root  –password=xxxx  –host=localhost  –socket=/tmp/mysql.sock

1.  pt-online-schema-change

功能：

不锁表的情况下,修改表结构.

实现原理：

创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。

用法：

添加索引

pt-online-schema-change -u root -h 10.250.7.50  -p yang –critical-load=”Threads\_running=200″ –alter=’add key indx\_vid(vid) ‘ –execute D=houyi,t=ga

删除字段

pt-online-schema-change -u root -h 10.250.7.50  -p yang –alter=’drop  column vid ‘ –execute D=houyi,t=ga

远程加索引

pt-online-schema-change -usjh -P3306 -h192.168.11.70 -psjh –alter=’add key idx\_c1\_c2(c1,c2)’ –execute D=sjh,t=yw

3．       pt-show-grants

功能：导出mysql.user权限。

工具执行流程：

1、先查找所有用户和Host

2、然后逐个执行show grants

用法：

pt-show-grants -u pt -p 213456 -S /tmp/mysql.sock

打印：

— Grants dumped by pt-show-grants

— Dumped from server Localhost via UNIX socket, MySQL 5.5.36-MariaDB-log at 2015-07-10 13:26:17

— Grants for ‘canal’@’192.168.%’

GRANT REPLICATION CLIENT, REPLICATION SLAVE, SELECT ON \*.\* TO ‘canal’@’192.168.%’ IDENTIFIED BY PASSWORD ‘\*219FB8C1D88632A46DF3F9C8039A14BB75051ECF';

— Grants for ‘hc\_newRO’@’192.168.1.28′

GRANT USAGE ON \*.\* TO ‘hc\_newRO’@’192.168.1.28′ IDENTIFIED BY PASSWORD ‘\*8A66FB579D98E9605C30D86CEF69B569251E5F08′;

GRANT SELECT ON \`voice\`.\* TO ‘hc\_newRO’@’192.168.1.28′;

1.  pt-upgrade

功能：

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

用法:

只查看某个sql在两个服务器的运行结果范例：

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123 –query=”select \* from user\_data.collect\_data limit 5″

查看文件中的对应sql在两个服务器的运行结果范例：

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123  aaa.sql

查看慢查询中的对应的查询SQL在两个服务器的运行结果范例：

pt-upgrade h=’localhost’ h=192.168.8.92 –user=root –password=zhang@123  slow.log

此外还可以执行compare的类型，主要包含三个query\_times,results,warnings，比如下面的例子，只比较sql的执行时间

pt-upgrade h=192.168.3.91 h=192.168.8.92 –user=root –password=zhang@123 –query=”select \* from user\_data.collect\_data” –compare query\_times

**(**二**) **性能类工具

1.  pt-index-usage

功能：

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

用法：

从满查询中的sql查看索引使用情况范例：

pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=root

将分析结果保存到数据库范例：

pt-index-usage /data/dbdata/localhost-slow.log –host=localhost –user=root –password=zhang@123 –no-report –create-save-results-database

1.  pt-pmp

功能：

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

用法：

pt-pmp -p 21933

pt-pmp -b /usr/local/mysql/bin/mysqld\_safe

1.  pt-visual-explain

功能：

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

用法：

mysql -uroot -pzhang@123  -e “explain select email from test.collect\_data where id=101992419″ |pt-visual-explain

** **

**(**三**)         **配置类工具

1.  pt-config-diff

功能：

对比配置文件中不一致的地方。

用法：

pt-config-diff h=192.168.8.85 h=192.168.8.22 -uwwg -pwwg

8 config differences

Variable                  dev-bbs-db-8-85.sh.hupu.com soccer-web-8-22-dev.sh.h

========================= ========================= =========================

back\_log                  4000                      50

basedir                   /usr/local/webserver/D… /usr/local/mysql

big\_tables                ON                        OFF

binlog\_format             ROW                       MIXED

bulk\_insert\_buffer\_size   67108864                  33554432

datadir                   /data/db-data/hupu.com… /data/mysql/mysql\_3306…

expire\_logs\_days          60                        10

general\_log               ON                        OFF

1.  pt-mysql-summary

功能：

精细地对mysql的配置和sataus信息进行汇总。

用法：

pt-mysql-summary — –user=root –password=root –host=localhost

3．  pt-variable-advisor

功能：

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

用法:

pt-variable-advisor –user=root –password=root  localhost

**(**四**) **监控类工具

1.  pt-deadlock-logger

功能：

打印死锁信息。

核心参数 :

–daemonize  ： 守护进程

–create-dest-table ：创建指定的表。

–dest              ：创建存储死锁信息的表。

–database          ：-D，指定链接的数据库。

–table             ：-t，指定存储的表名。

–log               ：指定死锁日志信息写入到文件。

–run-time          ：运行次数，默认永久

–interval          ：运行间隔时间，默认30s。

u，p，h，P           ：链接数据库的信息。

用法：

把本地死锁信息永久保存在 远端 test.deadlock222的表里

pt-deadlock-logger –user=wwg –password=wwg –host=192.168.8.22 –interval=3  –create-dest-table –dest u=wwg,p=wwg,h=192.168.8.85,D=test,t=deadlock222

(root@localhost)\[test\]> select \* from deadlock222;

\+————–+———————+——–+——–+———-+——+———–+—-+—–+——-+—————–+———–+———–+———–+——–+———————————————–+

| 192.168.8.22 | 2015-07-10 15:18:30 |     38 |      0 |       25 | root | localhost |    | wwg | test3 | GEN\_CLUST\_INDEX | RECORD    | X         | w         |      1 | update test3 set name=’helloworld’ where id>1 |

| 192.168.8.22 | 2015-07-10 15:18:30 |     39 |      0 |       11 | root | localhost |    | wwg | test3 | GEN\_CLUST\_INDEX | RECORD    | X         | w         |      0 | update test3 set name=’www’ where id>1        |

直接打印：

\[root@soccer-web-8-22-dev ~\]\# pt-deadlock-logger -uroot -proot -hlocalhost

server ts thread txn\_id txn\_time user hostname ip db tbl idx lock\_type lock\_mode wait\_hold victim query

localhost 2015-07-10T15:18:30 38 0 25 root localhost  wwg test3 GEN\_CLUST\_INDEX RECORD X w 1 update test3 set name=’helloworld’ where id>1

localhost 2015-07-10T15:18:30 39 0 11 root localhost  wwg test3 GEN\_CLUST\_INDEX RECORD X w 0 update test3 set name=’www’ where id>1

1.  pt-fk-error-logger

功能：

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

用法：

pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign\_key\_errors

写到远端：

pt-fk-error-logger –user=root –password=’root’ h=192.168.1.248 –dest D=test,t=foreign\_key\_errors –user=root –password=root –host=192.168.1.249

1.  pt-mext

功能：

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

用法：

每隔10s执行一次SHOW GLOBAL STATUS，并将结果合并到一起查看

pt-mext -r — mysqladmin -uroot -p’root’ –socket=/tmp/mysql.sock  ext -i10 -c2

\-i  sleep

\-c  迭代多少次

1.  pt-query-digest

功能：

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

用法：

*  分析本地的慢查询文件

pt-query-digest –user=root –password=zhang@123 /data/dbdata/localhost-slow.log

1.  重新回顾满查询日志，并将结果保存到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 );

从远端抓取slowlog

pt-query-digest –user=root –password=zhang@123 –review h=localhost,D=test,t=query\_review /data/dbdata/localhost-slow.log

**(**五**) **复制类工具

1.  pt-heartbeat

用法：

pt-heartbeat \[OPTION…\] \[DSN\] –update|–monitor|–check|–stop

主库上起个守护进程

pt-heartbeat -D test –update –user=root –password=zhang@123 -h192.168.8.22 –create-table –daemonize

从库监控延迟(实时监控)

pt-heartbeat -D test –monitor –user=wwg –password=wwg -h192.168.8.22 –master-server-id=203306

0.00s \[  0.00s,  0.00s,  0.00s \]

只监控一次就退出

pt-heartbeat -D test –check –user=root –password=zhang@123 -h192.168.8.22 –master-server-id=203306

1.  pt-slave-delay

功能：

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

用法：

使从落后主1分钟，并每隔15秒钟检测一次，运行10分钟（不加 run-time 会永久执行）

pt-slave-delay –daemonize –user=root –password=root –delay 1m –interval 15s –run-time 10m –host=192.168.3.92

1.  pt-slave-find

功能：

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

用法：

查找主库为192.168.8.22 所有从库

pt-slave-find –user=wwg –password=wwg –host=192.168.8.22

1.  pt-slave-restart

功能：

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

用法：

跳过错误数：

pt-slave-restart –user=root –password=zhang@123 –host=192.168.3.92 –skip-count=1

跳过错误类型：

pt-slave-restart –user=root –password=zhang@123 –host=192.168.3.92 –error-numbers=1062

1.  pt-table-checksum

功能：

检查mysql复制一致性

核心参数：

–no-check-binlog-format 如果不是row格式 都要带上

–replicate-check-only                 只显示问题的表

用法：

主库上执行：

pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate=sjh.checksums –create-replicate-table -h192.168.1.134 -P 3306 -u wwg -p wwg –recursion-method=”processlist”

TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE

07-26T21:23:38      0      3   700000       6       0   3.320 wubx.t5

如果数据是一致的DIFFS是等于0的，不等于0表示不一致的。

执行结果显示参数意义：

TS            ：完成检查的时间。

ERRORS     ：检查时候发生错误和警告的数量。

DIFFS        ：0表示一致，大于0表示不致。当指定–no-replicate-check时，会一直为0，当指定

–replicate-check-only会显示不同的信息。

ROWS       ：表的行数。

CHUNKS    ：被划分到表中的块的数目。

SKIPPED    ：由于错误或警告或过大，则跳过块的数目。

TIME         ：执行的时间。

TABLE       ：被检查的表名。

1.  pt-table-sync

功能：

高效同步mysql表的数据

用法：

1.打印 ip 为主库ip

pt-table-sync –replicate=sjh.checksums –print –databases=hc\_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg

2.执行

pt-table-sync –replicate=sjh.checksums –execute –databases=hc\_webgame –charset=utf8 h=192.168.1.134,u=wwg,p=wwg

**(**六**) **系统类工具

1.  pt-diskstats

功能：

查看服务器的IO。

用法：

pt-diskstats –interval=5 –show-timestamps  –columns-regex=sdb

1.  pt-fifo-split

功能：

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

用法：

一个每次读取一百万行，指定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 ‘\\t’ lines terminated by ‘\\n’ (col1, col2);” sleep 1; done

1.  pt-summary

功能：

友好地收集和显示系统信息概况

用法：

pt-summary

4．pt-stalk

功能：

在MySQL服务器出现短暂(5~30秒)的性能波动的时候，一般的性能监控工具都很难抓住故障现场，也就很难收集对应较细粒度的诊断信息。另外，如果这种波动出现的频率很低，例如几天才一次，我们也很难人为的抓住现场，收集数据。这正是pt-stalk所解决的问题。

用法：

pt-stalk –collect-tcpdump –function status –variable Threads\_connected –threshold 2 –daemonize — –user=root –password=’root’

查看日志(或 –log 指定日志输出)：

tail -f /var/log/pt-stalk.log

查看生成的信息( –dest 指定目录)：

ls /var/lib/pt-stalk/

=== pt-stalk的触发条件

三种触发条件，通过参数function设置：

status

–function status –variable Threads\_connected –threshold 2500，表示MySQL状态值Threads\_connected超过2500时触发数据收集。常用的触发条件还可以使用Threads\_running等。

processlist

–function processlist –variable State –match statistics –threshold 10，表示，show processlist中State列的值为statistics的线程数超过10则触发收集。

自定义脚本

包含 trg\_plugin函数的shell脚本， trg\_plugin 函数必须返回一个int值，比如下面的脚本，因为slow log的status是一个累加值，不能分析差值，因此写小脚本来获取差值，对slow突然出现的尖刺进行分析。

====其他有用的参数：

–iterations：该参数指定pt-stalk在收集几次故障现场后就退出。默认pt-stalk会一直运行

–run-time：触发收集后，该参数指定收集多长时间的数据。默认是30秒

–sleep：为防止一直触发收集数据，该参数指定在某次触发后，必须sleep一段时候才继续观察并触发收集。默认是300秒

–interval：默认情况pt-stalk会每隔一秒检查一次状态数据，判断是否需要触发收集。该参数指定间隔时间，默认是1秒。

–cycles：默认情况pt-stalk只有连续观察到五次状态值满足触发条件时，才触发收集。该参数控制，需要连续几次满足条件，收集被触发，默认是5次。

pt-sift 打印 pt-stalk输出的文件

1.  pt-ioprofile

功能：

对某个pid附加一个strace进程进行io分析。

用法：

pt-ioprofile –profile-pid=22544 –cell=sizes

先用 iotop -o 排查 什么进程 占用io 更高。

再用 pt-ioprofile  查看 pid 哪些 所属的文件 读写更 频繁。

** **

**(**七**) **实用类工具

*  pt-archiver

功能：

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

用法：

1.  每5000条 删除1次，每隔2000行输出一次 数据(基本不会照成延迟)

pt-archiver –source D=ibilling\_pay\_hupu,t=purchase\_order –user=root –password=’root’ -S /tmp/mysql.sock –where “create\_datetime<=’2015-06-20 00:00:00′” –purge –limit=5000 –progress 2000 –why-quit –no-check-charset > /opt/1.log

1.  用于把数据导出文件，不用删除原表中数据

pt-archiver  –source h=127.0.0.1,P=58886,D=test,t=t\_info\_refresh  –no-check-charset  –where ‘id>1′  –progress 4000  –no-delete –file “/tmp/pt-archiver.sql”  –limit=10000

1.  把数据归档至 归档库(默认保留最后条)。

pt-archiver  –source P=3306,D=wwg,t=yw –user=root –password=root -S /tmp/mysql.sock –dest h=192.168.8.85,P=3306,u=wwg,p=wwg,D=ibilling,t=his\_yw  –no-check-charset  –where ‘id < 5001′   –no-delete  –statistics

1.  pt-find

功能：

查找mysql表并执行指定的命令。

用法:

1.查找192.168.8.22中1天以前创建的InnoDB的表 ,并打印

pt-find –ctime +1  –host=localhost –engine InnoDB –user=root –password=root

\`test\`.\`deadlocks\`

\`wwg\`.\`test3\`

\`wwg\`.\`wwg\_t1\`

2.查找192.168.8.22中1天以前更改过的数据库名字匹配%mysql%的并且引擎为MYISAM的表，并将表的引擎更改为InnoDB引擎。

pt-find –mtime +1 –dblike mysql –engine MyISAM –host=localhost –user=root –password=root –exec “ALTER TABLE %D.%N ENGINE=InnoDB”

3.查找192.168.8.22中wwg库和zhang库中的空表，并删除。