'How to use wireshark to capture mysql query sql clearly

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 enter image description here

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 enter image description here 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 enter image description here 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
...
mysqlwiresharktshark


Solution 1:[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: Sample output from two commands

Solution 2:[2]

Useful answers here: https://serverfault.com/questions/358978/how-to-capture-the-queries-run-on-mysql-server

In particular: SoMoSparky's answer of:

tshark -T fields -R mysql.query -e mysql.query

and user1038090's answer of:

tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

Solution 3:[3]

I tried another tshark command from this post, and it could capture query sql from local to remote mysql server. 

tshark -i en0 -d tcp.port==3306,mysql -T fields -e mysql.query 'port 3306'
Capturing on 'Wi-Fi'
select version()


select now()


select rand()

but it also output some blank lines between these sql. I tried below command want to remove blank line but failed

tshark -i en0 -d tcp.port==6006,mysql -Y "frame.len>10" -T fields -e mysql.query 'port 6006'

And unfortunately this command cannot support capturing query sql to local mysql(5.7.12).

tshark -i lo -d tcp.port==3306,mysql -T fields -e mysql.query 'port 3306'
Capturing on 'Loopback'

Nothing output except blank lines.

Solution 4:[4]

Wireshark tool supports MySQL protocol: https://www.wireshark.org/docs/dfref/m/mysql.html

Then config wireshark

a.menu Analyze --> Decode as --> add "field=tcp_port value=3306  current=MySQL"
b.filter ‘mysql‘ or ‘mysql.query != “”‘

Solution 5:[5]

I had similar "problem"

Try to check your mysql ssl

Probably the ssl was turned on hence the traffic was encrypted

You can refer to this post to check the ssl: https://dba.stackexchange.com/questions/36776/how-can-i-verify-im-using-ssl-to-connect-to-mysql

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 pdwalker
Solution 3 zhuguowei
Solution 4 Kos
Solution 5 ivantedja

Related Questions