cannot find mysql slow query log file on mac

Question

I am trying to enable slow_query_log on mysql, but I could not find it on my mac. I read in MySQL 5.7 Documentation that"

By default, the server writes files for all enabled logs in the data directory.

When I write show variables like '%slow_query%'; in mysql shell, I see the following:

but I can't see McBook-Pro-6-slow.log in the data directory. Here is all I can see in the data directory:

1. Could someone let me know why I can't see the slow log file?
2. In order to enable the slow_query_log, I've read here that I should add slow-query-log=1 to my.cnf. Here, my problem is that I am not sure where is mysql config file on my Mac. I've found a my-default.cnf in usr/local/mysql/support-files/ and another my.cnf file in /etc. Which one should I modify??

Thanks,

Answer 1

Refer to this Stackoverflow question MySQL 'my.cnf' location? which pertains to Mac OS. As you can see the permutations of locations are numerous usually compounded by different distros and MAMP XAMP WAMP bundles and Home Brew. It is not uncommon to have 2 mysql daemons on a box and not even know it.

Which is why in comments I suggested looking at the output of select @@basedir for the location of the my.ini (Windows) or my.cnf (Linux/Mac). That is not to suggest a configuration file is going to be there, but that is where it should be if one were to exist. Without it, baked-in default values are used. Often there is a stub, a suggested file, named differently (like my-default), awaiting your tweaks and a rename or copy to the appropriate file name of my.ini or my.cnf.

There is also a system variable named slow_query_log_file and its value visible if set thru SELECT @@slow_query_log_file;. For me right now it has a value of GUYSMILEY-slow.log because I did not set it in my ini (Windows) and it defaults to computername+"-slow.log".

That is the filename without the path. Where the file actually is written to is in the datadir seen with the output of select @@datadir;.

On my system this means (via @@basedir)

C:\Program Files\MySQL\MySQL Server 5.6\my.ini 

would have a setting that ends up in a slowlog file written to in this absolute path (helped by @@datadir):

C:\ProgramData\MySQL\MySQL Server 5.6\data\GUYSMILEY-slow.log

and a fragment inside that log file might show something like this:

Ini and cnf changes require a MySQL daemon restart. In that configuration file a section similar to (my 5.6)

[mysqld]
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.6\\
datadir=C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Data\\
port=3306
log_warnings = 2

and (my 5.7)

[mysqld]
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.7\\
datadir=C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data\\
port=3307
log_error_verbosity=2

the above is used within the [mysqld] section to play with settings. What I suggest is playing with this section with an innocuous setting like log_error_verbosity (5.7.2 and up) or similar, save it. Restart the deamon and determine if the variable (as Rick James would call settings because most really aren't dynamically settable). So a sanity check of select @@log_error_verbosity (5.7.2 and up) can confirm it the change is picked up. If it is, bingo, you are doing it right.

The Manual Page Server System Variables depicts the variables (settings) and whether or not they can be dynamically set/changed after the config file load via commands. Dynamic changes are reverted upon daemon restart.

How one would dynamically change a variable might look like:

SET GLOBAL log_error_verbosity=2;

Again, only certain variables are available in certain MySQL versions, such as the above, not available in older versions.

Also note multiple versions of MySQL running concurrently on a server. On mine i have 5.6.31 and 5.7.14. To access a different one via command line tools, use something like the -P 3307 switch to point at the one running on port 3307. Note the uppercase P as opposed to lowercase (which would mean prompt for password).

Determine if multiple instances are running. I use port checks such as

sudo netstat -tulpn  (Linux)
netstat -aon | more  (Windows, the top part, State=LISTENING)

Unfortunately these types of changes and trial and error take time and are very frustrating. Sorry I do not have a quick and easy answer for all cases.

Addendum

Notes here related to comments. In the below, w-x-y-z is a redacted IP Address.

On a Linux box (amazon ec2 redhat btw):
select @@slow_query_log;
-- 0 (so it is turned off)
SELECT @@slow_query_log_file;
-- /var/lib/mysql/ip-w-x-y-z-slow.log
select @@version;
-- 5.7.14

set global slow_query_log=1;
Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation   0.094 sec

(ok I was in MySQL Workbench as a dummied down user, off to do it as root via MySQL cmd line ...
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

btw Workbench user can confirm the above `1`

at shell as linux user:
[ec2-user@ip-w-x-y-z ~]$ cd /var/lib/mysql
[ec2-user@ip-w-x-y-z mysql]$ sudo ls -la
(there were many files, only one needed to show you below)
-rw-r-----.  1 mysql mysql      179 Sep 19 01:47 ip-w-x-y-z-slow.log

[ec2-user@ip-w-x-y-z mysql]$ sudo vi ip-w-x-y-z-slow.log

(Header stub, the entire contents, no slow queries yet, log seen below):

/usr/sbin/mysqld, Version: 5.7.14 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument