Analysis of MySQL query records-ma yongzhan

Copyright statement: original works can be reproduced. During reprinting, you must mark the original publication, author information, and this statement in hyperlink form. Otherwise, legal liability will be held. Http://blog.csdn.net/mayongzhan-ma yongzhan, myz, mayongzhan

Address: http://blog.thinkphp.de/archives/303-Observing-the-MySQL-Query-Log.html

Debugging existing applications is very difficult. Sometimes, you just want to see if the database operations performed by the program have been sent to the database. You can use MySQL to query logs. Unfortunately, MySQL query logs do not directly tell users which databases are queried.
I want to get the query executed by the last few programs. Because this program is not the only program in this database, and MySQL query records do not support filtering, I use an awk script to filter it myself. I made some formatting for better reading.

The MySQL query record format is as follows:
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
We need to filter out the results of the Connect row that contain the database name I defined. Of course, you need to format the SELECT statement.
 
 
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 ");
Else if ($ I = "where") printf ("/n/T ");
Else if ($ I = "group") printf ("/n/T ");
Else if ($ I = "having") printf ("/n/T ");
Else if ($ I = "order") printf ("/n/T ");
Else if ($ I = "Limit") printf ("/n/T ");
Else if ($ I = "and") printf ("/n/T"); # And clses are indented one level deeper
Gsub (",/n/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 users who are not familiar with awk, you can understand the use of awk. Use begin as the start and then execute the internal statement. When $0, $1, $2 is encountered, the related regular expression matching is executed accordingly.

As follows:
• Begin, and a parameter DB = mydbname is obtained.
• Connect match, whether it is what we need, and save the required part in the array.
• Query matches all query statements, and then queries in the array to determine if it is needed. If yes, format and output the query.
• Quit deletes connect from the array and then outputs it.

Save the above awk script ~ /Querylog. awk, and then add log =/data/mysql-queries.log in my. CNF
Tail-F/data/mysql-queries.log | awk-f ~ /Querylog. awk DB = mydb_name

Some people may have similar requirements and use my solutions. Of course, they may also find some problems. Suggestions for improvement are welcome.

 

 

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 particle 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 ");
Else if ($ I = "where") printf ("/n/T ");
Else if ($ I = "group") printf ("/n/T ");
Else if ($ I = "having") printf ("/n/T ");
Else if ($ I = "order") printf ("/n/T ");
Else if ($ I = "Limit") printf ("/n/T ");
Else if ($ I = "and") printf ("/n/T"); # And clses are indented one level deeper
Gsub (",/n/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 ~ /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!