MySQL主从同步检查工具PT使用介绍

文章目录[隐藏]

       在 mysql 工作中接触最多的就是 mysql replication,mysql 在复制方面还是会有一些常规问题,比如主库宕机或者从库宕机有可能会导致复制中断,通常需要进行人为修复,或者很多时候需要把一个从库提升为主库,但对从库和主库的数据一致性不能保证一样。这种情况下就需要使用 percona-toolkit 工具的 pt-table-checksum 组件来检查主从数据的一致性;如果发现不一致的数据,可以通过 pt-table-sync 修复;还可以通过 pt-heartbeat 监控主从复制延迟。当然如果数据量小,slave 只是当做一个备份使用,那么出现数据不一致完全可以重做,或者通过其他方法解决。如果数据量非常大,重做就是非常蛋碎的一件事情了。比如说,线上数据库做了主从同步环境,数据库在进行了迁移后,需要对 mysql 迁移(Replication)后的数据一致性进行校验,但又不能对生产环境使用造成影响,pt-table-checksum 成为了绝佳也是唯一的检查工具。

percona-toolkit 介绍
percona-toolkit 是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的 mysql 和系统任务,这些任务包括:
   1)检查 master 和 slave 数据的一致性
   2)有效地对记录进行归档
   3)查找重复的索引
   4)对服务器信息进行汇总
   5)分析来自日志和 tcpdump 的查询
   6)当系统出问题的时候收集重要的系统信息
percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的工具。不过,现在 Maatkit 工具已经不维护了,所以以后推荐还是使用 percona-toolkit 工具!
这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的 DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。

percona-toolkit 工具中最主要的三个组件分别是:
   1)pt-table-checksum 负责监测 mysql 主从数据一致性
   2)pt-table-sync 负责当主从数据不一致时修复数据,让它们保存数据的一致性
   3)pt-heartbeat 负责监控 mysql 主从同步延迟
下面就对这三个组件的使用做一记录,当然 percona-toolkit 工具也有很多其他组件,后面会一一说明。

percona-toolkit 工具安装(建议主库和从库服务器上都安装)
软件下载并在主库服务器上安装 

[root@master-server src]# rpm -ivh https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/7/x86_64/percona-toolkit-3.0.12-1.el7.x86_64.rpm
    //安装后,percona-toolkit 工具的各个组件命令就有有了(输入 pt-,按 TAB 键就会显示)

安装该工具依赖的软件包

[root@master-server src]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y

一、pt-table-checksum 使用梳理

pt-table-checksum 是 Percona-Toolkit 的组件之一,用于检测 MySQL 主、从库的数据是否一致。其原理是在主库执行基于 statement 的 sql 语句来生成主库数据块的 checksum,把相同的 sql 语句传递到从库执行,并在从库上计算相同数据块的 checksum,最后,比较主从库上相同数据块的 checksum 值,由此判断主从数据是否一致。检测过程根据唯一索引将表按 row 切分为块(chunk),以为单位计算,可以避免锁表。检测时会自动判断复制延迟、 master 的负载, 超过阀值后会自动将检测暂停,减小对线上服务的影响。
pt-table-checksum 默认情况下可以应对绝大部分场景,官方说,即使上千个库、上万亿的行,它依然可以很好的工作,这源自于设计很简单,一次检查一个表,不需要太多的内存和多余的操作;必要时,pt-table-checksum 会根据服务器负载动态改变 chunk 大小,减少从库的延迟。

为了减少对数据库的干预,pt-table-checksum 还会自动侦测并连接到从库,当然如果失败,可以指定–recursion-method 选项来告诉从库在哪里。它的易用性还体现在,复制若有延迟,在从库 checksum 会暂停直到赶上主库的计算时间点(也通过选项–设定一个可容忍的延迟最大值,超过这个值也认为不一致)。

为了保证主数据库服务的安全,该工具实现了许多保护措施:
    1)自动设置 innodb_lock_wait_timeout 为 1s,避免引起
    2)默认当数据库有 25 个以上的并发查询时,pt-table-checksum 会暂停。可以设置 –max-load 选项来设置这个阀值
    3)当用 Ctrl+C 停止任务后,工具会正常的完成当前 chunk 检测,下次使用 –resume 选项启动可以恢复继续下一个 chunk

pt-table-checksum [OPTIONS] [DSN] pt-table-checksum:在主(master)上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN 指向的是主的地址,该工具的退出状态不为零,如果发现有任何差别,或者如果出现任何警告或错误。注意:第一次运行的时候需要加上–create-replicate-table 参数,生成 checksums 表!!如果不加这个参数,那么就需要在对应库下手工添加这张表了,表结构 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 :不检查复制过滤器,建议启用。后面可以用–databases 来指定需要检查的数据库。
–no-check-binlog-format : 不检查复制的 binlog 模式,要是 binlog 模式是 ROW,则会报错。
–replicate-check-only :只显示不同步的信息。
–replicate= :把 checksum 的信息写入到指定表中,建议直接写到被检查的数据库当中。
–databases= :指定需要被检查的数据库,多个则用逗号隔开。
–tables= :指定需要被检查的表,多个用逗号隔开
h= :Master 的地址
u= :用户名
p=:密码
P= :端口

最重要的一点就是:
要在主库上授权,能让主库 ip 访问。这一点不能忘记!(实验证明从库上可以不授权,但最好还是从库也授权)
注意:
1)根据测试,需要一个即能登录主库,也能登录从库的账号;
2)只能指定一个 host,必须为主库的 IP;
3)在检查时会向表加 S 锁;
4)运行之前需要从库的同步 IO 和 SQL 进程是 YES 状态。

例如:(本文例子中:192.168.4.239 是主库 ip,192.168.4.50 是从库 ip)
在主库执行授权(一定要对主库 ip 授权,授权的用户名和密码可以自行定义,不过要保证这个权限能同时登陆主库和从库)

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'replicate'@'192.168.4.239' identified by 'replicate';
mysql> flush privileges;

在从库上执行授权

mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.4.239' identified by 'replicate';
mysql> flush privileges;

如下,在主库上执行的一个检查主从数据一致性的命令(别忘了第一次运行的时候需要添加–create-replicate-table 参数,后续再运行时就不需要加了):
下面命令中的 192.168.4.239 是主库 ip
检查的是 wordpress 库下的 wp_posts 表的数据(当然,命令中也可以不跟表,直接检查某整个库的数据;如下去掉–tables=wp_posts 表,直接检查 wordpress 库的数据)

