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

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

Answer 2

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

Answer 3

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:

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.