Log every MySQL query to a file by enabling the General Log in MAMP

Question

How can I enable the General Log for MySQL for use with MAMP?

I use it to run Apache, MySQL and PHP on my Mac so I can turn on/off/restart via a UI instead of messing with Apache or PHP that's installed in the machine by default.

On my Mac, I've tried setting config settings in the my.cnf file to log every SQL query that's run to a file. The setting seems to have taken affect as shown by the output below when I run the SHOW VARIABLES command from within Terminal.

Problem: General Log file isn't bring populated.
When I actually execute an SQL query or reload a PHP page on the MAMP server that reads data from the MySQL database, the general log file isn't created or populated.

Here's the part of content of my.cnf that I have set:

# The MySQL server
[mysqld]
#port       = 9999
socket      = /Applications/MAMP/tmp/mysql/mysql.sock
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
bind-address = 127.0.0.1
general_log = 1
general_log_file = /Applications/MAMP/logs/mysql_general_sql.log

Here's part of the output from running SHOW VARIABLES statement:

mysql> show variables;
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                                                                  |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| ...                                               | ...                                                                                                                    |
| general_log                                       | ON                                                                                                                     |
| general_log_file                                  | /Applications/MAMP/logs/mysql_general_sql.log                                                                          |
| log                                               | ON                                                                                                                     |
| log_bin                                           | OFF                                                                                                                    |
| log_bin_trust_function_creators                   | OFF                                                                                                                    |
| log_error                                         | /Applications/MAMP/logs/mysql_error_log.err                                                                            |
| log_output                                        | FILE                                                                                                                   |
| log_queries_not_using_indexes                     | OFF                                                                                                                    |
| log_slave_updates                                 | OFF                                                                                                                    |
| log_slow_queries                                  | OFF                                                                                                                    |
| log_warnings                                      | 1                                                                                                                      |
| long_query_time                                   | 10.000000                                                                                                              |
| relay_log                                         |                                                                                                                        |
| relay_log_index                                   |                                                                                                                        |
| relay_log_info_file                               | relay-log.info                                                                                                         |
| relay_log_purge                                   | ON                                                                                                                     |
| relay_log_recovery                                | OFF                                                                                                                    |
| relay_log_space_limit                             | 0                                                                                                                      |
| report_host                                       |                                                                                                                        |
| report_password                                   |                                                                                                                        |
| report_port                                       | 3306                                                                                                                   |
| report_user                                       |                                                                                                                        |
| server_id                                         | 1                                                                                                                      |
| slow_launch_time                                  | 2                                                                                                                      |
| slow_query_log                                    | OFF                                                                                                                    |
| slow_query_log_file                               | /Library/Application Support/appsolute/MAMP PRO/db/mysql/tim-slow.log                                                  |
| socket                                            | /Applications/MAMP/tmp/mysql/mysql.sock                                                                                |
| sql_log_bin                                       | ON                                                                                                                     |
| sql_log_off                                       | OFF                                                                                                                    |
| ...                                               | ...                                                                                                                    |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
318 rows in set (0.01 sec)

Is there something I'm missing?

Answer 1

I tried running the command SET GLOBAL general_log = 'ON'; to see if I could get it to work that way. This produced an error message File Not Found, even though the file did exist.

This indicated to me that problem may be due to file permissions.

After some playing around, I changed:

1. the owner of the file to match the user that the MySQL service was being run under, and
2. gave that user "write access" to the file

Since making those changes, it's been working perfectly ever since.