[root@beiyong ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=wordpress --tables=wp_posts h=192.168.4.239,u=replicate,p=replicate,P=3306
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
10-23T14:51:01      0      0       10          0       1       0   0.317 wordpress.wp_posts

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

二、pt-table-sync 用法梳理

如果通过 pt-table-checksum 检查找到了不一致的数据表,那么如何同步数据呢?即如何修复 MySQL 主从不一致的数据,让他们保持一致性呢?
这时候可以利用另外一个工具 pt-table-sync。
使用方法:
pt-table-sync: 高效的同步 MySQL 表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。

假如上面检查数据时发现主从不一致

[root@master-server ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --databases=wordpress --tables=wp_posts h=192.168.4.239,u=replicate,p=replicate,P=3306
Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:04:54      0      0        4       1       0   0.009 wordpress.wp_posts

现在需要 DIFFS 为 1 可知主从数据不一致,需要修复!修复命令如下:
先 master 的 ip,用户,密码,然后是 slave 的 ip,用户,密码

[root@master-server ~]# pt-table-sync --replicate=pt.checksums h=192.168.4.239,u=replicate,p=replicate h=192.168.4.50,u=replicate,p=replicate --print 
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('1', 'wangshibo') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('2', 'wangshikui') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('3', 'limeng') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;
REPLACE INTO `huanqiu`.`haha`(`id`, `name`) VALUES ('4', 'wanghi') /*percona-toolkit src_db:huanqiu src_tbl:haha src_dsn:h=192.168.1.101,p=...,u=root dst_db:huanqiu dst_tbl:haha dst_dsn:h=192.168.1.102,p=...,u=root lock:1 transaction:1 changing_src:huanqiu.checksums replicate:huanqiu.checksums bidirectional:0 pid:23676 user:root host:master-server*/;

参数解释:
–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 :执行命令

上面命令介绍完了,接下来开始执行修复:
通过(–print)打印出来了修复数据的 sql 语句,可以手动的在 slave 从库上执行,让他们数据保持一致性,这样比较麻烦!
可以直接在 master 主库上执行修复操作,通过–execute 参数,如下:
[root@master-server ~]# pt-table-sync –replicate=huanqiu.checksums h=192.168.4.239,u=replicate,p=replicate h=192.168.4.50,u=replicate,p=replicate –execute

如上修复后,再次检查,发现主从库数据已经一致了!

[root@master-server ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --databases=wordpress --tables=wp_posts h=192.168.4.239,u=replicate,p=replicate,P=3306
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
01-08T04:36:43      0      0        4       1       0   0.040 wordpress.wp_posts

———————————————————————————————————————–
建议:
修复数据的时候,最好还是用–print 打印出来的好,这样就可以知道那些数据有问题,可以人为的干预下。
不然直接执行了,出现问题之后更不好处理。总之还是在处理之前做好数据的备份工作。

注意:要是表中没有唯一索引或则主键则会报错:
Can’t make changes on the master because no unique index exists at /usr/local/bin/pt-table-sync line 10591.
———————————————————————————————————————–
为了确保主从数据的一致性,可以编写监控脚本,定时检查。当检查到主从数据不一致时,强制修复数据。

[root@master-server ~]# cat /root/pt_wordpress.sh
#!/bin/bash
NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanqiu.checksums --databases=huanqiu  h=192.168.1.101,u=root,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p')
if [ $NUM -eq 1 ];then
  /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print
  /usr/bin/pt-table-sync --replicate=huanqiu.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute
else
  echo "data is ok"
fi

[root@master-server ~]# cat /root/pt_huanpc.sh 
#!/bin/bash
NUM=$(/usr/bin/pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=huanpc.checksums --databases=huanpc  h=192.168.1.101,u=root,p=123456,P=3306|awk -F" " '{print $3}'|sed -n '2p')
if [ $NUM -eq 1 ];then
  /usr/bin/pt-table-sync --replicate=huanpc.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --print
  /usr/bin/pt-table-sync --replicate=huanpc.checksums h=192.168.1.101,u=root,p=123456 h=192.168.1.102,u=root,p=123456 --execute
else
  echo "data is ok"
fi
[root@master-server ~]# crontab -l
#检查主从 huanqiu 库数据一致性
* * * * * /bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1
* * * * * sleep 10;/bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1
* * * * * sleep 20;/bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1
* * * * * sleep 30;/bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1
* * * * * sleep 40;/bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1
* * * * * sleep 50;/bin/bash -x /root/pt_wordpress.sh > /dev/null 2>&1

#检查主从 huanpc 库数据一致性
* * * * * /bin/bash -x /root/root/pt_huanpc.sh > /dev/null 2>&1
* * * * * sleep 10;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1
* * * * * sleep 20;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1
* * * * * sleep 30;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1
* * * * * sleep 40;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1
* * * * * sleep 50;/bin/bash -x /root/pt_huanpc.sh > /dev/null 2>&1

———————————————————————————————————————–
最后总结:
pt-table-checksum 和 pt-table-sync 工具很给力,工作中常常在使用。注意使用该工具需要授权,一般 SELECT, PROCESS, SUPER, REPLICATION SLAVE 等权限就已经足够了。

三、pt-heartbeat 监控 mysql 主从复制延迟梳理

对于 MySQL 数据库主从复制延迟的监控,可以借助 percona 的有力武器 pt-heartbeat 来实现。
pt-heartbeat 的工作原理通过使用时间戳方式在主库上更新特定表,然后在从库上读取被更新的时间戳然后与本地系统时间对比来得出其延迟。具体流程:
   1)在主上创建一张 heartbeat 表,按照一定的时间频率更新该表的字段(把时间更新进去)。监控操作运行后,heartbeat 表能促使主从同步!
   2)连接到从库上检查复制的时间记录,和从库的当前系统时间进行比较,得出时间的差异。

使用方法(主从和从库上都可以执行监控操作):
pt-heartbeat [OPTIONS] [DSN] –update|–monitor|–check|–stop

注意:需要指定的参数至少有 --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,
  position              bigint unsigned DEFAULT NULL,
  relay_master_log_file varchar(255) DEFAULT NULL,
  exec_master_log_pos   bigint unsigned DEFAULT NULL
);
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。

更新主库上的 heartbeat,–interval=1 表示 1 秒钟更新一次(注意这个启动操作要在主库服务器上执行)

[root@master-server ~]# pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize
Enter password: 
[root@master-server ~]# 
[root@master-server ~]# ps -ef|grep pt-heartbeat
root 15152 1 0 19:49 ? 00:00:00 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize
root 15154 14170 0 19:49 pts/3 00:00:00 grep pt-heartbeat

在主库运行监测同步延迟:

