解析binlog工具百家争鸣,最常用的是mysqlbinlog,各有千秋,对于DBA,唯手熟尔罢了
然而,有工具的地方就有江湖,故本文无意争论工具属优属劣,免得引起不成熟的争端
然而,有工具的地方就有江湖,故本文无意争论工具属优属劣,免得引起不成熟的争端
仅介绍pt-query-digest其实也是能用来解析Binlog,友好、可读性强、便于快速诊断故障
如果直接:
[root@ld88 mysqldata]# pt-query-digest --type binlog mysql-bin88.000189
将无法解析,需先做如下转换
[root@ld88 mysqldata]# mysqlbinlog mysql-bin88.000189 > mysql-bin88.000189.sql
看两个例子,以下为了排版,对输出做了大量裁减-_-!
㈠ 指定时间窗口
[root@ld88 mysqldata]# pt-query-digest --type binlog --since "2013-11-06 20:55:00" --until "2013-11-06 21:00:00" mysql-bin88.000189.sql
mysql-bin88.000189.sql: 17% 02:22 remain
mysql-bin88.000189.sql: 33% 01:58 remain
mysql-bin88.000189.sql: 47% 01:39 remain
mysql-bin88.000189.sql: 62% 01:12 remain
mysql-bin88.000189.sql: 83% 00:28 remain
# 160.9s user time, 8.8s system time, 23.62M rss, 150.04M vsz
# Current date: Thu Nov 7 15:37:19 2013
# Hostname: ld88
# Files: mysql-bin88.000189.sql
# Overall: 914 total, 31 unique, 3.06 QPS, 41.19kx concurrency ___________
# Time range: 2013-11-06 20:55:00 to 20:59:59
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 12316