分析MySQL查詢記錄 – 馬永占 譯

著作權聲明：原創作品，允許轉載，轉載時請務必以超連結形式標明文章原始出版、作者資訊和本聲明。否則將追究法律責任。http://blog.csdn.net/mayongzhan - 馬永占,myz,mayongzhan

原文地址：http://blog.thinkphp.de/archives/303-Observing-the-MySQL-Query-Log.html

調試現有的應用程式是很困難的。有時候，只是想要得到程式執行的資料庫操作是否已經發送到資料庫。可以使用MySQL查詢日誌，不幸的是， MySQL查詢日誌不直接告訴使用者查詢到哪些資料庫。
我要得到最近幾條程式執行的查詢。因為這個程式不是這個資料庫唯一的程式，而且MySQL查詢記錄不支援過濾，我使用一個awk的指令碼來自己進行過濾。我做了一些格式化，以便能更好的閱讀。

MySQL查詢記錄格式如下：
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
我們需要過濾出Connect那行的含有我定義的資料庫名稱的那組結果。當然還要對SELECT進行一下格式化。
 
 
我的awk指令碼：
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);
}
 
對那些不熟悉awk的使用者：瞭解awk的使用就可以了。使用BEGIN作為開始，然後執行內部語句，當遇到$0,$1,$2時，相應執行相關的正則匹配。

如下：
• BEGIN這塊，得到一個參數為db=mydbname。
• Connect匹配，是否為我們所需要的部分，把需要的部分儲存在數組中。
• Query匹配所有查詢語句，然後在數組中查詢判斷是否為我們需要的。是的話，就格式化然後輸出查詢。
• Quit從數組中刪除Connect，然後輸出。

  把上面的awk指令碼儲存為~/querylog.awk，然後在my.cnf中添加 log=/data/mysql-queries.log
tail -f /data/mysql-queries.log | awk -f ~/querylog.awk db=mydb_name

或許有人有類似的需求，會使用我的解決方案，當然也可能發現一些問題。歡迎提出改進意見。

 

 

Observing the MySQL Query Log - ThinkPHP /dev/blog - PHP

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!