Logging MySql Queries on MAMP
Date : 2008 08 05 Category : Design & Usability
ParticletreeWhen I switched to a Mac and started playing around with a LAMP development environment a couple of months ago, I was delighted to find out that MAMP could install and configure my PHP, Apache, and MySql setup without the need to mess around with config files for hours. The default setup was great, but there was a major missing feature. I needed the ability log my SQL queries in order to easily track SQL errors and improve efficiency.
The good news was that even though SQL logging wasn’t part of the default MAMP installation, I quickly learned that it could easily be configured by simply changing some options in the my.cnf file, which contains default MySql startup options and system variables. After a little Google searching, I determined that the MySql General Query Log and Slow Query Log were the tools I had in mind.
The MySQL Query Log is used for logging all SQL queries, and it’s quickly setup by making a simple change to the “log” setting in the my.cnf file. This option requires that you set a path to the file where MySQL will write all statements. It will save you from writing your SQL queries to standard output and keep a history of all your SQL queries so you can track exactly what’s going on.
# General Query Log log = /Applications/MAMP/logs/mysql_sql.logAnd to take basic logging a step further, the Slow Query Log helps you optimize your code by determining which queries are taking a bit too long to execute. The first setting you’ll want to edit is “log_slow_queries”, which is the file Slow Query Log will write to. Note that you’ll want to point to a different log file than the General Query Log. The second setting, long_query_time, sets the threshold for what queries MySql should consider slow. The minimum amount of time you could set prior to MySql 5.1.21 was 1 second, but if you’re running MySql 5.1.21 or higher, you’ll be able to set the Slow Query log threshold in microseconds. To learn more, take a look at the MySql version capabilities.
# Slow Query Log log_slow_queries=/Applications/MAMP/logs/mysql_sql_slow.log long_query_time=1MAMP provides three example my.cnf files in the /Applications/MAMP/Library/share/mysql/ directory. To get this all working, I just modified the my-medium.cnf file with my log settings and then saved it as my.cnf in the /Applications/MAMP/Library/ directory. After restarting MAMP and executing a few queries, there was an active record of the MySql activity in my log file. Now armed with that log information, profiling and debugging are much easier.
