Pt-query-digest parses the MySQLBinlog Log File

The binlog parsing tool is widely used. mysqlbinlog is the most commonly used tool. It has its own merits. For DBA, this article only introduces pt-query-digest, which can be used to solve the problem.

The binlog parsing tool is widely used. mysqlbinlog is the most commonly used tool. It has its own merits. For DBA, this article only introduces pt-query-digest, which can be used to solve the problem.

The binlog parsing tool is widely used. mysqlbinlog is the most commonly used tool.
However, there are rivers and lakes where tools are available. Therefore, this article does not intend to argue that tools are superior or inferior to avoid immature disputes.

Only pt-query-digest can be used to parse Binlog, Which is user-friendly, readable, and easy to diagnose faults quickly.

If direct:
[Root @ ld88 mysqldata] # pt-query-digest -- type binlog mysql-bin88.000189
It will not be parsed. You need to convert it as follows:
[Root @ ld88 mysqldata] # mysqlbinlog mysql-bin88.000189> mysql-bin88.000189. SQL

Let's look at two examples. Below we have made a lot of reductions in the output for typographical purposes -_-!

(I) specified time window

[Root @ ld88 mysqldata] # pt-query-digest -- type binlog -- since "20:55:00" -- until "21:00:00" mysql-bin88.000189. SQL
Mysql-bin88.000189. SQL: 17% remain
Mysql-bin88.000189. SQL: 33% remain
Mysql-bin88.000189. SQL: 47% remain
Mysql-bin88.000189. SQL: 62% remain
Mysql-bin88.000189. SQL: 83% remain

#160.9 s user time, 8.8 s system time, 23.62 M rss, 150.04 M 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: 20:55:00 to 20:59:59
# Attribute total min max avg 95% stddev median
#===================================================== ======================================
# Exec time 12316866 s 13349 s 13639 s 13476 s 13443 s 145 s 13443 s
# Query size 442.35 k 6 35.33 k 434.29 1012.63 1.88 k 143.84
# Error code 0 0 0 0 0 0 0

# Profile
# Rank Query ID Response time callr/Call V/M Item
#===================================================== ======================================
#1 0x972834777a1d4a3f 3739234.0000 30.4% 277 13499.0397 UPDATE tbBlogArticleStat?
#2 0xF5B3ADEC45DB5266 1099848.0000 8.9% 82 13412.7805 1.39 UPDATE tbBlogArticleStat?
#3 0xC05BF1F3A8344559 1099848.0000 8.9% 82 13412.7805 1.39 UPDATE tbBlogArticleChart
#4 0xA85CE0CC3154666E 1024921.0000 8.3% 76 13485.8026 UPDATE tbBlogTag
#5 0x7c12b8c66b450b73 822705.0000 6.7% 61 13486.9672 1.26 INSERT tbBlogTagArticle
#6 0xE8059EB28F9F68AA 752337.0000 6.1% 56 13434.5893 1.39 INSERT tbBlogArticleVote
#7 0x01FCF322381E8B7E 404606.0000 3.3% 30 13486.8667 INSERT tbBlogArticleDayClick
#8 0xD5C00F71DB944F6C 404606.0000 3.3% 30 13486.8667 UPDATE tbBlogSubArticleStatist
#9 0x6EFA3A2E4EC6B202 404606.0000 3.3% 30 13486.8667 UPDATE tbBlogMemberSort
#10 0xE14AB2C787449950 404606.0000 3.3% 30 13486.8667 UPDATE tbBlogMemberStat
#11 0x000064e44be970cdd 404606.0000 3.3% 30 13486.8667 1.28 INSERT tbBlogArticleChart
#12 0xCFEB2F244234CE05 404605.0000 3.3% 30 13486.8333 1.28 INSERT tbBlogArticleStat?
#13 0xCDB381C90AF965D4 404604.0000 3.3% 30 13486.8000 1.28 INSERT tbBlogArticle?
#14 0xF7D29C9021590977 162011.0000 1.3% 12 13500.9167 1.30 UPDATE tbBlogArticleChart
#15 0xfba7bd32b7692132 134777.0000 1.1% 10 13477.7000 1.23 INSERT tbBlogTag
#16 0xb2317c2de1e871_108184.0000 0.9% 8 13523.0000 UPDATE tbBlogMemberStat
# MISC 0 xMISC 540762.0000 4.4% 40 13519.0500 0.0 <14 ITEMS>

# Query 1: 0.93 QPS, 12.59kx concurrency, ID 0x972810977a1d4a3f at byte 218115626
# This item is wrongly ded in the report because it matches -- limit.
# Scores: V/M = 1.54
# Time range: 20:55:00 to 20:59:57
# Attribute pct total min max avg 95% stddev median
#===================================================== ======================================
# Count 30 277
# Exec time 30 3739234 s 13351 s 13639 s 13499 s 13443 s 144 s 13443 s
# Query size 10 44.59 k 144 201 164.84 192.76 17.53
# Error code 0 0 0 0 0 0 0 0 0
# Query_time distribution
# 1us
# 10us
# 100us
#1 ms
#10 ms
#100 ms
#1 s
#10 s + #################################### ############################
# Tables
# Show table status like 'tbblogarticlestat0022 '\ G
# Show create table 'tbblogarticlestat0022 '\ G
Update tbBlogArticleStat0022 set PrevArticleID = '0', PrevArticleAppearTime = '', NextArticleID = '000000', NextArticleAppearTime = '2017-03-14 19:59:01 'where ArticleID = '000000' \ G
# Converted for EXPLAIN
# EXPLAIN /*! 50100 PARTITIONS */
Select PrevArticleID = '0', PrevArticleAppearTime = '', NextArticleID = '000000', NextArticleAppearTime = '2017-03-14 19:59:01 'from tbBlogArticleStat0022 where ArticleID = '2016' \ G

For more details, please continue to read the highlights on the next page:

Related reading:

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

Implementation and performance of MySQL binlog pre-allocation