percona-toolkit工具介绍

https://www.percona.com/software/database-tools/percona-toolkit

percona-toolkit工具使用介绍

 

 

percona-toolkit工具使用介绍

• 1. pt-heartbeat
• 1.1 pt-heartbeat 原理
• 1.2 pt-heartbeat 主要参数介绍
• 1.3 pt-heartbeat 实战
• 2. pt-slave-restart
• 2.1 pt-slave-restart 原理
• 2.2 pt-slave-restart 主要参数介绍
• 2.3 pt-slave-restart 实战
• 3. pt-table-checksum
• 3.1 pt-table-checksum 原理
• 3.2 pt-table-checksum 主要参数介绍
• 3.3 pt-table-checksum 实战
• 4. pt-table-sync
• 4.1 pt-table-sync 原理
• 4.2 pt-table-sync 主要参数介绍
• 4.3 pt-table-sync 实战
• 5.pt-query-disgest

 

1. pt-heartbeat

用于监控主从延迟的工具

1.1 pt-heartbeat 原理

1. 主库创建一张heartbeat表，表中有个时间戳字段。主库上pt-heartbeat的update线程会在指定时间间隔更新时间戳。
2. 从库上的pt-heartbeat的monitor线程会检查复制的心跳记录，这个记录就是主库修改的时间戳。然后和当前系统时间进行对比，得出时间上的差异，差异值就是延迟的时间大小。由于heartbeat表中有server_id字段，在监控某个从库的延迟时指定参考主库的server_id即可。

1.2 pt-heartbeat 主要参数介绍

注意：需要指定的参数至少有 --stop，--update，--monitor，--check。
其中--update，--monitor和--check是互斥的，--daemonize和--check也是互斥。

`--ask-pass`:隐式输入MySQL密码

`--charset`:字符集设置

`--check`:检查从的延迟，检查一次就退出，除非指定了--recurse会递归的检查所有的从服务器。

`--check-read-only`:如果从服务器开启了只读模式，该工具会跳过任何插入。