[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
........
解释:0 表示从没有延迟。 [ 0.00s, 0.00s, 0.00s ] 表示 1m,5m,15m 的平均值。可以通过--frames 去设置。

或者加上--master-server-id 参数(主库 my.cnf 里配置的 server-id 值)
[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --master-server-id=101
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
.........
也可以将主库的 server-id 打印出来(--print-master-server-id)
[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monit --host=192.168.1.102 --user=root --password=123456 --print-master-server-id
0.00s [ 0.00s, 0.00s, 0.00s ] 101
0.00s [ 0.00s, 0.00s, 0.00s ] 101
0.00s [ 0.00s, 0.00s, 0.00s ] 101
0.00s [ 0.00s, 0.00s, 0.00s ] 101
.........
[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --check --host=192.168.1.102 --user=root --password=123456 --print-master-server-id
0.00 101

上面的监测命令会一直在运行状态中,可以使用–check 监测一次就退出
注意:使用了–check,就不能使用–monit
–update,–monitor 和–check 是互斥的,–daemonize 和–check 也是互斥。

[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --check --host=192.168.1.102 --user=root --password=123456
0.00
[root@master-server ~]#

注意:
如果想把这个输出结果加入自动化监控,那么可以使用如下命令使监控输出写到文件,然后使用脚本定期过滤文件中的最大值作为预警即可:
注意–log 选项必须在有–daemonize 参数的时候才会打印到文件中,且这个文件的路径最好在/tmp 下,否则可能因为权限问题无法创建

[root@master-server ~]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize
[root@master-server ~]# tail -f /opt/master-slave.txt            //可以测试,在主库上更新数据时,从库上是否及时同步,如不同步,可以在这里看到监控的延迟数据
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
.......

下面是编写的主从同步延迟监控脚本,就是定期过滤–log 文件中最大值(此脚本运行的前提是:启动更新主库 heartbeat 命令以及带上–log 的同步延迟检测命令)。如果发生延迟,发送报警邮件。sendemail 邮件发送环境部署参考:http://www.cnblogs.com/kevingrace/p/5961861.html

[root@master-server ~]# cat /root/check-slave-monit.sh    
#!/bin/bash
cat /opt/master-slave.txt > /opt/master_slave.txt
echo > /opt/master-slave.txt
max_time=`cat /opt/master_slave.txt |grep -v '^$' |awk '{print $1}' |sort -k1nr |head -1`
NUM=$(echo "$max_time"|cut -d"s" -f1)
if [ $NUM == "0.00" ];then
   echo "Mysql 主从数据一致"
else
   /usr/local/bin/sendEmail -f ops@huanqiu.cn -t wangshibo@huanqiu.cn -s smtp.huanqiu.cn -u "Mysql 主从同步延迟" -o message-content-type=html -o message-charset=utf8 -xu ops@huanqiu.cn -xp WEE78@12l$ -m "Mysql 主从数据同步有延迟"
fi
[root@master-server ~]# chmod /root/check-slave-monit.sh
[root@master-server ~]# sh /root/check-slave-monit.sh 

Mysql 主从数据一致

结合 crontab,每隔一分钟检查一次

[root@master-server ~]# crontab -e
#mysql 主从同步延迟检查
* * * * * /bin/bash -x /root/check-slave-monit.sh > /dev/null 2>&1

在从库上运行监测同步延迟(也可以在命令后加上–master-server-id=101 或–print-master-server-id,同上操作)

[root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --user=root --password=123456
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
........
[root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --user=root --password=123456 --check
0.00
[root@slave-server src]# pt-heartbeat -D huanqiu --table=heartbeat --monitor --user=root --password=123456 --log=/opt/master-slave.txt --daemonize
[root@slave-server src]# tail -f /opt/master-slave.txt 
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]

如何关闭上面在主库上执行的 heartbeat 更新进程呢?
方法一:可以用参数--stop 去关闭
[root@master-server ~]# ps -ef|grep heartbeat
root 15152 1 0 19:49 ? 00:00:02 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize
root 15310 1 0 19:59 ? 00:00:01 perl /usr/bin/pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize
root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat
[root@master-server ~]# pt-heartbeat --stop
Successfully created file /tmp/pt-heartbeat-sentinel
[root@master-server ~]# ps -ef|grep heartbeat
root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat
[root@master-server ~]#

这样就把在主上开启的进程杀掉了。
但是后续要继续开启后台进行的话,记住一定要先把/tmp/pt-heartbeat-sentinel 文件删除,否则启动不了

方法二:直接 kill 掉进程 pid(推荐这种方法)
[root@master-server ~]# ps -ef|grep heartbeat
root 15152 1 0 19:49 ? 00:00:02 perl /usr/bin/pt-heartbeat --user=root --ask-pass --host=192.168.1.101 --create-table -D huanqiu --interval=1 --update --replace --daemonize
root 15310 1 0 19:59 ? 00:00:01 perl /usr/bin/pt-heartbeat -D huanqiu --table=heartbeat --monitor --host=192.168.1.102 --user=root --password=123456 --log=/opt/master-slave.txt --daemonize
root 15555 31932 0 20:13 pts/2 00:00:00 grep heartbeat
[root@master-server ~]# kill -9 15152
[root@master-server ~]# ps -ef|grep heartbeat
root 15558 31932 0 20:14 pts/2 00:00:00 grep heartbeat

最后总结:
通过 pt-heartbeart 工具可以很好的弥补默认主从延迟的问题,但需要搞清楚该工具的原理。
默认的 Seconds_Behind_Master 值是通过将服务器当前的时间戳与二进制日志中的事件时间戳相对比得到的,所以只有在执行事件时才能报告延时。备库复制线程没有运行,也会报延迟 null。
还有一种情况:大事务,一个事务更新数据长达一个小时,最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当备库执行这条语句时,会临时地报告备库延迟为一个小时,执行完后又很快变成 0。

—————————————percona-toolkit 其他组件命令用法———————————- 

下面这些工具最好不要直接在线上使用,应该作为上线辅助或故障后离线分析的工具,也可以做性能测试的时候配合着使用。

1)pt-online-schema-change
功能介绍:
功能为:在 alter 操作更改表结构的时候不用锁定表,也就是说执行 alter 的时候不会阻塞写和读取操作,注意执行这个工具的时候必须做好备份,操作之前最好要充分了解它的原理。
工作原理是:创建一个和你要执行 alter 操作的表一样的空表结构,执行表结构修改,然后从原表中 copy 原始数据到表结构修改后的表,当数据 copy 完成以后就会将原表移走,用新表代替原表,默认动作是将原表 drop 掉。在 copy 数据的过程中,任何在原表的更新操作都会更新到新表,因为这个工具在会在原表上创建触发器,触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。

用法介绍:
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help(或加–help 查看有哪些选项),DNS 为你要操作的数据库和表。
有两个参数需要注意一下:
–dry-run 这个参数不建立触发器,不拷贝数据,也不会替换原表。只是创建和更改新表。
–execute 这个参数的作用和前面工作原理的介绍的一样,会建立触发器,来保证最新变更的数据会影响至新表。注意:如果不加这个参数,这个工具会在执行一些检查后退出。这一举措是为了让使用这充分了解了这个工具的原理。

使用示例:
在线更改表的的引擎,这个尤其在整理 innodb 表的时候非常有用,如下 huanqiu 库的 haha 表默认是 Myisam 存储引擎,现需要在线修改成 Innodb 类型。

mysql> show create table huanqiu.haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha  | CREATE TABLE `haha` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改操作如下:

[root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --check-replication-filters
Found 1 slaves:
  slave-server
Will check slave lag on:
  slave-server
Replication filters are set on these hosts:
  slave-server
    slave_skip_errors = ALL
    replicate_ignore_db = mysql
    replicate_do_db = huanqiu,huanpc
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-online-schema-change line 8083.

如上命令就是在主库上操作的,会提示它有从库,需要添加参数–nocheck-replication-filters,即不检查从库。(注意:下面命令中可以将 localhost 换成主库 ip。另外:该命令只能针对某张表进行修改,因为它是针对 alter 操作的,而 alter 是针对表的操作命令。所以不能省略命令中”t=表名”的选项)

[root@master-server ~]# pt-online-schema-change --user=root --password=123456 --host=localhost --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute --nocheck-replication-filters       
Found 1 slaves:
  slave-server
.......
2017-01-16T10:36:33 Dropped old table `huanqiu`.`_haha_old` OK.
2017-01-16T10:36:33 Dropping triggers...
2017-01-16T10:36:33 Dropped triggers OK.
Successfully altered `huanqiu`.`haha`.

>

然后再次查看 huanqiu.haha 表的存储引擎,发现已是 Innodb 类型的了。

mysql> show create table huanqiu.haha;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| haha  | CREATE TABLE `haha` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

若是在从库上,则可以直接执行(也可以将下面从库 ip 替换成 localhost)

[root@slave-server ~]# pt-online-schema-change --user=root --password=123456 --host=192.168.1.102 --alter="ENGINE=InnoDB" D=huanqiu,t=haha --execute
No slaves found.  See --recursion-method if host slave-server has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
.......
2017-01-15T21:40:35 Swapped original and new tables OK.
2017-01-15T21:40:35 Dropping old table...
2017-01-15T21:40:35 Dropped old table `huanqiu`.`_haha_old` OK.
2017-01-15T21:40:35 Dropping triggers...
2017-01-15T21:40:35 Dropped triggers OK.
Successfully altered `huanqiu`.`haha`.

2)pt-duplicate-key-checker
功能介绍:
功能为从 mysql 表中找出重复的索引和外键,这个工具会将重复的索引和外键都列出来,并生成了删除重复索引的语句,非常方便
用法介绍:
pt-duplicate-key-checker [OPTION…] [DSN] 包含比较多的选项,具体的可以通过命令 pt-duplicate-key-checker –help 来查看具体支持那些选项,我这里就不一一列举了。DNS 为数据库或者表。
使用示例:
查看 huanqiu 库或 huanqiu.haha 表的重复索引和外键使用情况使用,如下命令:

[root@master-server ~]# pt-duplicate-key-checker  --host=localhost --user=root --password=123456  --databases=huanqiu
# ########################################################################
# Summary of indexes                                                     
# ########################################################################
 
# Total Indexes  6
# [root@master-server ~]# pt-duplicate-key-checker  --host=localhost --user=root --password=123456  --databases=huanqiu --table=haha
# ########################################################################
# Summary of indexes                                                     
# ########################################################################
 
# Total Indexes  1

3)pt-slave-find
功能介绍:
查找和打印 mysql 所有从服务器复制层级关系
用法介绍:
pt-slave-find [OPTION…] MASTER-HOST
原理:连接 mysql 主服务器并查找其所有的从,然后打印出所有从服务器的层级关系。
使用示例:
查找主服务器为 192.168.1.101 的 mysql 有所有从的层级关系(将下面的 192.168.1.101 改成 localhost,就是查询本机 mysql 的从关系):

[root@master-server ~]# pt-slave-find --user=root --password=123456 --host=192.168.1.101
192.168.1.101
Version         5.6.33-log
Server ID       101
Uptime          5+02:59:42 (started 2017-01-11T10:44:14)
Replication     Is not a slave, has 1 slaves connected, is not read_only
Filters        
Binary logging  MIXED
Slave status   
Slave mode      STRICT
Auto-increment  increment 1, offset 1
InnoDB version  5.6.33
+- 192.168.1.102
   Version         5.6.34-log
   Server ID       102
   Uptime          4+22:22:18 (started 2017-01-11T15:21:38)
   Replication     Is a slave, has 0 slaves connected, is not read_only
   Filters         slave_skip_errors=ALL; replicate_ignore_db=mysql; replicate_do_db=huanqiu,huanpc
   Binary logging  MIXED
   Slave status    265831 seconds behind, running, no errors
   Slave mode      STRICT
   Auto-increment  increment 1, offset 1
   InnoDB version  5.6.34

4)pt-show-grants
功能介绍:
规范化和打印 mysql 权限,让你在复制、比较 mysql 权限以及进行版本控制的时候更有效率!
用法介绍:
pt-show-grants [OPTION…] [DSN] 选项自行用 help 查看,DSN 选项也请查看 help,选项区分大小写。
使用示例:
查看指定 mysql 的所有用户权限:

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456'
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:22:12
-- Grants for ''@'localhost'
GRANT USAGE ON *.* TO ''@'localhost';
-- Grants for 'data_check'@'%'
.......

查看执行数据库的权限:

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456' --database='huanqiu'
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:23:16
-- Grants for ''@'localhost'
GRANT USAGE ON *.* TO ''@'localhost';
-- Grants for 'data_check'@'%'
GRANT SELECT ON *.* TO 'data_check'@'%' IDENTIFIED BY PASSWORD '*36B94ABF70E8D5E025CF9C059E66445CBB05B54F';
-- Grants for 'mksync'@'%'
GRANT ALL PRIVILEGES ON *.* TO 'mksync'@'%' IDENTIFIED BY PASSWORD '*B5E7409B1A22D47C6F1D8A693C6146CEB6570475';
........

查看每个用户权限生成 revoke 收回权限的语句:

[root@master-server ~]# pt-show-grants --host='localhost' --user='root' --password='123456'  --revoke
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-16 11:24:58
-- Revoke statements for ''@'localhost'
REVOKE USAGE ON *.* FROM ''@'localhost';
-- Grants for ''@'localhost'
..........

5)pt-upgrade
功能介绍:
这个工具用来检查在新版本中运行的 SQL 是否与老版本一样,返回相同的结果,最好的应用场景就是数据迁移的时候。这在升级服务器的时候非常有用,可以先安装并导数据到新的服务器上,然后使用这个工具跑一下 sql 看看有什么不同,可以找出不同版本之间的差异。
用法介绍:
pt-upgrade [OPTION…] DSN [DSN…] [FILE] 比较文件中每一个查询语句在每台服务器上执行的结果(主要是针对不同版本的执行结果)。(–help 查看选项)
使用示例:
查看某个 sql 文件在两个服务器的运行结果范例:

 

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

[root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /root/test.sql
 
#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------
.......
host1:
  DSN:       h=192.168.1.101
  hostname:  master-server
  MySQL:     Source distribution 5.6.33
 
host2:
  DSN:       h=192.168.1.102
  hostname:  slave-server
  MySQL:     Source distribution 5.6.34
......
queries_read          1
queries_with_diffs    0
queries_with_errors   0

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

[root@master-server ~]# pt-upgrade h=192.168.1.101 h=192.168.1.102 --user=root --password=123456 /data/mysql/data/mysql-slow.log
.......

6)pt-index-usage
功能介绍:
这个工具主要是用来分析慢查询的索引使用情况。从 log 文件中读取插叙语句,并用 explain 分析他们是如何利用索引。完成分析之后会生成一份关于索引没有被查询使用过的报告。
用法介绍:
pt-index-usage [OPTION…] [FILE…] 可以直接从慢查询中获取 sql,FILE 文件中的 sql 格式必须和慢查询中个是一致,如果不是一直需要用 pt-query-digest 转换一下。也可以不生成报告直接保存到数据库中,具体的见后面的示例
注意:使用这个工具需要 MySQL 必须要有密码,另外运行时可能报找不到/var/lib/mysql/mysql.sock 的错,简单的从 mysql 启动后的 sock 文件做一个软链接即可。
重点要说明的是 pt-index-usage 只能分析慢查询日志,所以如果想全面分析所有查询的索引使用情况就得将 slow_launch_time 设置为 0,因此请谨慎使用该工具,线上使用的话最好在凌晨进行分析,尤其分析大量日志的时候是很耗 CPU 的。
整体来说这个工具是不推荐使用的,要想实现类似的分析可以考虑一些其他第三方的工具,比如:mysqlidxchx, userstat 和 check-unused-keys。网上比较推荐的是 userstat,一个 Google 贡献的 patch。
使用示例:
从满查询中的 sql 查看索引使用情况范例:

[root@master-server ~]# pt-index-usage --host=localhost --user=root --password=123456 /data/mysql/data/mysql-slow.log

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

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

7)pt-visual-explain
功能介绍:
格式化 explain 出来的执行计划按照 tree 方式输出,方便阅读。
用法介绍:
pt-visual-explain [OPTION…] [FILE…] 通过管道直接查看 explain 输出结果的范例:

mysql> select * from huanqiu.haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | wangshihuan |
|  3 | 王世博      |
| 10 | wangshiman  |
+----+-------------+
4 rows in set (0.00 sec)
 
[root@master-server ~]# mysql -uroot -p123456  -e "explain select * from huanqiu.haha" |pt-visual-explain
Warning: Using a password on the command line interface can be insecure.
Table scan
rows           4
+- Table
   table          haha
    
[root@master-server ~]# mysql -uroot -p123456  -e "explain select * from huanqiu.haha where id=3" |pt-visual-explain
Warning: Using a password on the command line interface can be insecure.
Bookmark lookup
+- Table
|  table          haha
|  possible_keys  PRIMARY
+- Constant index lookup
   key            haha->PRIMARY
   possible_keys  PRIMARY
   key_len        4
   ref            const
   rows           1

查看包含查询语句的 test.sql 文件的范例:

[root@master-server ~]# pt-visual-explain --connect /root/test.sql --user=root --password=123456

8)pt-config-diff
功能介绍:
比较 mysql 配置文件和服务器参数
用法介绍:
pt-config-diff [OPTION…] CONFIG CONFIG [CONFIG…] CONFIG 可以是文件也可以是数据源名称,最少必须指定两个配置文件源,就像 unix 下面的 diff 命令一样,如果配置完全一样就不会输出任何东西。
使用示例:
范例 1:查看本地和远程服务器的配置文件差异:

