Shulou(Shulou.com)06/01 Report--

The original indicates the source:

1. The question leads to:

Sometimes you need to judge whether there are big things in binlog, such as solving master-slave latency.

In the case of high. Usually we use mysqlbinlog to find it, but there is a problem. We use mysqbinlog

Is there a quick way to find trouble? Of course, use shell scripts to do some formatting, also

You can find it. Here we introduce a tool called infobin to do it. I wrote it myself and finished it in C language.

2. What can infobin do?

Find things that are larger than the amount of logs you specify, which are generally defined as big things, and give their location, which can be output in mysqlbinlog through the location.

Find big things in

-- find out which time period in a binlog generates the most logs

-- parsing the distribution of event generated by binlog and some table statement information

-- the number of logs generated per second for this binlog file, the maximum event size, the total number of transactions, and so on.

3. How to use it

-- USAGE:./infobin [binlogfile] [piece] [bigtrxsize]

[binlogfile]: binlogfile!

[piece]: how many piece will split,is a Highly balanced histogram

Find which time generate biggest binlog. (must:piece256 (bytes))

For example, we need to analyze the big things in 72mysql-bin.000586 that are larger than 600K and divide them into 10 pieces.

The period for determining the amount of log generated can be as follows:

. / infobin 72mysql-bin.000586 10 600000 > log6.log

Here we focus on the analysis.

Piece: this is a sharding parameter. For example, if a 1G binlog is divided into 10 slices, then one slice is about 100m. If

Shard 1 is generated within 100 seconds, while Shard 2 is generated within 10 seconds, so it can be explained that the period of Shard 2

The resulting log volume change is actually a 100m histogram of balanced size divided into 10 buckets.

The shorter the time of watching which movie is, the busier it will be.

Such as:

