五分钟学会Percona Toolkit 安装及使用

pt(Percona Toolkit)工具是由Percona公司开发的一个用perl语言编写的工具集,包含很多功能,例如在线更改数据表结构,校验主从数据,检查数据库状态,分析慢查询等。依靠这些工具可以帮助DBA更简单的维护及优化MySQL数据库。

01

安装

1.1 下载或拷贝安装包

官方地址：

https://www.percona.com/downloads/percona-toolkit

本次直接从其他服务器拷贝

 scp root@106.14.184.47:/test/percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar  /tmp/percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar
 tar -xvf percona-toolkit-3.0.4-r2e44c3a-el6-x86_64-bundle.tar

1.2 安装依赖包

yum install perl-DBI
yum install perl-DBD-MySQL 
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-TermReadKey

1.3 安装 Percona Toolkit

rpm -ivh percona-toolkit-3.0.4-1.el6.x86_64.rpm

1.4 测试是否安装成功

 pt-query-digest --help

02

主要工具使用介绍

percona_tools重点使用的是pt-online-schema-change, pt-heartbeat,pt-table-checksum ，pt-table-sync 这几个工具：

2.1  pt-online-schema-change

添加字段：

pt-online-schema-change --alter "ADD COLUMN  c1 int" h=106.14.184.46,P=3306,p=123456,u=root,D=test,t=percona_tools --execute

删除字段：

pt-online-schema-change --alter "drop column c1" h=106.14.184.46,P=3306,p=123456,u=root,D=test,t=percona_tools --execute

添加索引：

 pt-online-schema-change --alter "add  key name(name)" h=106.14.184.46,P=3306,p=123456,u=root,D=test,t=percona_tools --execute

修改字段长度：

pt-online-schema-change  --alter "modify symbol varchar(10)" h=106.14.184.46,P=3306,p=123456,u=root,D=test,t=IDX_MARKETVALUE --execute

修改表引擎：

pt-online-schema-change  --alter "ENGINE=InnoDB" h=106.14.184.46,P=3306,p=123456,u=root,D=test,t=T_MyIsam --execute

工作原理：

1） 创建一个和你要执行 alter 操作的表一样的空表结构  

2） 执行表结构修改，然后从原表中的数据到copy到 表结构修改后的表 

3） 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作 更新到新表

4） copy 完成以后，用rename table 新表代替原表，默认删除原表。

注:

修改的表必须有主键,另外也建议加上字符集选项；  

如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行；  

如果表中已经定义了触发器则这个工具就不能工作了

用法介绍：

pt-online-schema-change [OPTIONS] DSN

options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：

--dry-run  这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute 这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

2.2 pt-heartbeat

1)首先要在主服务器下创建一个定期更新heartbeat表

pt-heartbeat -D test1 --update -u root -p 123456 -P 3346 -h 106.14.184.46 --create-table --daemonize

2)监控复制在slave上的落后程度

pt-heartbeat -D test --monitor -u root -p 123456 -P 3306 -h 106.14.184.46

3）停止监控

pt-heartbeat --stop

2.3 pt-table-checksum

pt-table-checksum  --host=106.14.184.46 --port 3306 --databases=test1 -uroot -p123456   --no-check-binlog-format

注：如执行报错：

Diffs cannot be detected because no slaveswere found.  Please read the--recursion-method documentation for information

从库配置文件 my.cnf中添加

report_host=slave_ip
report_port=slave_port

[root@mysql2 ~]# pt-table-checksum  --host=106.14.184.46 --port 3346 --databases=test1 -uroot -p123456   --no-check-binlog-format
Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
# A software update is available:
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-14T00:05:20      0      0     9759       4       0   0.742 test1.COST_RISKFREE
09-14T00:05:20      0      0    17862       1       0   0.349 test1.FFUT_QUOTATION
09-14T00:05:27      0      0   461012      12       0   6.714 test1.STK_MKT_QUOTATION
09-14T00:05:27      0      0       20       1       0   0.177 test1.e
09-14T00:05:27      0      0        1       1       0   0.171 test1.heartbeat
09-14T00:05:28      0      0        1       1       0   0.175 test1.test_int
You have new mail in /var/spool/mail/root

