Tcpdump catch sql[Turn]

Transferred from: http://www.cnblogs.com/LMySQL/p/5060604.html

Preface: Suppose that if there is a server dozens of links suddenly reached thousands of links, show processlist,general_log, and slow query log these are not used, how do you take these links to the SQL situation to understand clearly, if you think that the use of those can not be used, This impossible thing, always leave what clues, since said can not use, then can not do it, the pattern Tucson broken, in fact, there is an artifact can be used.

Here's a look at Tcpdump's artifact.

In string form
[[email protected] ~]# tcpdump-i em1 Port 3306-l-S 0-w-| Strings | Grep-a 5 "Select" >/tmp/tcpdump.txttcpdump:listening on Em1, Link-type EN10MB (Ethernet), capture size 65535 bytes^c 699 Packets captured699 Packets received by FILTER0 packets dropped by kernel[[email protected] ~]# cat/tmp/tcpdump. TXT | Moreselect 1[email protected]8=! Pselect @ @session. tx_read_only8=!| @ @session. tx_read_only8=npselect DISTINCT opp.course_big_id from Opp left JOIN app on opp.id = app.id where student_id = 259585937 and (Editable_code = ' chance_allocated ' or (Editable_code = ' chance_feedback ' and Effective_code = ' DATA_CODE_EF Fective ') and delete_state = ' ACTIVE ' and opp.course_big_id not in (+) 8=n|oppoppcourse_big_idcourse_big_id--you can Can feel the format is not good-looking, can grab the package combined with Pt-query-digest to analyze the package [[email protected] ~]# tcpdump-s 65535-x-nn-q-tttt-i em1-c Port 3306 &G T /tmp/mysq.tcp.txttcpdump:verbose output suppressed, use-v OR-VV for full protocol decodelistening on EM1, LInk-type EN10MB (Ethernet), capture size 65535 bytes1000 packets captured1000 packets received by FILTER0 packets dropped by kernel[[email protected] ~]# Cat/tmp/mysq.tcp.txt |  more2015-12-20 11:49:02.521497 IP 192.168.1.25.3306 > 192.168.1.26.33514:tcp 4625 0x0000:4508 1245 719e 4000 4006    3389 c0a8 0119 0x0010:c0a8 011a 0cea 82ea fb97 e0b4 bcd8 0218 0x0020:8018 007a 95bb 0000 0101 080a 531c 97fc 0x0030:7677 0229 5200 00ae 002e 2576 5602 5abd 0x0040:2500 5100 0000 8802 3409 0800 bf05 000 0x0050:0000 0 0300 0024 0000 0000 0000 0100 0x0060:0020 4000 0000 0006 0373 7464 0421 0021 0x0070:0021 0005 0653 5953 5445 4 d0c fe63 726d 0x0080:0042 4547 494e 05bd 8242 ae00 00af 002e 0x0090:2576 5613 5abd 2500 ad00 0000 3503 3409 0 x00a0: 0000 0a58 0000 0000 0300 0363 726d 0014 0x00b0: 745f 6f6e 6c69 6e65 5f6f 7070 6f72 7475 0x00c0: 6e69 7479    002c 0803 0308 0303 0f0f 0f0f 0x00d0:0f0f 0ffc 0f0f 0f0f 0f0f 0f03 0f0f 12030x00e0:120f 0312 0f0f 0f12 0f12 0f0f 0f0f 0f0f[[email protected] ~]# pt-query-digest--type tcpdump/tmp/mysq.tcp.t XT # 410MS User Time, 20ms system time, 27.92M RSS, 216.83M vsz# current Date:sun Dec 11:50:58 2015# Hostname:localho st# Files:/tmp/mysq.tcp.txt# overall:207 Total, 9 unique, 14.88 QPS, 0.01x concurrency _____________# time range:2015-1 2-20 11:49:02.621793 to 11:49:16.536897# Attribute Total min max avg 95% StdDev median# ======= ===== ======= ======= ======= ======= ======= ======= =======# Exec time 87ms 23us 33ms 419us 260u       s 3ms 31us# Rows affecte 0 0 0 0 0 0 0# Query size 53.68k 8 1.83k 265.56 1012.63 398.95 28.75# Warning coun 0 0 0 0 0 0 0# Pro file# Rank Query ID Response time Calls r/call v/m item# = ================== ============= ===== ====== = =    === ===============#1 0x9d4b9366e8a0f276 0.0331 38.2% 1 0.0331 0.00 SELECT xxxx# 2 0x8a9c2066e45e4b88 0.0318 36.7% 1 0.0318 0.0 0 Select xxxxx# 3 0x7c4bc7d69a3993ef 0.0085 9.8% 0.0003 0.00 SELECT xxxxx# 4 0x081eb4df07f38a22 0.0055 6. 3% 0.0002 0.00 SELECT adfadf# 5 0x16219655761820a2 0.0023 2.7% 6 0.0000 0.00 select# 9B1 0.0020 2.3% 9 0.0002 0.00 SELECT t_adfadfadsf# MISC 0xMISC 0.0035 4.0% 0.0000 0.0 <3 I tems># Query 1:0 QPS, 0x concurrency, ID 0x9d4b9366e8a0f276 at byte 1224863 __# scores:v/m = 0.00# time Range:all ev Ents occurred at 2015-12-20 11:49:11.484563# Attribute pct Total min max avg 95% StdDev median# = = =    ========= = = = ======= ======= ======= ======= ======= ======= =======# Count 0 # Exec Time 33ms  33ms 33ms 33ms 33ms 0 33ms# Rows affecte 0 0 0 0 0 0 0 0# Query size 2 1.59K 1.59k 1.59k 1.59k 1.59k 0 1.59k# Warning coun 0 0 0 0 0 0 0 0# string:# Hosts 192.168.1.235# query_time distribution# 1us# 10us# 100us# 1ms# 10ms ###################### ########################################### 100ms# 1s# 10s+# tables# show TABLE STATUS like aaa\g# show CREATE T ABLE ' AAA ' \g# Show Table STATUS like ' BBB ' \g# show CREATE TABLE ' BBB ' \g# EXPLAIN/*!50100 partitions*/select Count (* ) from the AAA Skyopp left join BBB topp on aaaopp.sky_potential_opportunity_id = topp.id where Skyopp.serving_state_code = ' Serving ' and skyopp.account is null and skyopp.area_id = 2 and First_visit_time <= ' 2015-12-20 23:59:  First_visit_time >= ' 2015-09-20 00:00:00 ' and office_id in (4, 24, 25,  1003, 1004, 2020, 2021, 2028, 2029, 2039, 2040, 10043, 10045) and project_id in (11, 13, 10099  , 10504, 11, 13, 10099, 10504, 11, 13, 10099, 10504, 11, 13, 10099, 10504, 11, 10099, 10504/* Omitted ... */) and Last_visit_type_code in (' First_visit_type ', ' the  _week_return ', ' Next_week_return ', ' Second_visit_type ') and Topp.source_code in (' Cs_leyu ', ' Cs_bbs ')  , ' Cs_push ', ' cs_qq ') and get_count<=22 and legion_id in (1, 3, 4, 5, 6, 7, 8    , 9, ten, one, A, a, a, (+),----------* and (Abandon_time >= ' 2015-12-20 23:59:59.774 ' or abandon_time <= ' 2015-12-20 00:00:00.774 ' or abandon_time is null) ORDER by Skyopp.first_visit_time Desc\g

