Capture query to MySQL in Server

Question

I want to capture queries to MySQL from my web-application on my Linux-based Server. MySQL port is 3306 and my web-application in on port 8181. So I started using tcpdump as follow:

# tcpdump -x "port 3306"

Now I browse my application and make some queries to Data-Base but I get nothing in tcpdump console. I also tried this:

# tcpdump -i eth0 -s 0 -l -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.=" $_";
  }
}'

But I got nothing either. I really don't know what is the problem. When I checked servlet code on my server, I can find Data-Base connection is established on port 3306:

Class.forName("com.mysql.jdbc.Driver");
    cnn = DriverManager.getConnection("jdbc:mysql://localhost:3306/OTP?useUnicode=true&characterEncoding=UTF-8", "blahblah", "blahblah");

can any one help me with this?

Answer 1

Ok, I figured it out. Since the application send local query to local database, in order to capture these queries I should capture lo packets with tcpdump:

# tcpdump -xx -i lo

This works perfectly.

Answer 2

You can use Jet Profiler for catch your querys and is a cross-platform program and you can download a free version.

Answer 3

In MySQL:

SET GLOBAL general_log = 'ON';

With following Query you can check, where the data is logged, and if the logging is enabled:

SHOW VARIABLES LIKE '%general_log%';