| Russian Version |
I already wrote about using of general query log requires resources. Part of the problem can be solved if use new feature of MySQL 5.1: online logging which allows to turn general query log to on or to off without stopping the MySQL server.
Unfortunately this doesn't always work: you can have old version of MySQL server which has not such a feature, general query log can contain very large amount of information, so you can just miss the problem in thousands of correct queries, you can have some other own reason.
But what to do if you can not use general query log?
One of the methods is write log files using your application. Add a code which will write queries which your application sends to MySQL to the log file.
With this method you can tune how and what to write to the log file. Would be good if you will write return value and error messages in the same log file.
Method #9: tune your application in such a way so it will write queries to log files itself.
Yet another method is using proxy which will get queries and write them to the file.
One of most preffered variants is MySQL Proxy, because this is scriptable proxy made for working with MySQL server. It uses MySQL client-server protocol. You can write programs for MySQL Proxy using Lua programming language.
Below you can see example how to write general query log using MySQL Proxy:
function read_query( packet )
if packet:byte() == proxy.COM_QUERY then
print(os.date("%d%m%g %T") .. "\t"
.. proxy.connection.server.thread_id
.."\tQuery\t" .. packet:sub(2))
end
end
The script writes all received queries to STDOUT.
You can run MySQL Proxy with such a script using command like:
$mysql-proxy --proxy-lua-script=`pwd`/general_log.lua
Here is the output:
$mysql-proxy --proxy-lua-script=`pwd`/general_log.lua
011109 15:00:24 12 Query select @@version_comment limit 1
011109 15:00:27 12 Query SELECT DATABASE()
011109 15:00:27 12 Query show databases
011109 15:00:27 12 Query show tables
011109 15:00:30 12 Query select * from t1
Of course, as MySQL Proxy uses full-functional programming language Lua, you can tune output in such a way so it will write all necessary information about queries which you are interested in. For example, you can write in such "General Query Log" information about return codes and errors.
You can find more information here: http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html and here: http://forge.mysql.com/wiki/MySQL_Proxy
You can find examples of scripts for MySQL Proxy here: http://forge.mysql.com/search.php?k=proxy
Method #10: use MySQL Proxy or any other proxy.
Using methods described here is useful also becasue you can tune output in details and write information only about querires which you need for further analysis.
| Back | Content | Forward |
|
Author 2010 Sveta Smirnova COPYRIGHT © 2010 S.Smirnova and S. Lasunov sveta_at_js-client_dot_com |