mysql tcpdump使用

tcpdump的使用

选项介绍

-A 以ASCII格式打印出所有分组，并将链路层的头最小化。-c 在收到指定的数量的分组后，tcpdump就会停止。-C 在将一个原始分组写入文件之前，检查文件当前的大小是否超过了参数file_size 中指定的大小。如果超过了指定大小，则关闭当前文件，然后在打开一个新的文件。参数 file_size 的单位是兆字节（是1,000,000字节，而不是1,048,576字节）。-d 将匹配信息包的代码以人们能够理解的汇编格式给出。-dd 将匹配信息包的代码以c语言程序段的格式给出。-ddd 将匹配信息包的代码以十进制的形式给出。-D 打印出系统中所有可以用tcpdump截包的网络接口。-e 在输出行打印出数据链路层的头部信息。-E 用spi@ipaddr algo:secret解密那些以addr作为地址，并且包含了安全参数索引值spi的IPsec ESP分组。-f 将外部的Internet地址以数字的形式打印出来。-F 从指定的文件中读取表达式，忽略命令行中给出的表达式。-i 指定监听的网络接口。-l 使标准输出变为缓冲行形式，可以把数据导出到文件。-L 列出网络接口的已知数据链路。-m 从文件module中导入SMI MIB模块定义。该参数可以被使用多次，以导入多个MIB模块。-M 如果tcp报文中存在TCP-MD5选项，则需要用secret作为共享的验证码用于验证TCP-MD5选选项摘要（详情可参考RFC 2385）。-b 在数据-链路层上选择协议，包括ip、arp、rarp、ipx都是这一层的。-n 不把网络地址转换成名字。-nn 直接以IP和端口号显示，而非主机与服务器名称。-N 不输出主机名中的域名部分。例如，‘nic.ddn.mil‘只输出’nic‘。-t 在输出的每一行不打印时间戳。（－tt   -ttt）-O 不运行分组分组匹配（packet-matching）代码优化程序。-P 不将网络接口设置成混杂模式。-q 快速输出。只输出较少的协议信息。-r 从指定的文件中读取包(这些包一般通过-w选项产生)。-S 将tcp的序列号以绝对值形式输出，而不是相对值。-s 从每个分组中读取最开始的snaplen个字节，而不是默认的68个字节。-s 0表示不限制长度，输出整个包。-T 将监听到的包直接解释为指定的类型的报文，常见的类型有rpc远程过程调用）和snmp（简单网络管理协议；）。-t 不在每一行中输出时间戳。-tt 在每一行中输出非格式化的时间戳。-ttt 输出本行和前面一行之间的时间差。-tttt 在每一行中输出由date处理的默认格式的时间戳。-u 输出未解码的NFS句柄。-v 输出一个稍微详细的信息，例如在ip包中可以包括ttl和服务类型的信息。-vv 输出更详细的信息。-vv 输出详细的报文信息。-w 直接将分组写入文件中，而不是不分析并打印出来。 （输出的.pcap文件可以在windows中用wireshark打开，进行进一步分析）要让wireshark能分析tcpdump的包，关键的地方是 -s 参数， 还有要为 -w保存输出文件。-X 以及-XX，以16进制与ASCII方式输出，即可读方式显示数据包，适合http、memcached ascii等明文传输的协议，可以看到内容；

抓MySQL包基本使用

抓所有指定MySQL端口的包