(4) Time:1487561012-1487561480 (468 (s) piece:107374204 (bytes) [104857.625 (kb)]

(5) Time:1487561480-1487562682 (1202 (s)) piece:107374204 (bytes) [104857.625 (kb)]

The number of logs generated during fragment 5 is small, while that generated during fragment 4 is large. Here is the number of seconds since the new era.

You can use the LINUX command to convert such as: date-s "@ 1487035999"

Bigtrxsize: this is the specified size the amount of binlog generated will be output at the end, note that the size is bytes bytes, because row

The binlog format will record the actual data. If it is update, of course * 2. For example, it is expected that each row of data will be.

1000 bytes, you want to output something whose delete is greater than 1000 lines, that's about 1000mm 1000004max 3x 1330000 (bytes)

Left and right, if it is update * 2.

. / infobin 72mysql-bin.000586 10 1330000 > log.log

(10 is piece)

This is a variable parameter to define what is a big thing.

4. How to get the tools

You can obtain it through Baidu Cloud disk.

Http://pan.baidu.com/s/1jHIWUN0

Only the LINUX64 version of the

Restrictions:

-- can only be used on Little_endian. Compilation is compiled on LINUX gcc.

-- load data infile event is not tested.

You can't read out row event statements because it's not that complicated to write.

-- you can read out statements in statement format, but truncate 35 bytes for simplicity to facilitate output

All of these things are found in mysqlbinlog parsing.

If you want to judge something big, you need to use the row format binlog, otherwise the judgment may be wrong.

5. Output explanation:

The output is divided into three segments

1. Now begin part:

It is clear at a glance that one thing needs to be explained, Warning:Check This binlog is not closed! Indicates that this binlog is currently using binlog

2. Detail now part:

This section is the output of a detailed binlog event

-1.

Event starts with >, but the event of a thing I use-- >-> to differentiate is easier to read if

After a careful study of event, these event will be no stranger.

-- 2.

Pos: current event location

N_pos: next event location

Gtid: of course, it's GTID. If it's anonymous, it's ANONYMOUS. Its GTID is 0.

Time: the number of seconds since the new era can be converted with the LINUX command, such as: date-s "@ 1487035999"

Event_size: how big is this event

The thing number part of Gno:gtid, which I use to mark them as one thing.

TABLE_ID: is unique to the line format, which is used to ensure the correctness of slave replication

Use_db: under which data does use database currently exist by default, which is unique to query event?

DB_NAME: this is unique to map event and row format. It records the database where the table is located, which is different from Use_db.

Statment (35b-trun): a statement recorded in query event does a 35-byte phase to facilitate output

/ *! Trx beginnings scratch: indicates that this is the beginning of a thing. If it is a gtid mode, you need to push an event forward, because gtid event is also counted in the thing.

/ *! Trx end*/: is naturally the end point of things.

It is also consistent in mysqlbinlog, for example:

> Gtid Event:Pos:194 (0Xc2) N_pos:259 (0X103) Time:1487035999 Event_size:65 (bytes)

Gtid:4a6f2a67-5d87-11e6-a6bd-0c29a879a3:1100463

[root@testmy ~] # date-s "@ 1487035999"

Tue Feb 14 09:33:19 CST 2017

Corresponding to the following parts of mysqlbinlog:

# at 194

# 170214 9:33:19 server id 93157 end_log_pos 259 CRC32 0xb664a0c6 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '4a6f2a67-5d87-11e6mura6bdmura000c29a879a34a6f2a67 1100463 /

3. Total now part:

This part is the final summary, which gives:

Trx total [counts]: the total number of things

Event total [counts]: total number of event

Avg binlog size (/ sec): average size of binlog generated per second

Avg binlog size (/ min): average size of binlog generated per minute

-Piece view: get a highly balanced histogram based on the user-specified piece size. This histogram is used to find out whether there is a certain period of time when the binlog is particularly large.

-Large than xxx (bytes) trx: there are about xxx BYTES things, and there will be a summary at the end. This section gives the starting position of big things trx_begin_p.

Where it ends, trx_end_p.

Liezi is as follows:

-Total now-

Trx total [counts]: 592420

Event total [counts]: 3788611

Max trx event size:14344 (bytes) Pos:858067571 [0X33251273]

Avg binlog size (/ sec): 261251.109 (bytes) [255.128 (kb)]

Avg binlog size (/ min): 15675067.000 (bytes) [15307.683 (kb)]

-- Piece view:

(1) Time:1487560299-1487560543 (244s) piece:107374204 (bytes) [104857.625 (kb)]

(2) Time:1487560543-1487560751 (208s) piece:107374204 (bytes) [104857.625 (kb)]

(3) Time:1487560751-1487561012 (261s) piece:107374204 (bytes) [104857.625 (kb)]

(4) Time:1487561012-1487561480 (468 (s) piece:107374204 (bytes) [104857.625 (kb)]

(5) Time:1487561480-1487562682 (1202 (s)) piece:107374204 (bytes) [104857.625 (kb)]

(6) Time:1487562682-1487563492 (810s) piece:107374204 (bytes) [104857.625 (kb)]

(7) Time:1487563492-1487563723 (231s) piece:107374204 (bytes) [104857.625 (kb)]

(8) Time:1487563723-1487563951 (228s) piece:107374204 (bytes) [104857.625 (kb)]

(9) Time:1487563951-1487564159 (208s) piece:107374204 (bytes) [104857.625 (kb)]

(10) Time:1487564159-1487564409 (250s) piece:107374204 (bytes) [104857.625 (kb)]

-Large than 700000 (bytes) trx:

(1) Trx_size:719621 (bytes) [702.755 (kb)] trx_begin_p: 60579814 [0X39C5FE6] trx_end_p: 61299435 [0X3A75AEB]

(2) Trx_size:719771 (bytes) [702.901 (kb)] trx_begin_p: 177760551 [0XA986927] trx_end_p: 178480322 [0XAA364C2]

(3) Trx_size:719779 (bytes) [702.909 (kb)] trx_begin_p: 314334603 [0X12BC5D8B] trx_end_p: 315054382 [0X12C7592E]

(4) Trx_size:719803 (bytes) [702.933 (kb)] trx_begin_p: 317542845 [0X12ED51BD] trx_end_p: 318262648 [0X12F84D78]

(5) Trx_size:719811 (bytes) [702.940 (kb)] trx_begin_p: 367838322 [0X15ECC472] trx_end_p: 368558133 [0X15F7C035]

(6) Trx_size:719765 (bytes) [702.896 (kb)] trx_begin_p: 370735395 [0X1618F923] trx_end_p: 371455160 [0X1623F4B8]

(7) Trx_size:719755 (bytes) [702.886 (kb)] trx_begin_p: 433385835 [0X19D4F16B] trx_end_p: 434105590 [0X19DFECF6]

(8) Trx_size:719827 (bytes) [702.956 (kb)] trx_begin_p: 446989814 [0X1AA485F6] trx_end_p: 447709641 [0X1AAF81C9]

(9) Trx_size:719973 (bytes) [703.099 (kb)] trx_begin_p: 748301414 [0X2C9A2C66] trx_end_p: 749021387 [0X2CA528CB]

(10) Trx_size:719827 (bytes) [702.956 (kb)] trx_begin_p: 915609664 [0X36931840] trx_end_p: 916329491 [0X369E1413]

(11) Trx_size:719765 (bytes) [702.896 (kb)] trx_begin_p: 918974063 [0X36C66E6F] trx_end_p: 919693828 [0X36D16A04]

(12) Trx_size:719797 (bytes) [702.927 (kb)] trx_begin_p: 1029346825 [0X3D5A9609] trx_end_p: 1030066622 [0X3D6591BE]

Total large trx count size (kb): # 8435.053 (kb)

At a glance, it is obvious that Time:1487561480-1487562682 (1202 (s)) Time:1487562682-1487563492 (810s)

Less logs are generated during this period, and more are generated in other periods. Average log generation of about 15307.683 (kb) per minute

If you need to analyze what the first big thing is, you just need to find the location 60579814 in the output of mysqlbinlog to see what it is.

Mysqlbinlog-base64-output='decode-rows'-vv-start-position=60579814-stop-position=61299435 72mysql-bin.000586 > log.log

OK, notice that there is a lack of an event that generates gtid. Isn't it much easier to find gtid in the previous event?

To learn the knowledge reference of binlog event:

Http://blog.itpub.net/7728585/viewspace-2133188/ parsing MYSQL BINLOG binary format (1)-- preparation

Http://blog.itpub.net/7728585/viewspace-2133189/ parsing MYSQL BINLOG binary format (2)-- FORMAT_DESCRIPTION_EVENT

Http://blog.itpub.net/7728585/viewspace-2133321/ parsing MYSQL BINLOG binary format (3)-- QUERY_EVENT

Http://blog.itpub.net/7728585/viewspace-2133429/ parsing MYSQL BINLOG binary format (4)-- TABLE_MAP_EVENT

Http://blog.itpub.net/7728585/viewspace-2133463/ parsing MYSQL BINLOG binary format (5)-- WRITE_ROW_EVENT

Http://blog.itpub.net/7728585/viewspace-2133469/ parsing MYSQL BINLOG binary format (6)-- UPDATE_ROW_EVENT/DELETE_ROW_EVENT

Http://blog.itpub.net/7728585/viewspace-2133502/ parsing MYSQL BINLOG binary format (7)-- Xid_log_event/XID_EVENT

Http://blog.itpub.net/7728585/viewspace-2133506/ parses MYSQL BINLOG binary format (8)-- GTID_LOG_EVENT/ANONYMOUS_GTID_LOG_EVENT and others

Http://blog.itpub.net/7728585/viewspace-2133534/ parses MYSQL BINLOG binary format (9)-- infobin parses binlog help documents

Http://blog.itpub.net/7728585/viewspace-2133537/ parses MYSQL BINLOG binary format (10)-- question and answer

Author Wechat:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.