Enabling logging for your MySQL queries from the MySQL prompt

I usually find it a chore to edit the MySQL conf files (as for one, I often have no idea which my.cnf file to edit!) One way to work around this issue, particularly if you want to change a setting temporarily, is to simply enable the change dynamically via the MySQL command prompt. This can be accomplished by modifying "global" variables which can be listed with commands like so:

The above will list all global variables in MySQL. In my dev server, this comes to 317 entries. What is usually needed is a filter to find the variables that you are interested in. For example,

The first command above will list all global variables with the word "log" in them. The second will trim this further down by listing all global variables with the word "general" in their names. With this information, we can enable the general log (not recommended at all on live sites) with the following:

and turn the setting off with the following:

The log will be stored in the file specified in the general_log_file variable. The more common requirement is to enable the slow log to ferret out unoptimised queries.

Hope this helps :)