[root@master-server ~]# pt-config-diff h=localhost h=192.168.1.102 --user=root --password=123456
18 config differences
Variable                  master-server             slave-server
========================= ========================= =========================
binlog_checksum           NONE                      CRC32
general_log_file          /data/mysql/data/maste... /data/mysql/data/slave...
hostname                  master-server             slave-server
innodb_version            5.6.33                    5.6.34
log_bin_index             /data/mysql/data/maste... /data/mysql/data/slave...
log_slave_updates         OFF                       ON
relay_log_recovery        OFF                       ON
secure_file_priv                                    NULL
server_id                 101                       102
server_uuid               d8497104-d7a7-11e6-911... d8773e51-d7a7-11e6-911...
slave_net_timeout         3600                      5
slave_skip_errors         OFF                       ALL
sync_binlog               1                         0
sync_master_info          10000                     1
sync_relay_log            10000                     1
sync_relay_log_info       10000                     1
system_time_zone          CST                       EST
version                   5.6.33-log                5.6.34-log

范例 2:比较本地配置文件和远程服务器的差异:

[root@master-server ~]# pt-config-diff /usr/local/mysql/my.cnf h=localhost h=192.168.1.102 --user=root --password=123456
6 config differences
Variable                  /usr/local/mysql/my.cnf master-server      slave-ser
========================= ================= ================== ===============
binlog_checksum           none              NONE               CRC32
innodb_read_io_threads    1000              64                 64
innodb_write_io_threads   1000              64                 64
log_bin_index             master-bin.index  /data/mysql/dat... /data/mysql/...
server_id                 101               101                102
sync_binlog               1                 1                  0

