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.
then I executed two query sql in mysql terminal
select version();
select now();
but very disappointing I cannot find these two sql packets in wireshark I only found these four packets.
But from a post I knew
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.
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?
hi @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
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
...
You can use tshark and save to a pcap or just export the fields you're interested in.
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)
To save to a text file:
tshark -i lo -Y "mysql.command==3" -T fields -e mysql.query > output.txt
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
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.