How to use wireshark to capture mysql query sql clearly

Question

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.

The capture filter is

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
...

Answer 1

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.

Example Output: