Lecture
already wrote that the use of general query log is resource intensive. Part of the problem is resolved if you use the new MySQL version 5.1: online logging, which allows you to enable and disable general query log without stopping the server.
Unfortunately, this is not a panacea: you may have an old version of MySQL server that does not have this capability, general query log may contain too much information and it will be difficult to find an error, you may have some other reason of its own.
What if you can't use the general query log?
One option is to use journaling with your application. Add code that will write requests that your application sends to the log file.
This approach has the advantage that you can fine-tune the conclusion: what and how to write. It will be good if you write the return value and error messages in the same log.
Admission number 9: set up your application in such a way that it will record request logs on its own.
Another option is to use a proxy that will intercept requests and write them to a file.
One of the preferred options is MySQL Proxy, since it is a scripted proxy designed to work with the MySQL server. It uses the MySQL client server protocol. You can write scripts for MySQL Proxy in the Lua programming language.
The following is an example implementation of a 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 above script writes to the standard output all received requests.
You can run it like this:
$mysql-proxy --proxy-lua-script=`pwd`/general_log.lua
The output will be:
$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, since MySQL Proxy uses the full-fledged Lua programming language, you can customize the output in such a way as to obtain the necessary information about exactly those queries that interest you. For example, you can write information about the return code and errors in such a "General Query Log".
More information here: http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy.html and http://forge.mysql.com/wiki/MySQL_Proxy
Examples of scripts can be found here: http://forge.mysql.com/search.php?k=proxy
Method # 10: use MySQL Proxy or any other proxy.
The use of the proposed two options is also attractive because you can fine-tune the output and record only the queries that you need for further analysis.
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application