9)pt-mysql-summary
功能介绍:
精细地对 mysql 的配置和 sataus 信息进行汇总,汇总后你直接看一眼就能看明白。
工作原理:连接 mysql 后查询出 status 和配置信息保存到临时目录中,然后用 awk 和其他的脚本工具进行格式化。OPTIONS 可以查阅官网的相关页面。
用法介绍:
pt-mysql-summary [OPTIONS] [– MYSQL OPTIONS] 使用示例:
汇总本地 mysql 服务器的 status 和配置信息:

[root@master-server ~]# pt-mysql-summary -- --user=root --password=123456 --host=localhost

10)pt-deadlock-logger
功能介绍:
提取和记录 mysql 死锁的相关信息
用法介绍:
pt-deadlock-logger [OPTION…] SOURCE_DSN
收集和保存 mysql 上最近的死锁信息,可以直接打印死锁信息和存储死锁信息到数据库中,死锁信息包括发生死锁的服务器、最近发生死锁的时间、死锁线程 id、死锁的事务 id、发生死锁时事务执行了多长时间等等非常多的信息。
使用示例:
查看本地 mysql 的死锁信息

[root@master-server ~]# pt-deadlock-logger  --user=root --password=123456 h=localhost D=test,t=deadlocks
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2017-01-11T11:00:33 188 0 0 root  192.168.1.101 huanpc checksums PRIMARY RECORD X w 1 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `member`, `city`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `huanpc`.`heihei` /*checksum table*/
localhost 2017-01-11T11:00:33 198 0 0 root  192.168.1.101 huanpc checksums PRIMARY RECORD X w 0 REPLACE INTO `huanpc`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'huanpc', 'heihei', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `member`, `city`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `huanpc`.`heihei` /*checksum table*/

