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

Benedicto

That is exactly how you have to do it. What happens if you enter this in your filter? The display columns will change with your filter. – Seemly

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:

Guth

I have tried your way, but it's not success. Please see my post. – Benedicto

I just tried it and it worked for me. What version of tshark are you using? I edited my answer above with a screenshot. – Guth

Thanks! tshark version: tshark -v TShark (Wireshark) 2.0.4 (v2.0.4-0-gdd7746e from master-2.0); mysql version: 5.7.12; Mac OS X EI Capitan version 10.11.5 – Benedicto

I upgraded to 2.0.4 and I'm able to see the queries. Try to adjust the commands to see if you can get it to work. For example, use -Y 'mysql.command>=0' or your original 'mysql.query != “”' Unfortunately, I don't have access to a mac to verify that. – Guth

hi @Jeff S I tried another tshark command, this time it could successfully capture query sql from local to remote mysql server. please see my answer below. – Benedicto

Answer 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.=" $_";
  }
}'

Erasion

Answer 3

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

Bronez

This. I was seeing data in port 3306 but it didn't make sense and the mysql display filter couldn't do anything. I had to disable the encryption with mysql -uroot --ssl-mode=DISABLED which then connected to the server without encryption. Then Wireshark could show the mysql protocol. – Burgonet

Answer 4

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.

Benedicto

You're seeing blank lines because the command is not filtering for just queriers. It also looks like you're now seeing this traffic on en0 rather than lo so try this: tshark -i en0 -Y "mysql.command==3" -T fields -e mysql.query – Guth

Thanks! but use your way nothing output. It seems tshark cannot resolve it correctly in my machine. – Benedicto

Answer 5

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 != “”‘ 

Factitious