如何使用Wireshark清晰地捕获MySQL查询SQL [英] How to use wireshark to capture mysql query sql clearly

问题描述

由于我们使用远程Mysql服务器进行开发，因此无法轻松检查查询sql，如果使用本地服务器，则可以tail - f general_log_file查看调用某些http接口时要执行的sql.因此，我安装了wireshark来捕获这些从本地发送的查询sql.首先，我使用本地mysql进行验证.

Because we develop using remote Mysql server , so cannot check query sql easily, if use local server you can tail - f general_log_file to see which sql are executed when call some http interface. So I installed a wireshark to capture these query sql send from local. At first I use local mysql to verify it.

捕获过滤器是

然后我在mysql终端中执行了两个查询sql

then I executed two query sql in mysql terminal

select version();
select now();

但是非常令人失望的是，我在wireshark中找不到这两个sql数据包 我只发现了这四个包.

but very disappointing I cannot find these two sql packets in wireshark I only found these four packets.

但是从我知道的帖子中

要过滤掉mysql数据包，只需在只需要请求查询的数据包时使用过滤器"mysql"或"mysql.query！="".之后，您可以添加一个自定义列，其字段名称为"mysql.query"，以获取在其中执行的查询的列表.

To filter out the mysql packets you just use the filter ‘mysql‘ or ‘mysql.query != ""‘ when you only want packets that request a query. After that you can add a custom column with the field name ‘mysql.query’ to have a list of queries that where executed.

效果是这样的 仅捕获查询sql并非常清楚地显示这些查询sql十分方便.那么如何使用wireshark来实现呢?

and the effect is like this It's convenient to capture only query sql and very clearly displayed these query sql. So how could I use wireshark to implement this?

嗨@Jeff S.

我尝试了您的命令，请参见下文

I tried your command, please see below

#terminal 1
tshark -i lo0 -Y "mysql.command==3"
Capturing on 'Loopback'

# terminal 2
mysql -h127.0.0.1 -u root -p
select version();
#result: nothing output in terminal 1

和tshark -i lo0 -Y "mysql.command==3" -T fields -e mysql.query与tshark -i lo -Y "mysql.command==3"相同，也没有任何输出.但是，如果我只使用tshark -i lo0，它就会输出

and tshark -i lo0 -Y "mysql.command==3" -T fields -e mysql.query is same with tshark -i lo -Y "mysql.command==3" also nothing output. But if I only use tshark -i lo0, it has output

Capturing on 'Loopback'
 1   0.000000    127.0.0.1 -> 127.0.0.1    TCP 68 57881 → 3306 [SYN] Seq=0 Win=65535 Len=0 MSS=16344 WS=32 TSval=1064967501 TSecr=0 SACK_PERM=1
 2   0.000062    127.0.0.1 -> 127.0.0.1    TCP 68 3306 → 57881 [SYN, ACK] Seq=0 Ack=1 Win=65535 Len=0 MSS=16344 WS=32 TSval=1064967501 TSecr=1064967501 SACK_PERM=1
 3   0.000072    127.0.0.1 -> 127.0.0.1    TCP 56 57881 → 3306 [ACK] Seq=1 Ack=1 Win=408288 Len=0 TSval=1064967501 TSecr=1064967501
 4   0.000080    127.0.0.1 -> 127.0.0.1    TCP 56 [TCP Window Update] 3306 → 57881 [ACK] Seq=1 Ack=1 Win=408288 Len=0 TSval=1064967501 TSecr=1064967501
...

推荐答案

您可以使用tshark并将其保存到pcap或仅导出您感兴趣的字段.

You can use tshark and save to a pcap or just export the fields you're interested in.

要保存到pcap中(如果要使用Wireshark稍后查看):

To save to a pcap (if you want to use wireshark to view later):

tshark -i lo -Y "mysql.command==3" -w outputfile.pcap
tshark -i lo -R "mysql.command==3" -w outputfile.pcap
-R is deprecated for single pass filters, but it will depend on your version
-i is interface so replace that with whatever interface you are using (e.g -i eth0)

要保存到文本文件:

tshark -i lo -Y "mysql.command==3" -T fields -e mysql.query > output.txt

您也可以将BPF过滤器与tcpdump一起使用(以及wireshark前置电容过滤器).它们比较复杂，但是如果您捕获了大量流量，则对系统的负担较少.

You can also use BPF filters with tcpdump (and wireshark pre cap filters). They are more complex, but less taxing on your system if you're capturing a lot of traffic.

sudo tcpdump -i lo "dst port 3306 and  tcp[(((tcp[12:1]&0xf0)>>2)+4):1]=0x03" -w outputfile.pcap

注意:
*这会在TCP有效负载中查找03(类似mysql.command == 3).
**由于这是一个非常宽松的过滤器，因此我还添加了3306，以仅限制发往该端口的流量. ***该过滤器基于您的屏幕截图.我现在无法验证它，所以让我知道它是否不起作用.

NOTE:
*This looks for 03 (similar mysql.command==3) within the TCP payload.
**Since this is a pretty loose filter, I also added 3306 to restrict to only traffic destined for that port. ***The filter is based on your screenshot. I cannot validate it right now so let me know if it doesn't work.

示例输出:

这篇关于如何使用Wireshark清晰地捕获MySQL查询SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！