11)pt-mext
功能介绍:
并行查看 SHOW GLOBAL STATUS 的多个样本的信息。
用法介绍:
pt-mext [OPTIONS] — COMMAND
原理:pt-mext 执行你指定的 COMMAND,并每次读取一行结果,把空行分割的内容保存到一个一个的临时文件中,最后结合这些临时文件并行查看结果。
使用示例:
每隔 10s 执行一次 SHOW GLOBAL STATUS,并将结果合并到一起查看

[root@master-server ~]# pt-mext  -- mysqladmin ext -uroot -p123456  -i10 -c3

12)pt-query-digest
功能介绍:
分析查询执行日志,并产生一个查询报告,为 MySQL、PostgreSQL、 memcached 过滤、重放或者转换语句。
pt-query-digest 可以从普通 MySQL 日志,慢查询日志以及二进制日志中分析查询,甚至可以从 SHOW PROCESSLIST 和 MySQL 协议的 tcpdump 中进行分析,如果没有指定文件,它从标准输入流(STDIN)中读取数据。
用法介绍:
pt-query-digest [OPTION…] [FILE] 解析和分析 mysql 日志文件
使用示例:(建议:当 log 很大的时候最好还是将日志文件移到其他机器上进行分析,以免过多耗费本机性能)
范例 1:分析本地的慢查询日志文件(本例是慢查询日志,也可以是 mysql 的其他日志)

[root@master-server ~]# pt-query-digest --user=root --password=123456 /data/mysql/data/mysql-slow.log
 
# 260ms user time, 30ms system time, 24.85M rss, 204.71M vsz
# Current date: Mon Jan 16 13:20:39 2017
# Hostname: master-server
# Files: /data/mysql/data/mysql-slow.log
# Overall: 18 total, 2 unique, 0 QPS, 0x concurrency _____________________
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           812s      2s     92s     45s     80s     27s     52s
# Lock time              0       0       0       0       0       0       0
# Rows sent              0       0       0       0       0       0       0
# Rows examine           0       0       0       0       0       0       0
# Query size         1.10k      62      63   62.56   62.76    0.50   62.76
 
# Profile
# Rank Query ID           Response time  Calls R/Call  V/M   Item
# ==== ================== ============== ===== ======= ===== =============
#    1 0x50B84661D4CCF34B 467.9075 57.6%    10 46.7907 16.48 CREATE DATABASE `huanqiu`
#    2 0x9CC34439A4FB17E3 344.2984 42.4%     8 43.0373 16.22 CREATE DATABASE `huanpc`
 
# Query 1: 0 QPS, 0x concurrency, ID 0x50B84661D4CCF34B at byte 2642 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 16.48
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         55      10
# Exec time     57    468s      2s     92s     47s     80s     28s     52s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    55     630      63      63      63      63       0      63
# String:
# Hosts
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  #######
#  10s+  ################################################################
CREATE DATABASE IF NOT EXISTS `huanqiu` /* pt-table-checksum */\G
 
# Query 2: 0 QPS, 0x concurrency, ID 0x9CC34439A4FB17E3 at byte 3083 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 16.22
# Time range: all events occurred at 2017-01-11 11:00:33
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         44       8
# Exec time     42    344s      2s     82s     43s     80s     26s     56s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    44     496      62      62      62      62       0      62
# String:
# Hosts
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  #########
#  10s+  ################################################################
CREATE DATABASE IF NOT EXISTS `huanpc` /* pt-table-checksum */\G
 
整个输出分为三大部分:
1)整体概要(Overall)
这个部分是一个大致的概要信息(类似 loadrunner 给出的概要信息),通过它可以对当前 MySQL 的查询性能做一个初步的评估,比如各个指标的最大值(max),平均值(min),95%分布值,中位数(median),标准偏差(stddev)。
这些指标有查询的执行时间(Exec time),锁占用的时间(Lock time),MySQL 执行器需要检查的行数(Rows examine),最后返回给客户端的行数(Rows sent),查询的大小。
 
2)查询的汇总信息(Profile)
这个部分对所有“重要”的查询(通常是比较慢的查询)做了个一览表。
每个查询都有一个 Query ID,这个 ID 通过 Hash 计算出来的。pt-query-digest 是根据这个所谓的 Fingerprint 来 group by 的。
Rank 整个分析中该“语句”的排名,一般也就是性能最常的。
Response time  “语句”的响应时间以及整体占比情况。
Calls 该“语句”的执行次数。
R/Call 每次执行的平均响应时间。
V/M 响应时间的差异平均对比率。
在尾部有一行输出,显示了其他 2 个占比较低而不值得单独显示的查询的统计数据。
 
3)详细信息
这个部分会列出 Profile 表中每个查询的详细信息:
包括 Overall 中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。
pt-query-digest 还有很多复杂的操作,这里就不一一介绍了。比如:从 PROCESSLIST 中查询某个 MySQL 中最慢的查询:

范例2:重新回顾满查询日志,并将结果保存到 query_review 中,注意 query_review 表的表结构必须先建好,表结构如下:

mysql> use test;
Database changed
 
mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)
  
mysql> select * from query_review;
Empty set (0.00 sec)
  
[root@master-server ~]# pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_review /data/mysql/data/mysql-slow.log
 