`--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
);
heratbeat表一直在更改ts和position,而ts是我们检查复制延迟的关键。

`--daemonize`:执行时，放入到后台执行

`--user | -u`:连接数据库的帐号

`--database | -D`:连接数据库的名称

`--host|-h`:连接的数据库地址

`--password | -p`:连接数据库的密码

`--port | -P`:连接数据库的端口

`--socket | -S`:连接数据库的套接字文件

`--file 【--file=output.txt】`:打印--monitor最新的记录到指定的文件，很好的防止满屏幕都是数据的烦恼。

`--frames 【--frames=1m,2m,3m】`:在--monitor里输出的[]里的记录段，默认是1m,5m,15m。可以指定1个，如：--frames=1s，多个用逗号隔开。可用单位有秒（s）、分钟（m）、小时（h）、天（d）。

`--interval`:检查、更新的间隔时间。默认是见是1s。最小的单位是0.01s，最大精度为小数点后两位，因此0.015将调整至0.02。

`--log`:开启daemonized模式的所有日志将会被打印到制定的文件中。

`--monitor`:持续监控从的延迟情况。通过--interval指定的间隔时间，打印出从的延迟信息，通过--file则可以把这些信息打印到指定的文件。

`--master-server-id`:指定主的server_id，若没有指定则该工具会连到主上查找其server_id。

`--print-master-server-id`:在--monitor和--check 模式下，指定该参数则打印出主的server_id。

`--recurse`:多级复制的检查深度。模式M-S-S...不是最后的一个从都需要开启log_slave_updates，这样才能检查到。

`--recursion-method`:指定复制检查的方式,默认为processlist,hosts。

`--update`:更新主上的心跳表。

`--replace`:使用--replace代替--update模式更新心跳表里的时间字段，这样的好处是不用管表里是否有行。

`--stop`:停止运行该工具（--daemonize），在/tmp/目录下创建一个“pt-heartbeat-sentinel” 文件。后面想重新开启则需要把该临时文件删除，才能开启（--daemonize）。

`--table`:指定心跳表名，默认heartbeat。

1.3 pt-heartbeat 实战

1）主库创建 heartbeat心跳表，通过update执行更新时间戳，心跳表指定建立在sbtest库下。

pt-heartbeat -S /tmp/mysql3306.sock --database sbtest --update --create-table --daemonize

2）主库利用sysbench模拟数据操作

sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-socket=/tmp/mysql3306.sock \
--mysql-user=root \
--mysql-password=mysql \
--mysql-db=sbtest \
--db-driver=mysql \
--tables=10 \
--table-size=500000 \
--report-interval=10 \
--threads=128 \
--time=120 \
prepare|run|cleanup

prepare:准备数据，
run：执行压测，
cleanup:清除数据

3）从库进行监控

pt-heartbeat -S /tmp/mysql3306.sock --master-server-id=1003306 --monitor --database sbtest 
1.00s [  0.02s,  0.00s,  0.00s ]
1.00s [  0.03s,  0.01s,  0.00s ]
0.86s [  0.05s,  0.01s,  0.00s ]
0.86s [  0.06s,  0.01s,  0.00s ]
0.96s [  0.08s,  0.02s,  0.01s ]
1.00s [  0.09s,  0.02s,  0.01s ]
0.88s [  0.11s,  0.02s,  0.01s ]
0.00s [  0.11s,  0.02s,  0.01s ]
1.01s [  0.13s,  0.03s,  0.01s ]
0.00s [  0.13s,  0.03s,  0.01s ]
0.00s [  0.13s,  0.03s,  0.01s ]
...

结果表示有延时1s左右，中括号中的数据分别表示1m,5m,15m的平均值

2. pt-slave-restart

pt-slave-restart是一个可以跳过特定错误并自动重启slave的工具。

2.1 pt-slave-restart 原理

pt-slave-restart监控一个或者多个MySQL复制slave，试图跳过引起错误的语句。它以指数变化的睡眠时间职能地检查slave。你可以指定要跳过的错误然后运行slave一直到一个确定的binlog位置。

pt-slave-restart一旦检测到slave有错误就会打印一行。默认情况下该打印行为：时间戳、连接信息、relay_log_file，relay_log_pos，以及last_errno。你可以使用--verbose选项添加更多信息，也可以使用--quiet选项阻止所有输出。

SLEEP
pt-slave-restart检查slave的过程中智能地sleep。当前的sleep时间是变化的。

• 初始sleep时间通过--sleep选项给出。
• 如果检测发现错误，它对半之前的sleep时间。
• 如果检测到没有错误，它倍增之前的sleep时间。
• 通过--min-sleep和--max-sleep参数限定sleep时间的下界和上界。
• 一旦检测到错误，pt-slave-restart假定接下来很可能发生另一个错误，因此它采用当前的sleep时间或者初始sleep时间，取决于哪个值更小。

注意：
从Percona Toolkit 2.2.8版本起，pt-slave-restart开始支持由MySQL 5.6.5版本引入的GTID复制。重点牢记：

当采用多线程复制（slave_parallel_workers > 0）时，pt-slave-restart不能跳过事务。pt-slave-restart不能确定GTID事件是哪个特定slave线程执行失败的事务。
默认行为是跳过来自master的下一个事务。写可以来自不同的服务器，每个服务器都有它自己的UUID。参考–master-uuid选项。

2.2 pt-slave-restart 主要参数介绍

- ` --always `       ：永不停止slave线程，手工停止也不行

- ` --ask-pass`      ：替换`-p`命令，不显示密码输入

- ` --error-numbers` ：指定跳过哪些错误，可用`,`进行分隔

- ` --error-text`    ：根据错误信息进行匹配跳过

- ` --log`           ：输出到文件

- ` --recurse`       ：在主端执行，监控从端

- ` --runtime  `     ：工具执行多长时间后退出：默认秒， m=minute,h=hours,d=days

- ` --slave-user --slave-password` ：从库的账号密码，从主端运行时使用

- ` --skip-count  `  ：一次跳过错误的个数，胆大的可以设置大些，不指定默认1个

 - `--master-uuid`   ：级联复制的时候，指定跳过上级或者上上级事务的错误

- ` --until-master`  ：到达指定的master_log_pos,file位置后停止，格式：”file:pos“

 - `--until-relay`   ：和上面一样，但根据relay_log的位置来停止

- `--sleep`：默认值为1，检查slave间隔的初始sleep秒数。

2.3 pt-slave-restart 实战

#1. 在master上创建表
create table z1(id int not null,uname varchar(32),primary key(id));

#2. 在slave上插入数据
set sql_log_bin=0;
set global read_only=off;
insert into z1(id,uname) values(3,'python');

#3. 在master上插入数据
insert into z1(id,uname) values(2,'mysql');
insert into z1(id,uname) values(3,'java');

#4. 在slave上查看复制状态 
show slave status\G

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
...
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table test.z1; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log my3306_binlog.000071, end_log_pos 1214

#5. 在slave上使用pt-slave-restart跳过指定错误
pt-slave-restart --error-numbers=1062
2018-09-19T15:24:02  mysqldb2-relay-bin.000019        1088 1062 

#6. 在slave上查看复制状态 
···
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
···

#说明复制已经正常，跳过了错误事务。

#但是master与slave上的这张表数据不一致，所以这种修复方法只能算暂时的。

#之后还需进行修复

3. pt-table-checksum

pt-table-checksum工具用来检查主从数据一致性。

3.1 pt-table-checksum 原理

pt-table-checksum用于校验主从数据的一致性，该命令在主库上执行校验，然后对复制的一致性进行检查，来对比主从之间的校验值，并输出对比结果。

3.2 pt-table-checksum 主要参数介绍

- `--[no]check-replication-filters`：是否检查复制的过滤器，默认是yes，建议启用不检查模式。

- `--databases | -d`：指定需要被检查的数据库，多个库之间可以用逗号分隔。

- `--[no]check-binlog-format`：是否检查binlog文件的格式，默认值yes。建议开启不检查。因为在默认的row格式下会出错。

- `--replicate`：把checksum的信息写入到指定表中。

- `--replicate-check-only`：只显示不同步信息

3.3 pt-table-checksum 实战

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --tables=z1 h=192.168.56.100,u=wanbin,p=mysql,P=3306

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-19T15:58:29      0      1        2          0       1       0   0.086 test.z1
#解释：
`TS` ：完成检查的时间。
`ERRORS` ：检查时候发生错误和警告的数量。
`DIFFS`：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
`ROWS` ：表的行数。
`CHUNKS` ：被划分到表中的块的数目。
`SKIPPED` ：由于错误或警告或过大，则跳过块的数目。
`TIME` ：执行的时间。
`TABLE` ：被检查的表名。

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --replicate-check-only  h=192.168.56.100,u=wanbin,p=mysql,P=3306

Checking if all tables can be checksummed ...
Starting checksum ...
Differences on mysqldb2
TABLE CHUNK CNT_DIFF CRC_DIFF CHUNK_INDEX LOWER_BOUNDARY UPPER_BOUNDARY
test.z1 1 -1 1   


【注意】：
1）根据测试，需要一个即能登录主库，也能登录从库的账号；
2）只能指定一个host，必须为主库的IP；
3）在检查时会向表加S锁；
4）运行之前需要从库的同步IO和SQL进程是YES状态。

补充：pt-table-checksums工具用到的主库配置是autocommited=1,也就是是自动提交的方式，如果你把这个配置设置为0了，当使用这个工具时会出现如下情况：

[root@node01 ~]#  pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=liulin.checksums  --databases=liulin --tables=test1   h=172.17.94.206,u=root,p=123,P=3307 （本人用的是这条命令进行测试的）
Checking if all tables can be checksummed ...
Starting checksum ...
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
^C# Caught SIGINT.

这时就检测不出来，原因在于你关闭了mysql的自动提交功能，将autocommit设置为0，所以第一种方式就是你将自动提交给打开，将它设置为1
第二种解决方法是修改pt-table-checksums脚本，
如下所示：

修改第一个地方：

 $update_sth->execute(
     # UPDATE repl_table SET
     sprintf('%.6f', $tbl->{nibble_time}), # chunk_time
     $crc,                                 # master_crc
     $cnt,                                 # master_cnt
     # WHERE
     $tbl->{db},
     $tbl->{tbl},
     $chunk,
  );

在以上代码后面添加

  $master_dbh->do("commit");

修改第二个地方：

  $sth->{nibble}->execute(
     # REPLACE INTO repl_table SELECT
     $tbl->{db},             # db
     $tbl->{tbl},            # tbl
     $chunk,                 # chunk (number)
     $chunk_index,           # chunk_index
     $lb_quoted,             # lower_boundary
     $ub_quoted,             # upper_boundary
     # this_cnt, this_crc WHERE
     @{$boundary->{lower}},  # upper boundary values
     @{$boundary->{upper}},  # lower boundary values
  );

在代码这后添加：

  $dbh->do("commit");

修改之后，关闭autocommit ,并清空percona.checksums 

这时再执行以上pt-table-checksums命令就是现实正常了。

4. pt-table-sync

pt-table-sync用来修复主从数据不一致

4.1 pt-table-sync 原理

pt-table-sync高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

4.2 pt-table-sync 主要参数介绍

`--replicate` ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
`--databases` : 指定执行同步的数据库。
`--tables` ：指定执行同步的表，多个用逗号隔开。
`--sync-to-master` ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
`h=` ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
`u=` ：帐号。
`p=` ：密码。
`--print` ：打印，但不执行命令。
`--execute ：执行命令。

