0

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:

enter image description here

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

enter image description here

  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,

mOna
  • 2,341
  • 9
  • 36
  • 60
  • SO is for programming questions. Questions about configuring databases belong on dba.stackexchange.com. – Barmar Sep 18 '16 at 07:02
  • @Barmar: ok, should I delete it now? – mOna Sep 18 '16 at 07:06
  • Maybe the mods will move it for you. – Barmar Sep 18 '16 at 07:09
  • for now can you `SET GLOBAL slow_query_log=1;` – Drew Sep 18 '16 at 09:37
  • 1
    and also `select @@basedir` should be the location of the ini or cnf. The stub of the file (the filename as a non pathed name) has the file targeted to be written to this path: `SELECT @@datadir;` – Drew Sep 18 '16 at 09:41
  • 1
    See our docs page here: http://stackoverflow.com/documentation/mysql/5102/log-files/18011/slow-query-log#t=201609180936322552637 – Drew Sep 18 '16 at 09:46
  • Thanks @Drew. It worked :). Just one more question: Is it better to set these variables (e.g., `slow_query_log`, `innodb_buffer_pool_size`) on mysql config file or from mysql environment? – mOna Sep 18 '16 at 10:04
  • certainly in your ini or cnf if you can. That said you are not the first person to hunt for the location and a bunch of restarts. Some people spend days trying to get the config files to work – Drew Sep 18 '16 at 10:09
  • @Drew: I found three .cnf files in `/etc/my.cnf.d/`: 1. mysql-clients.cnf, 2.server.cnf and 3.tokudb.cnf. Should I add these variables in server.cnf or mysql-clients.cnf? – mOna Sep 18 '16 at 10:28

2 Answers2

2

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:

enter image description here

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
Graham
  • 7,431
  • 18
  • 59
  • 84
Drew
  • 24,851
  • 10
  • 43
  • 78
  • Thanks for your detailed answer. `SELECT @@slow_query_log_file;` also shows me this path: `/usr/local/mysql/data/MacBook-Pro-6-slow.log` but as I mentioned in my question I can't see `MacBook-Pro-6-slow.log` in data directory. Is it because `slow_query_log` value is OFF now? – mOna Sep 19 '16 at 05:30
  • yes it is the reason ! Then create some slow queries :p – Drew Sep 19 '16 at 05:31
  • Thanks, btw, I'm not sir:) – mOna Sep 19 '16 at 05:34
  • Hopefully the Addendum section just added will mirror your situation and show the birth of a fresh slow query log file. – Drew Sep 19 '16 at 05:59
1

SHOW VARIABLES LIKE 'log_output'; to verify that it is set to FILE or FILE,TABLE.

Rick James
  • 135,179
  • 13
  • 127
  • 222