mysql> select * from query_review;
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
| checksum | fingerprint | sample | first_see
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
| 5816476304744969035 | create database if not exists `huanqiu` | CREATE DATABASE IF NOT EXISTS `huanqiu` /* pt-table-checksum */ | 2017-01-1
| 11295947304747079651 | create database if not exists `huanpc` | CREATE DATABASE IF NOT EXISTS `huanpc` /* pt-table-checksum */ | 2017-01-1
+----------------------+------------------------------------------+-----------------------------------------------------------------+----------
2 rows in set (0.00 sec)

从 tcpdump 中分析:

[root@master-server ~]# tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes
 
然后打开另一个终端窗口:
[root@master-server ~]# pt-query-digest --type tcpdump mysql.tcp.txt
Pipeline process 3 (TcpdumpParser) caused an error: substr outside of string at /usr/bin/pt-query-digest line 3628, <> chunk 93.
Will retry pipeline process 2 (TcpdumpParser) 100 more times.
 
# 320ms user time, 20ms system time, 24.93M rss, 204.84M vsz
# Current date: Mon Jan 16 13:24:50 2017
# Hostname: master-server
# Files: mysql.tcp.txt
# Overall: 31 total, 4 unique, 4.43 QPS, 0.00x concurrency _______________
# Time range: 2017-01-16 13:24:43.000380 to 13:24:50.001205
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           30ms    79us     5ms   967us     4ms     1ms   159us
# Rows affecte          14       0       2    0.45    1.96    0.82       0
# Query size         1.85k      17     200   61.16  192.76   72.25   17.65
.........

13)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 分钟

[root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --run-time 10m --host=192.168.1.102
2017-01-16T13:32:31 slave running 0 seconds behind
2017-01-16T13:32:31 STOP SLAVE until 2017-01-16T13:33:31 at master position mysql-bin.000005/102554361

范例 2:使从落后主 1 分钟,并每隔 15 秒钟检测一次,运行 10 分钟:

[root@master-server ~]# pt-slave-delay --user=root --password=123456 --delay 1m --interval 15s --run-time 10m --host=192.168.1.102
2017-01-16T13:38:22 slave running 0 seconds behind
2017-01-16T13:38:22 STOP SLAVE until 2017-01-16T13:39:22 at master position mysql-bin.000005/102689359

14)pt-slave-restart
功能介绍:
监视 mysql 复制错误,并尝试重启 mysql 复制当复制停止的时候
用法介绍:
pt-slave-restart [OPTION…] [DSN] 监视一个或者多个 mysql 复制错误,当从停止的时候尝试重新启动复制。你可以指定跳过的错误并运行从到指定的日志位置。
使用示例:
范例 1:监视 192.168.1.101 的从,跳过 1 个错误

[root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --skip-count=1

范例 2:监视 192.168.1.101 的从,跳过错误代码为 1062 的错误。

[root@master-server ~]# pt-slave-restart --user=root --password=123456 --host=192.168.1.101 --error-numbers=1062

15)pt-diskstats
功能介绍:
是一个对 GUN/LINUX 的交互式监控工具
用法介绍:
pt-diskstats [OPTION…] [FILES] 为 GUN/LINUX 打印磁盘 io 统计信息,和 iostat 有点像,但是这个工具是交互式并且比 iostat 更详细。可以分析从远程机器收集的数据。
使用示例:
范例 1:查看本机所有的磁盘的状态情况:

[root@master-server ~]# pt-diskstats

范例 2:只查看本机 sdc1 磁盘的状态情况:

[root@master-server ~]# pt-diskstats  --devices-regex vdc1
  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  0.9 vdc1       0.0     0.0     0.0     0%    0.0     0.0     5.9     4.0     0.0     0%    0.0     1.0   0%      0     5.9    0.6   0.4
  1.0 vdc1       0.0     0.0     0.0     0%    0.0     0.0     2.0     6.0     0.0    33%    0.0     0.7   0%      0     2.0    0.0   0.7

16)pt-summary
功能介绍:
友好地收集和显示系统信息概况,此工具并不是一个调优或者诊断工具,这个工具会产生一个很容易进行比较和发送邮件的报告。
用法介绍:
pt-summary
原理:此工具会运行和多命令去收集系统状态和配置信息,先保存到临时目录的文件中去,然后运行一些 unix 命令对这些结果做格式化,最好是用 root 用户或者有权限的用户运行此命令。
使用示例:
查看本地系统信息概况

[root@master-server ~]# pt-summary

17)pt-stalk
功能介绍:
出现问题的时候收集 mysql 的用于诊断的数据
用法介绍:
pt-stalk [OPTIONS] [– MYSQL OPTIONS] pt-stalk 等待触发条件触发,然后收集数据帮助错误诊断,它被设计成使用 root 权限运行的守护进程,因此你可以诊断那些你不能直接观察的间歇性问题。默认的诊断触发条件为 SHOW GLOBAL STATUS。也可以指定 processlist 为诊断触发条件 ,使用–function 参数指定。
使用示例:
范例 1:指定诊断触发条件为 status,同时运行语句超过 20 的时候触发,收集的数据存放在目标目录/tmp/test 下:

[root@master-server ~]# pt-stalk  --function status --variable Threads_running --threshold 20 --dest /tmp/test  -- -uroot -p123456  -h192.168.1.101

范例 2:指定诊断触发条件为 processlist,超过 20 个状态为 statistics 触发,收集的数据存放在/tmp/test 目录下:

[root@master-server ~]# pt-stalk  --function processlist --variable State --match statistics --threshold 20 --dest /tmp/test -- -uroot -p123456  -h192.168.1.101
.......
2017_01_15_17_31_49-hostname
2017_01_15_17_31_49-innodbstatus1
2017_01_15_17_31_49-innodbstatus2
2017_01_15_17_31_49-interrupts
2017_01_15_17_31_49-log_error
2017_01_15_17_31_49-lsof
2017_01_15_17_31_49-meminfo

18)pt-archiver
功能介绍:
将 mysql 数据库中表的记录归档到另外一个表或者文件
用法介绍:
pt-archiver [OPTION…] –source DSN –where WHERE
这个工具只是归档旧的数据,不会对线上数据的 OLTP 查询造成太大影响,你可以将数据插入另外一台服务器的其他表中,也可以写入到一个文件中,方便使用 source 命令导入数据。另外你还可以用它来执行 delete 操作。特别注意:这个工具默认的会删除源中的数据!!
使用示例:
范例 1:将 192.168.1.101 上的 huanqiu 库的 haha 表 id 小于 10 的记录转移到 192.168.1.102 上的 wangshibo 库下的 wang_test 表内,并归档到/var/log/haha_archive_20170115.log 文件中(注意:转移前后,两张表对应转移字段要相同,字段属性最好也要相同;)

