分析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!