4.3 pt-table-sync 实战

#print修复命令

pt-table-sync --replicate=test.checksums h=192.168.56.100,u=wanbin,p=mysql,P=3306 h=192.168.56.200,u=wanbin,p=mysql,P=3306 --print

REPLACE INTO `test`.`z1`(`id`, `uname`) VALUES ('3', 'java') /*percona-toolkit src_db:test src_tbl:z1 src_dsn:P=3306,h=192.168.56.100,p=...,u=wanbin dst_db:test dst_tbl:z1 dst_dsn:P=3306,h=mysqldb2,p=...,u=wanbin lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:4374 user:root host:mysqldb1*/;
#execute修复命令
pt-table-sync --replicate=test.checksums h=192.168.56.100,u=wanbin,p=mysql,P=3306 h=192.168.56.200,u=wanbin,p=mysql,P=3306 --execute
#再使用pt-table-checksum


pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --create-replicate-table --databases=test --tables=z1 h=192.168.56.100,u=wanbin,p=mysql,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-19T16:11:50      0      0        2          0       1       0   0.134 test.z1


【注意】：要是表中没有唯一索引或则主键则会报错：
Can't make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.




5 pt-query-disgest

• 该工具是用来分析mysql中的慢日志(slow.log)的工具，且可以分析general log、binlog日志，同时也可以通过show processlist或tcpdump抓取mysql协议数据来进行分析。　　
• 安装可以参考官网或github　　
• https://www.cnblogs.com/luyucheng/p/6265873.html（pt-guery-disgest参考博客）
https://aws.amazon.com/cn/blogs/china/pt-query-digest-rds-mysql-slow-searchnew/ (pt-query-disgest参考博客)