MySQL Capture and email SQL to a developer

Sometimes, with an extra layer between the database and the developer (such as a reporting server), it is helpful to capture the SQL statement that was sent directly to the MySQL instance.

On a busy system 1000s of statements can be sent in a minute.  So how do we only capture SQL for a particular developer?

First step is to setup the general log:  (Docs). 

Second, have the developer add the comment line, #capture, to the beginning of their SQL and and the comment line, #endcapture, to the end of their SQL.

For example:

#capture
Select * from customers where last_name like '%mith%';
#endcapture

Third, put this script in your path, mark it as executable (chmod +x capture)  and set the email address to developer'e email.
#!/usr/bin/env perl
use strict;
use warnings;
my $fileName=1;
while (<>) {
  if (/\#capture/ .. /\#endcapture/) {
    if($_=~/\#capture/) {
      open(MYFILE, '>', $fileName);
    }
    print MYFILE;
    if($_=~/\#endcapture/) {
            close(MYFILE);
            my $CMD = qq(cat "$fileName" | mail -s "query" "the .developer\@domain-email.eg");
            system($CMD);
            $fileName++;
    }
  }
}Fourth, in another shell, start monitoring the general log.
tail general.log | capture

Fifth, have the developer send the query and quickly turn on the general log.  We don't want to leave this on for very long as it could take up lots of disk space and slow down the server.

I like to use mysql -e "set global general_log='ON'" so I can quickly turn on and off the general log when coordinating with the developer.

Once the developer has sent the query, when the script captures it, it will create a file named '1' for the first capture, follwed by 2, 3, 4, etc.  When you are through capturing just use

mysql -e "set global general_log='OFF'"

Limitations
If the layer between the developer and MySQL likes to strip out comment lines, this will not work.   For hibernate, here's a link that shows how to let comments come through (Hibernate Comments)

I've had lots of good luck with this technique and developers love it.

end of post