Observing the MySQL Query Log

Avatar von Martin Brotzeller

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:

        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") {
                } else {
/[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);
/[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!

Avatar von Martin Brotzeller


5 Antworten zu „Observing the MySQL Query Log“

  1. Avatar von Ben Hicks

    Maybe you want to have a look at this project: http://mtop.sourceforge.net/

  2. Thanks for sharing this.
    But I am getting an error while executing the script.

    [test]# tail -f /var/log/mysql/mysqld.log | awk -f /root/querylog.awk db=test
    awk: /root/querylog.awk:1: BEGIN {
    ‚ in expression/querylog.awk:1: ^ invalid char ‚

    1. What version of awk do you use? I was using gnu awk 3.1.5
      The error you show does not make much sense, as there is only „BEGIN {“ on line 1. You can try to put the code in single quotes and pass (part of) it as argument instead of in a file

  3. Thanks for sharing this! I badly needed this since we are several developpers working on the same server, but with different databases…

    When using PEAR::DB, your script needs some adjustements, since the logging is not the same. Here is my version (without indentation, which only worked for SELECTs) :

    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]* Init DB/ {
    if(index($4,mydb)==0) {
    # printf(„not using %s\n“,$4);
    } else {
    if($2 == „Init“) {
    } else {
    # print;
    /[0-9]* Query/ {
    if(conns[$1]==“true“) {
    for(i=3; i<=NF; i++){ printf("%s ",$i); } printf("\n"); } } /[0-9]* Quit/ { delete conns[$1]; # printf("deleting %s\n",$1); }

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Für das Handling unseres Newsletters nutzen wir den Dienst HubSpot. Mehr Informationen, insbesondere auch zu Deinem Widerrufsrecht, kannst Du jederzeit unserer Datenschutzerklärung entnehmen.