源数据库机器 192.168.1.101 的 huanqiu 库下的 haha 表在转移前的信息:
mysql> select * from huanqiu.haha;                                                                                                            
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.01 sec)
 
目标数据库机器 192.168.1.102 的 wangshibo 库下的 wang_test 表在转移前的信息:
mysql> select * from wangshibo.wang_test;
+------+-----------+
| id   | name      |
+------+-----------+
|   20 | guominmin |
|   21 | gaofei    |
|   22 | 李梦楠    |
+------+-----------+
3 rows in set (0.00 sec)
 
接着在 192.168.1.101 机器上执行转移命令:
[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --dest h=192.168.1.102,D=wangshibo,t=wang_test --file '/var/log/haha_archive_20170115.log' --where "id<=10"  --commit-each
 
上面命令执行成功后,再次观察转移前后信息
发现源数据库机器 192.168.1.101 的 huanqiu.haha 表数据在转移后,源数据也删除了!
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)
 
查看归档日志:
[root@master-server ~]# tail -f /var/log/haha_archive_20170115.log
1   changbo
2   wangpengde
4   guocongcong
5   kevin
8   mamin
9   shihonge
 
目标数据库 192.168.1.102 的 wangshibo.wang_test 表内已经移转到了新数据
mysql> select * from wangshibo.wang_test;
+------+-------------+
| id   | name        |
+------+-------------+
|   20 | guominmin   |
|   21 | gaofei      |
|   22 | 李梦楠       |
|    1 | changbo     |
|    2 | wangpengde  |
|    4 | guocongcong |
|    5 | kevin       |
|    8 | mamin       |
|    9 | shihonge    |
+------+-------------+
9 rows in set (0.00 sec)

范例 2:将 192.168.1.101 上的 huanqiu 库的 haha 表里 id 小于 10 的记录归档到 haha_log_archive_2017.10.10.log 文件中:

mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.00 sec)
 
[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --file 'haha_log_archive_2017.10.10.log' --where "id<=10" --commit-each
 
转移后的源数据已被删除
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)
 
查看归档文件
[root@master-server ~]# cat haha_log_archive_2017.10.10.log
1   changbo
2   wangpengde
4   guocongcong
5   kevin
8   mamin
9   shihonge

范例 3:删除 192.168.1.101 上的 huanqiu 库的 haha 表中 id 小于 10 的记录:

mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
|  1 | changbo       |
|  2 | wangpengde    |
|  4 | guocongcong   |
|  5 | kevin         |
|  8 | mamin         |
|  9 | shihonge      |
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
8 rows in set (0.00 sec)
 
[root@master-server ~]# pt-archiver --source h=192.168.1.101,D=huanqiu,t=haha --user=root --password=123456 --purge --where 'id<=10' --no-check-charset

再次查看,发现数据已成功删除!
mysql> select * from huanqiu.haha;
+----+---------------+
| id | name          |
+----+---------------+
| 11 | zhanglei      |
| 15 | zhanghongmiao |
+----+---------------+
2 rows in set (0.00 sec)

19)pt-find
功能介绍:
查找 mysql 表并执行指定的命令,和 gnu 的 find 命令类似。
用法介绍:
pt-find [OPTION…] [DATABASE…] 默认动作是打印数据库名和表名
使用示例:
查找 192.168.1.101 中 1 天以前创建的 InnoDB 的表 ,并打印。

[root@master-server ~]# pt-find --ctime +1  --host=192.168.1.101 --engine InnoDB --user=root --password=123456
`huanpc`.`_heihei_new`
`huanpc`.`checksums`
`huanqiu`.`_haha_new`
`huanqiu`.`checksums`
`huanqiu`.`heartbeat`
`mysql`.`innodb_index_stats`
`mysql`.`innodb_table_stats`
`mysql`.`slave_master_info`
`mysql`.`slave_relay_log_info`
`mysql`.`slave_worker_info`

范例 2:查找 192.168.1.101 中 1 天以前更改过的数据库名字匹配%huanqiu%的并且引擎为 Myisam 的表,并将表的引擎更改为 Innodb 引擎。

先查找出 192.168.1.101 上 1 天以前更改过的数据库名字匹配%huanqiu%的并且引擎为 Myisam 的表
[root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456
`huanqiu`.`_haha_new`
`huanqiu`.`checksums`
`huanqiu`.`heartbeat`
[root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456
`huanpc`.`_heihei_new`
`huanpc`.`checksums`
 
再将查找出的表的引擎改为 Innodb
[root@master-server ~]# pt-find --ctime +2 --dblike huanqiu --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"
[root@master-server ~]# pt-find --ctime +2 --dblike huanpc --host=192.168.1.101 --engine Myisam --user=root --password=123456 --exec "ALTER TABLE %D.%N ENGINE=InnoDB"
 
最后再检查对应数据表的引擎

范例 3:查找 192.168.1.101 中 huanqiu 库和 huanpc 库中的空表,并删除。

[root@master-server ~]# pt-find --empty huanqiu huanpc --host=192.168.1.101 --user=root --password=123456  --exec-plus "DROP TABLE %s"

范例 4:查找 192.168.1.101 中超过 100M 的表

[root@master-server ~]# pt-find --tablesize +100M --host=192.168.1.101 --user=root --password=123456

20)pt-kill
功能介绍:
Kill 掉符合指定条件 mysql 语句
用法介绍:
pt-kill [OPTIONS] 加入没有指定文件的话 pt-kill 连接到 mysql 并通过 SHOW PROCESSLIST 找到指定的语句,反之 pt-kill 从包含 SHOW PROCESSLIST 结果的文件中读取 mysql 语句
使用示例:
范例 1:查找 192.168.1.101 数据库服务器运行时间超过 60s 的语句,并打印

[root@master-server ~]# pt-kill --busy-time 60 --print --host=192.168.1.101 --user=root --password=123456

范例 2:查找 192.168.1.101 数据库服务器运行时间超过 60s 的语句,并 kill

[root@master-server ~]# pt-kill --busy-time 60 --kill --host=192.168.3.135 --user=root --password=123456

范例 3:从 proccesslist 文件中查找执行时间超过 60s 的语句

[root@master-server ~]# mysql -uroot -p123456 -h192.168.1.101 -e "show processlist" > processlist.txt
Warning: Using a password on the command line interface can be insecure.
[root@master-server ~]# pt-kill --test-matching processlist.txt --busy-time 60 --print

IT 敢客 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:MySQL 主从同步检查工具 PT 使用介绍
喜欢 (2)
[313176056@qq.com]
分享 (0)
IT敢客
关于作者:
“我所做的一切都是为了方便我的生活~~~“
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址