Dumping all remote mysql queries to a server

For a project recently we needed to see which users were accessing which databases on a remote mysql server. After a little bit of reading and experimenting I came up with this command – hopefully it will be useful for someone in the future.

tshark -i any -n \
    -d tcp.port==3306,mysql \
    -T fields -e timestamp -e ip.src -e mysql.user -e mysql.schema -e mysql.query \
    -f "dst port 3306" \
    -Y "len(mysql.user) > 0 || len(mysql.query) > 0" 

This uses the excellent wireshark tool to capture traffic. The -f flag specifies a filter to only inspect inbound traffic to the server (as we don’t care about the responses).

The line beginning -T specifies the fields we wish to dump (in a tab-separated fashion), -d ensures that all traffic will attempt to be decoded as mysql. The final -Y flag ensures that only packets with queries or logins will be dumped, rather than TCP overhead or placeholder binding etc.

Note that user information is only given at the beginning of a session. Schema (database name) information may be on that same line, or you may have to scan the queries to look for a USE database type command as there are two different ways of setting the current database that will be accessed.