选项简介：

TS            ：完成检查的时间。
ERRORS        ：检查时候发生错误和警告的数量。
DIFFS         ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS          ：表的行数。
CHUNKS        ：被划分到表中的块的数目。
SKIPPED       ：由于错误或警告或过大，则跳过块的数目。
TIME          ：执行的时间。
TABLE         ：被检查的表名。

2.4  pt-table-sync

主库为 106.14.184.46 从库为 106.14.184.47

测试删除一条记录 然后检查

SELECT  MAX(updateid) FROM test1.COST_RISKFREE
DELETE  FROM  COST_RISKFREE WHERE  updateid=51453

1) 先校验

PTDEBUG=1 pt-table-checksum --user=root --password=123456 --host=106.14.184.46 --port=3346 --databases=test1 --tables=COST_RISKFREE --recursion-method=processlist

2) 根据校验结果修复从库数据

PTDEBUG=1  pt-table-sync --execute  --databases=test1  --tables=COST_RISKFREE   --replicate percona.checksums --sync-to-master  h=106.14.184.46,u=root,p=123456
# TableChecksum:4998 5214 SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
# TableChecksum:4998 5214 SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
# SchemaIterator:7923 5214 Table heartbeat is not in --tables list, ignoring
# pt_table_sync:10262 5214 No checksum differences
# pt_table_sync:11128 5214 Disconnected dbh DBI::db=HASH(0x2c229e0)
# pt_table_sync:11128 5214 Disconnected dbh DBI::db=HASH(0x25c18e0)
# pt_table_sync:11128 5214 Disconnected dbh DBI::db=HASH(0x25c1e80)
# pt_table_sync:11128 5214 Disconnected dbh DBI::db=HASH(0x25c1610)

 --replicate=:指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。

 --databases=:指定执行同步的数据库，多个用逗号隔开。 

--tables=:指定执行同步的表，多个用逗号隔开。 

--sync-to-master:指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。h=127.0.0.1  

--host=:服务器地址，命令里有2个ip，第一次出现的是M的地址，第2次是Slave的地址。 

--user=:帐号。    

--password:密码。 

--print:打印，但不执行命令。 

--execute:执行命令。

2.5 pt_query_digest

1)直接分析

pt-query-digest  slowlog20170925 > slow_report.log

2)分析最近12小时内的查询

pt-query-digest  --since=12h  slow.log > slow_report2.log

3)分析指定时间范围内的查询

pt-query-digest  /home/mysql_data/DBD_NOD05-slow.log --since '2017-09-24 09:30:00' --until '2017-09-25 14:00:00' > slow_report3.log

4)分析指含有select语句的慢查询

pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

5) 针对某个用户的慢查询

pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

6) 查询所有所有的全表扫描或full join的慢查询

pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' gjc-slow.log> slow_report6.log

7)把查询保存到query_review表

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log

8)把查询保存到query_history表

pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_ history--create-review-table  slow.log_20180401
pt-query-digest  --user=root –password=abc123--review  h=localhost,D=test,t=query_history--create-review-table  slow.log_20180402

9)通过tcpdump抓取mysql的tcp协议数据，然后再分析

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3346 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

10)分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

11)分析general log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

2.6 pt-diskstats 查看系统磁盘状态

1) 查看本机所有的磁盘的状态情况：

pt-diskstats

2) 查看指定磁盘状态

pt-diskstats --devices-regex  vda

往期精彩回顾

1.  MySQL不停地自动重启怎么办

2.  升级python，就是这么简单

3.  mysql8.0新增用户及加密规则修改的那些事

4.  Postgresql部署及简单操作

5.  比hive快10倍的大数据查询利器-- presto

6.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

7.  PostgreSQL主从复制--物理复制

8.  MySQL传统点位复制在线转为GTID模式复制

武

汉

加

油