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.
show variables like '%slow_query%';
As mentioned in comments we have a Docs page here: Slow Query Log
Also 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
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
On my system this means (via
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
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.
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