Observing the MySQL Query Log

Debugging an existing application can be hard to bootstrap. Sometimes it just helps to observe the queries a web application is sending to the database. Unfortunately, the MySQL Query log does not directly tell the user which query goes to which database.I wanted to have a tail on the queries that go from an existing web application to a particular database. Since that was not the only database, and the MySQL query log does not support filtering, I hacked up a short awk script to solve the task for me. On the way, I did some reformatting to be better able to read the queries.

The MySQL query log looks like this:

080228 15:27:50   1170 Connect     user@host on database_name
                  1170 Query       SET NAMES "utf8"
                  1170 Query       SELECT something FROM sometable WHERE some=thing
                  1170 Quit

So we need to filter out the „Connect“ line for all connections to the database in question and retrieve the connection id, then output all lines that reference that connection id. While we’re at it, we also break up the SELECT line into multiple lines for readability.

This is my awk script:

BEGIN { 
        mydb = "default_database";
        if (ARGC == 2 && substr(ARGV[1],0,3)=="db=") {
                mydb = substr(ARGV[1],4);
                printf("my db %s\n",mydb);
        }
}
/[0-9]* Connect/ {
        if(index($0,mydb)==0) {
                #printf("not using %s\n",$0);
        } else {
                if($2 == "Connect") {
                        what=$1;
                } else {
                        what=$3;
                }
                print;
                conns[what]="true";
        }
}
/[0-9]* Query/ {
        if(conns[$1]=="true") {
        printf("% 4s %s  : ",$1,$2);
        for(i=3; i<=NF; i++){
               if ($i == "FROM") printf("\n\t\t");
               else if ($i == "WHERE") printf("\n\t\t");
               else if ($i == "GROUP") printf("\n\t\t");
               else if ($i == "HAVING") printf("\n\t\t");
               else if ($i == "ORDER") printf("\n\t\t");
               else if ($i == "LIMIT") printf("\n\t\t");
               else if ($i == "AND") printf("\n\t\t\t"); # AND clauses are indented one level deeper
               gsub(",",",\n\t\t\t",$i); # selected fields are also indented deeper
               printf("%s ",$i);
               }
        printf("\n");
        }
}
/[0-9]* Quit/ {
        delete conns[$1];
        printf("deleting %s\n",$1);
}

For those not familiar with awk: The manpage tells you everything that is neccessary to understand how it works. Awk takes a couple of patterns (BEGIN, and /pattern/ here) and earch line that matches a pattern is then referenced as $0 and the following block is executed. Parts of the line are then put into $1, $2 and so forth.

What I do here:

• The BEGIN rule looks at the arguments, so that the user can provide a database name on the commandline as „db=mydbname“
• The Connect pattern grabs the connect lines and looks wether the correct line is referenced. It then looks whether the timestamp is omitted. After that it stores the connection id in an awk array
• The Query pattern grabs all queries, and if the connection id is already in our array, it prints the query, reformatting it with newlines and tabs
• The Quit pattern removes the connection ids from the array (Might not be neccessary since MySQL uses the ids in ascending order, but whatever :) )

I stored the above script as ~/querylog.awk and added log=/data/mysql-queries.log in my.cnf

tail -f /data/mysql-queries.log | awk -f ~/querylog.awk db=mydb_name

Perhaps someone with similar needs might find use in my solution. Suggestions for improvement are welcome!