Can be found: there is a logical pit Father statement (Abandon_time >= ' 2015-12-20 23:59:59.774 ' or abandon_time <= ' 2015-12-20 00:00:00.774 '), the intention is to query the day Time, turned into a query yesterday before, this is a module statement of the old project

Tcpdump options:

Copy code-a displays each packet in ASCII mode (does not display the link-Layer header information in the packet). When crawling a packet containing web page data, it is convenient to view the data (NT: The handy for capturing web pages).-C count Tcpdump exits after it receives count packets.-C File-size (NT: This option is used to With the-w file option) This option allows tcpdump to check if the file size exceeds file-size before saving the original packet directly to the file. If it is exceeded, the file will be closed, and another file continues to be used for the original packet's record. The newly created file name is the same as the filename specified by the-w option, but there is a number after the file name. This number will increase from 1 onwards as new files are created.  File-size is in million bytes (NT: This is 1,000,000 bytes, not 1,048,576 bytes, the latter is calculated as 1024 bytes 1k, 1024k bytes is 1M, i.e. 1m=1024 * 1024x768 = 1,048,576)-D In an easy-to-read form, the programmed package match code is printed on the standard output and then tcpdump stopped. (NT | Rt:human readable, easy to read, usually refers to the ASCII code to print some information. compiled, orchestrated. Packet-matching code, package match code, meaning unknown, need to be supplemented)-DD print out package matching in C language Code.-DDD Prints the package match code in decimal numbers (an additional ' count ' prefix precedes the package match code).-D print system all the network interfaces on which tcpdump can grab packets. Each interface will print out a numeric number, the corresponding interface name, and possibly a description of the network interface.    The network interface name and number can be used in the tcpdump-I flag option (NT: Name or number instead of flag) to specify the network interface on which to grab the packet. This option is useful on systems that do not support interface list commands (NT: For example, Windows systems, or UNIX systems that lack ifconfig-a);    The number of interfaces is useful in Windows 2000 or later systems because the interface names on these systems are complex and not easy to use. The-D option will not be supported if the LIBPCAP library on which the tcpdump is compiled is too old, because of the lack of the Pcap_findalldevs () functionThe Data link layer header information for the packet will be included in the printout of.-e per row-e [email protected] Algo:secret,... The IPSec ESP package (NT | rt:ipsec Encapsulating Security PAYLOAD,IPSEC encapsulated secure payload can be decrypted via [email protected] Algo:secret, which IPSec can understand as , a set of encryption protocol for IP packets, ESP for the entire IP packet or its upper-pelagic protocol partially encrypted data, the former mode of operation is called tunnel mode; The latter's mode of operation is called transfer mode.    Working principle, need to be supplemented separately).    It is important to note that you can set the key (secret) for IPV4 ESP packets when the terminal starts tcpdump. Algorithms that can be used for encryption include DES-CBC, 3DES-CBC, BLOWFISH-CBC, RC3-CBC, CAST128-CBC, or none. The default is DES-CBC (Nt:des, Data encryption Standard, data encryption standards, encryption algorithms unknown, additional). Secret is the key used for ESP and is expressed in ASCII string mode.    If you start with 0x, the key will be read in 16 binary mode. The definition of ESP in this option follows RFC2406, not RFC1827.    Also, this option is only for debugging, and it is not recommended to use this option with a real key (secret) because it is unsafe: The secret entered on the command line can be viewed by other people through commands such as PS. In addition to the syntax format above (NT: Refers to [email protected] algo:secret), you can also add a syntax input file name for tcpdump use (NT: that is, the [email protected] Algo: Secret,... In ... Change to a syntax file name). This file will open this file when it is accepted to the first ESP package, so it is best to cancel some of the privileges granted to tcpdump at this time (NT: This can be understood so that when the file is maliciously written, it does not cause too much damage).-F Displays the external IPv4 address (nt:foreign IP V4 addresses, which can be understood as non-native IP addresses), takes the form of a digital rather than a name. (this option is used to deal with sun publicDivision's Server for NIS (Nt:nis, Network Information Service, Tcpdump displays the name of the external address will use the name Service she provides): This NIS server often falls into an endless query loop when querying for non-local address names. Because testing of an external (foreign) IPV4 address requires a local network interface (the interface used to nt:tcpdump the packet) and its IPV4 address and netmask.  If this address or netmask is not available, or if the interface does not have the appropriate network address and netmask at all (the ' any ' network interface under Nt:linux does not need to set the address and mask, but this ' any ' interface can receive packets from all interfaces on the system), this option does not work correctly.-F  File uses the file as the input for the filter expression, and the input on the command line is ignored.-I interface specifies the interface that the tcpdump needs to listen on.    If not specified, Tcpdump will search the System interface list for the configured interface with the smallest number (excluding the loopback interface). Once you find the first qualifying interface, the search ends immediately. On Linux operating systems with version 2.2 or later kernels, the ' any ' virtual network interface can be used to receive packets on all network interfaces (NT: This will include the purpose of the network interface, and also the destination is not the network interface).    It is important to note that if the real network interface does not work in ' promiscuous ' mode (promiscuous), it cannot crawl its packets on the ' any ' virtual network interface. If the-d flag is specified, tcpdump prints the interface number in the system, which can be used for the interface parameter here.-L row buffer for standard output (NT: The standard output device encounters a newline character and prints the contents of the line immediately). It is useful when you need to observe both the capture and the capture record.  For example, this can be achieved by combining the following command: ' Tcpdump-l | Tee dat ' or ' tcpdump-l > Dat & tail-f dat '.  (NT: The former uses the tee to put the output of the tcpdump in both the file dat and the standard output, the latter through the redirection operation ' > ', the output of the tcpdump into the DAT file, while the DAT file through the tail to put the content in the standard output)-l Lists the types of data link layers supported by the specified network interface and exits. (NT: Specifies that the interface passes-I to specify)-m module loads the SMI MIB module via the file specified by the module (Nt:smi,structure of Management information, management information structure MIB, Management info Rmation Base, management information base. It is understood that both are used for fetching SNMP (simple Network Management protoco) protocol packets.    The specific SNMP operation principle is unknown, need to add additional). This option can be used multiple times to load different MIB modules for tcpdump.-M secret if the TCP packet (TCP segments) has a tcp-md5 option (described in RFC 2385), specify a public key for the validation of its digest secret.- n No address (for example, host address, port number) is represented by a numeric representation of the conversion.-N does not print out the domain name portion of the host. For example, if this option is set, tcpdump will print ' Nic ' instead of ' nic.ddn.mil '.-O does not enable optimization code for package matching. This option is useful when you suspect that some bugs are caused by optimized code.-P in general, the network interface is set to non-' promiscuous ' mode. However, it is important to note that in special cases this network interface will still work in a ' promiscuous ' mode, so that the '-p ' setting and not set is not synonymous with the following selection: ' Ether host {local-hw-add} ' or ' Ether Broadcast ' (NT: The former means only A packet with an Ethernet address of host, which represents a packet that matches the Ethernet address as the broadcast address.-Q Fast (maybe "quiet" better?) Print output. That is, printing very little protocol-related information, so that the output lines are shorter.-R setting Tcpdump the parsing of Esp/ah packets is encapsulated by RFC1825 instead of RFC1829 (Nt:ah, Authentication Header, ESP, security payload, both of which are used in the secure transport mechanism of IP packets). If this option is set, tcpdump will not print out the ' No Relay ' field (Nt:relay prevention field). Also, because the Esp/ah specification does not stipulate that the ESP/AH packet must have a protocol version number field, Tcpdump cannot derive the protocol version number from the received Esp/ah packet.-r file reads the package data from the file.If the file field is the '-' symbol, Tcpdump reads the package data from the standard input.-s prints the sequence number of the TCP packet, using the absolute sequential number instead of the relative sequential number. (NT: Relative sequence number can be understood as, relative to the first TCP packet sequence number of the gap, for example, the recipient receives the first packet's absolute order number is 232323, for the subsequent received 2nd, 3rd packet, tcpdump will print its serial number 1, 2 indicates a gap of 1 and 2, respectively, with the first packet. And if the-s option is set at this point, the 3rd packet will print out its absolute order number for the 2nd received later: 232324, 232325).-S Snaplen set tcpdump packet fetch length of Snaplen, if not set by default will be 68 bytes (while support The network interface sub-connector (Nt:nit, as described above, can search the ' network interface connector ' keyword found there) of the SunOS series operating system default is also the minimum value is 96). 68 bytes for IP, ICMP (nt:internet Control Message Protocol, Internet Control Message Protocol), TCP and UDP packets are sufficient, but for the name Service (NT: Services that can be understood as DNS, NIS, etc.), the packet truncation is generated by the NFS service-related packets. If a packet truncation occurs, the "[|proto]" flag appears in the corresponding printout line of tcpdump (the proto will actually appear as the associated protocol hierarchy for truncated packets). It is important to note that the long fetch length (nt:snaplen) increases the processing time of the packet and reduces the number of packets that can be cached by tcpdump, which can result in packet loss.  So, under the premise of grasping the package we want, the smaller the crawl length, the better. Setting Snaplen to 0 means that tcpdump automatically chooses the appropriate length to crawl the packet.-T type forces the tcpdump to parse the received packets by the package structure described by the protocol specified by type. The currently known type Preferred protocol is: AODV (ad-hoc on-demand Distance Vector Protocol, on-demand distance vector routing protocol, used in Ad hoc (point-to-point mode) networks), CNFP (Cisco Ne Tflow protocol), RPC (Remote Procedure Call), RTP (Real-time appliCations protocol), RTCP (Real-time Applications Con-trol Protocol), SNMP (Simple Network Management protocol), TFTP (Trivial file Transfer Protocol, shredding Protocol), VAT (Visual Audio Tool, Application layer protocol for teleconferencing on the Internet), and WB (distributed Whit e Board, which can be used for Web conferencing Application layer protocol.-T does not print a timestamp in each line of output-tt the time of each line of output is not formatted (NT: This format may not see its meaning at one glance, such as when the timestamp is printed to 1261798315)-ttt tcpdump output , a period of time (in milliseconds) is delayed between each of the two lines of printing-tttt the time stamp that is printed on each line before the date the print-u prints out an unencrypted NFS handle (Nt:handle can be understood as a file handle used in NFS, which will include files in folders and folders)-U makes Properly tcpdump when you use the-W option, its file writes are synchronized with the save of the package. (NT: That is, when each packet is saved, it will be written to the file in a timely manner, not when the output buffer of the file is full before the file is actually written) the-U flag does not work on the old version of the Libcap library (Nt:tcpdump relies on the message capture library) because of the lack of pcap_cump The _flush () function.-V when parsing and printing, produce verbose output. For example, the lifetime of the package, the identity, the total length, and some options for the IP packet. This also opens some additional package integrity checks, such as the checksum of the IP or ICMP packet headers.-VV produces more verbose output than-V. For example, the additional domain in the NFS response package will be printed and the SMB packet will be fully decoded.-VVV produces more verbose output than-VV. For example, the telent used by the SB, SE option will be printed, if Telnet is using a graphical interface, the corresponding graphics options will be printed in 16 (nt:telnet the meaning of the sb,se option is unknown, additional).-W the packet data straight Write to the file without parsing and printing output. These package data can then be re-read and analyzed and printed via the-r option.-W FILeCount This option is used in conjunction with the-C option, which limits the number of files that can be opened, and when the file data exceeds the limit set here, loops over the previous file, which is equivalent to a file buffer pool with filecount files. At the same time, this option causes the beginning of each file name to appear as much as 0 of the placeholder, which makes it easier for these files to be sorted correctly.-X when parsing and printing, tcpdump prints the header data for each packet, and prints out the data for each package in 16, excluding the header of the connection layer. The total printed data size does not exceed the size of the entire packet and the minimum value in Snaplen. It is important to note that if the high-level protocol data is not snaplen so long, and the data link layer (for example, the Ethernet layer) has padding data, the fill data will also be printed. (Nt:so for link layers that pad, which fails to understand and translate, need to be supplemented)-xx tcpdump prints the header data for each packet, and prints the data for each package in 16, including the header of the data link layer.-X when analyzing and hitting At the time of printing, tcpdump prints the header data for each packet, and the data for each package is printed in 16 binary and ASCII format (excluding the header of the connection layer). This is convenient for analyzing packets of some new protocol.-XX when analyzing and printing, tcpdump prints the header data for each packet. The data for each package is printed in 16 binary and ASCII format, including the header of the data link layer. This is convenient for analyzing packets of some new protocols.-Y Datalinktype set tcpdump only capture Data Link layer protocol type is Datalinktype packet -Z user causes Tcpdump to abandon its super privilege (if the root user starts tcpdump, tcpdump will have superuser privileges), and the user ID of the current tcpdump is set to users, and the group ID is set to the ID of the group to which the user first belongs (NT: Tcpdump can be understood here as the corresponding process after the tcpdump run) This option can also be set to open by default at compile time. (NT: User's value is unknown at this time, need to supplement) copy code

Tcpdump catch sql[Turn]