• 确定访问路径

  ifconfig查看网络接口。

  $ifconfigbond0     Link encap:Ethernet  HWaddr A0:D3:C1:F2:0A:E4      inet addr:19.12.170.189  Bcast:10.101.171.255  Mask:255.255.252.0      UP BROADCAST RUNNING MASTER MULTICAST  MTU:1500  Metric:1      RX packets:119817170230 errors:0 dropped:2112 overruns:0 frame:18      TX packets:132336751707 errors:0 dropped:23 overruns:0 carrier:0      collisions:0 txqueuelen:0      RX bytes:44567284326251 (40.5 TiB)  TX bytes:103345125339263 (93.9 TiB)docker0   Link encap:Ethernet  HWaddr 02:42:C0:A8:05:01      inet addr:192.168.5.1  Bcast:0.0.0.0  Mask:255.255.255.0      UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1      RX packets:372 errors:0 dropped:0 overruns:0 frame:0      TX packets:80 errors:0 dropped:0 overruns:0 carrier:0      collisions:0 txqueuelen:0      RX bytes:24710 (24.1 KiB)  TX bytes:4603 (4.4 KiB)eth0      Link encap:Ethernet  HWaddr A0:D3:C1:F2:0A:E4      UP BROADCAST RUNNING SLAVE MULTICAST  MTU:1500  Metric:1      RX packets:59832062720 errors:0 dropped:1767 overruns:0 frame:10      TX packets:64608052591 errors:0 dropped:6 overruns:0 carrier:0      collisions:0 txqueuelen:1000      RX bytes:22169378780278 (20.1 TiB)  TX bytes:50446063242220 (45.8 TiB)      Interrupt:32eth1      Link encap:Ethernet  HWaddr A0:D3:C1:F2:0A:E4      UP BROADCAST RUNNING SLAVE MULTICAST  MTU:1500  Metric:1      RX packets:59985107510 errors:0 dropped:345 overruns:0 frame:8      TX packets:67728699116 errors:0 dropped:17 overruns:0 carrier:0      collisions:0 txqueuelen:1000      RX bytes:22397905545973 (20.3 TiB)  TX bytes:52899062097043 (48.1 TiB)      Interrupt:36lo        Link encap:Local Loopback      inet addr:127.0.0.1  Mask:255.0.0.0      UP LOOPBACK RUNNING  MTU:16436  Metric:1      RX packets:52922771166 errors:0 dropped:0 overruns:0 frame:0      TX packets:52922771166 errors:0 dropped:0 overruns:0 carrier:0      collisions:0 txqueuelen:0      RX bytes:44886284314078 (40.8 TiB)  TX bytes:44886284314078 (40.8 TiB)

  如果监听访问127.0.0.1，端口号为13000的，那么执行

  tcpdump -i bond0 host 127.0.0.1 and port 13000

  当我们本地访问时候就可以看到网络包

  mysql -h127.1 -uroot -P13000$tcpdump -i lo host 127.0.0.1 and port 13000tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on lo, link-type EN10MB (Ethernet), capture size 65535 bytes13:23:21.536030 IP localhost.57752 > localhost.13000: Flags [P.], seq 854726947:854726952, ack 4046376385, win 65, options [nop,nop,TS val 604692396 ecr 604645746], length 513:23:21.536061 IP localhost.57752 > localhost.13000: Flags [F.], seq 5, ack 1, win 65, options [nop,nop,TS val 604692396 ecr 604645746], length 013:23:21.536254 IP localhost.13000 > localhost.57752: Flags [F.], seq 1, ack 6, win 67, options [nop,nop,TS val 604692396 ecr 604692396], length 013:23:24.661664 IP localhost.57796 > localhost.13000: Flags [S], seq 484333654, win 32792, options [mss 16396,sackOK,TS val 604695521 ecr 0,nop,wscale 9], length 013:23:24.661683 IP localhost.13000 > localhost.57796: Flags [S.], seq 2678557336, ack 484333655, win 32768, options [mss 16396,sackOK,TS val 604695521 ecr 604695521,nop,wscale 9], length 013:23:24.661697 IP localhost.57796 > localhost.13000: Flags [.], ack 1, win 65, options [nop,nop,TS val 604695521 ecr 604695521], length 013:23:24.662080 IP localhost.13000 > localhost.57796: Flags [P.], seq 1:97, ack 1, win 64, options [nop,nop,TS val 604695522 ecr 604695521], length 9613:23:24.662103 IP localhost.57796 > localhost.13000: Flags [.], ack 97, win 65, options [nop,nop,TS val 604695522 ecr 604695522], length 013:23:24.662132 IP localhost.57796 > localhost.13000: Flags [P.], seq 1:168, ack 97, win 65, options [nop,nop,TS val 604695522 ecr 604695522], length 16713:23:24.662141 IP localhost.13000 > localhost.57796: Flags [.], ack 168, win 67, options [nop,nop,TS val 604695522 ecr 604695522], length 013:23:24.662286 IP localhost.13000 > localhost.57796: Flags [P.], seq 97:108, ack 168, win 67, options [nop,nop,TS val 604695522 ecr 604695522], length 1113:23:24.662337 IP localhost.57796 > localhost.13000: Flags [P.], seq 168:205, ack 108, win 65, options [nop,nop,TS val 604695522 ecr 604695522], length 3713:23:24.662696 IP localhost.13000 > localhost.57796: Flags [P.], seq 108:198, ack 205, win 67, options [nop,nop,TS val 604695522 ecr 604695522], length 9013:23:24.701868 IP localhost.57796 > localhost.13000: Flags [.], ack 198, win 65, options [nop,nop,TS val 604695562 ecr 604695522], length 0

  加入-A选项可以看到ASCII码的整个包

  use test;$tcpdump -i lo -A host 127.0.0.1 and port 13000tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on lo, link-type EN10MB (Ethernet), capture size 65535 bytes13:24:52.699143 IP localhost.57796 > localhost.13000: Flags [P.], seq 484333859:484333881, ack 2678557534, win 65, options [nop,nop,TS val 604783559 ecr 604755522], length 22E..J..@.@.............2...Y#...^...A.>.....$.C.$..B.....SELECT DATABASE()13:24:52.699570 IP localhost.13000 > localhost.57796: Flags [P.], seq 1:65, ack 22, win 67, options [nop,nop,TS val 604783559 ecr 604783559], length 64E..ta~@.@...........2......^..Y9...C.h.....$.C.$.C...... ....def...DATABASE()..!.f................................13:24:52.699592 IP localhost.57796 > localhost.13000: Flags [.], ack 65, win 65, options [nop,nop,TS val 604783559 ecr 604783559], length 0E..4..@.@.............2...Y9.......A.......$.C.$.C.13:24:52.699632 IP localhost.57796 > localhost.13000: Flags [P.], seq 22:31, ack 65, win 65, options [nop,nop,TS val 604783559 ecr 604783559], length 9E..=..@.@.............2...Y9.......A.1.....$.C.$.C......test13:24:52.699746 IP localhost.13000 > localhost.57796: Flags [P.], seq 65:76, ack 31, win 67, options [nop,nop,TS val 604783559 ecr 604783559], length 11E..?a.@.@../........2.........YB...C.3.....$.C.$.C............13:24:52.700901 IP localhost.57796 > localhost.13000: Flags [P.], seq 31:50, ack 76, win 65, options [nop,nop,TS val 604783561 ecr 604783559], length 19E..G..@.@.............2...YB.......A.;.....$.C.$.C......show databases13:24:52.701728 IP localhost.13000 > localhost.57796: Flags [P.], seq 76:251, ack 50, win 67, options [nop,nop,TS val 604783561 ecr 604783561], length 175E...a.@.@...........2.........YU...C.......$.C.$.C......K....def.information_schema.SCHEMATA.SCHEMATA.Database.SCHEMA_NAME.!.................."......information_schema.....mtr.....mysql.....performance_schema.....test. ..       ...".13:24:52.701855 IP localhost.57796 > localhost.13000: Flags [P.], seq 50:66, ack 251, win 67, options [nop,nop,TS val 604783561 ecr 604783561], length 16E..D..@.@.............2...YU...X...C.8.....$.C.$.C......show tables13:24:52.702307 IP localhost.13000 > localhost.57796: Flags [P.], seq 251:370, ack 66, win 67, options [nop,nop,TS val 604783562 ecr 604783561], length 119E...a.@.@...........2......X..Ye...C.......$.C.$.C......V....def.information_schema.TABLE_NAMES.TABLE_NAMES.Tables_in_testTABLE_NAME.!.................."......t.......".13:24:52.702405 IP localhost.57796 > localhost.13000: Flags [P.], seq 66:73, ack 370, win 67, options [nop,nop,TS val 604783562 ecr 604783562], length 7E..;..@.@.............2...Ye.......C./.....$.C.$.C......t.13:24:52.702586 IP localhost.13000 > localhost.57796: Flags [P.], seq 370:426, ack 73, win 67, options [nop,nop,TS val 604783562 ecr 604783562], length 56E..la.@.@...........2.........Yl...C.`.....$.C.$.C.+....def.test.t.t.@mytime.@mytime.?.....................13:24:52.741866 IP localhost.57796 > localhost.13000: Flags [.], ack 426, win 67, options [nop,nop,TS val 604783602 ecr 604783562], length 0E..4..@.@.............2...Yl.......C.......

  查看IP为19.12.170.189的包

  $tcpdump -i bond0 -A host 19.12.170.189 and port 13000$mysql -h19.12.170.189 -uroot -P13000ERROR 1130 (HY000): Host '19.12.170.189' is not allowed to connect to this MySQL server$tcpdump -i bond0 -A host 19.12.170.189 and port 13000tcpdump: verbose output suppressed, use -v or -vv for full protocol decodelistening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes13:26:56.813547 IP 19.12.170.189.34505 > 19.12.170.180: Flags [S], seq 762925633, win 14600, options [mss 1460,sackOK,TS val 3653483557 ecr   0,nop,wscale 7], length 0E..<.d@.@..{e.de....2.-yRA......9...............%........13:26:56.813588 IP 19.12.170.180 > 19.12.170.189.34505: Flags [S.], seq 1381285549, ack 762925634, win 14480, options [mss 1460,sackOK,TS val    604907673 ecr 3653483557,nop,wscale 9], length 0E..<..@.@...e..e.d2...RT..-yRB..8.x..........$.(....%...13:26:56.813760 IP 19.12.170.189.34505 > 19.12.170.180: Flags [.], ack 1, win 115, options [nop,nop,TS val 3653483557 ecr 604907673], length 0E..4.e@.@...e.de....2.-yRBRT.....s..........%$.(.13:26:56.814151 IP 19.12.170.180 > 19.12.170.189.34505: Flags [P.], seq 1:92, ack 1, win 29, options [nop,nop,TS val 604907674 ecr 3653483557],   length 91E....7@.@.:Ne..e.d2...RT..-yRB....j].....$.(....%W....j.Host '19.12.170.189' is not allowed to connect to this MySQL server13:26:56.814175 IP 19.12.170.180 > 19.12.170.189.34505: Flags [F.], seq 92, ack 1, win 29, options [nop,nop,TS val 604907674 ecr 3653483557], length 0E..4.8@.@.:.e..e.d2...RT.      -yRB...........

  -w选项可以保存.cap文件，用wireshark分析

  $tcpdump -i bond0  host 19.12.170.189 and port 13000 -w w.cap也可以继续用tcpdump分析，加上-r命令即可$tcpdump -A -r w.cap

cap文件截断

可以用命令截断指定时间的cap文件

editcap -A '2017-06-21 12:00:00' -B '2017-06-21 13:00:00' input.pcap output.pcap

观察指定客户端发过来的包

tcpdump -i bond0 host 19.12.170.121 or 19.12.170.31 or 19.12.170.76 -w target.cap

观察指定MySQL与clentIP之间的包

$tcpdump -i bond0  host 19.12.170.189 and port 13000 and host 19.12.170.180