How can I enable MySQL s slow query log without restarting MySQL

Question

I followed the instructions here  http   crazytoon com 2007 07 23 mysql-changing-runtime-variables-with-out-restarting-mysql-server  but that seems to only set the threshold   Do I need to do anything else like set the filepath   According to MySQL s docs   If no file name value is given for --log-slow-queries  the default name is  host name-slow log  The server creates the file in the data directory unless  an absolute path name is given to specify a different directory     Running  SHOW VARIABLES   doesn t indicate any log file path and I don t see any slow query log file on my server     EDIT  Looks like I m using server version 5 0 77  so I needed to do   SET GLOBAL log slow queries   1   but I get  ERROR 1238  HY000   Variable  log slow queries  is a read only variable  I assume I m going to need to restart the server and have log slow queries set in my config

User · Answer

Find log enabled or not   SHOW VARIABLES LIKE   log      Set the logs -  SET GLOBAL general log    ON     SET GLOBAL slow query log    ON

User · Answer

If you want to enable general error logs and slow query error log in the table instead of file  To start logging in table instead of file   set global log output      TABLE       To enable general and slow query log   set global general log   1  set global slow query log   1    To view the logs   select   from mysql slow log  select   from mysql general log    For more details visit this link  http   easysolutionweb com technology mysql-server-logs

User · Answer

I think the problem is making sure that MySQL server has the rights to the file and can edit it   If you can get it to have access to the file  then you can try setting  SET GLOBAL slow query log   1   If not  you can always  reload  the server after changing the configuration file  On linux its usually  etc init d mysql reload

User · Answer

MySQL Manual - slow-query-log-file  This claims that you can run the following to set the slow-log file  5 1 6 onwards    set global slow query log file    path     The variable slow query log just controls whether it is enabled or not

User · Answer

Try SET GLOBAL slow query log    ON   and perhaps FLUSH LOGS   This assumes you are using MySQL 5 1 or later  If you are using an earlier version  you ll need to restart the server  This is documented in the MySQL Manual  You can configure the log either in the config file or on the command line

User · Answer

This should work on mysql  gt  5 5  SHOW VARIABLES LIKE   long    SET GLOBAL long query time   1

User · Answer

For slow queries on version  lt  5 1  the following configuration worked for me   log slow queries  var log mysql slow-query log long query time 20 log queries not using indexes YES   Also note to place it under  mysqld  part of the config file and restart mysqld

User · Answer

These work  SET GLOBAL LOG SLOW TIME   1  SET GLOBAL LOG QUERIES NOT USING INDEXES   ON    Broken on my setup 5 1 42  SET GLOBAL LOG SLOW QUERIES   ON  SET GLOBAL SLOW QUERY LOG   ON  set   global log slow queries 1    http   bugs mysql com bug php id 32565  Looks like the best way to do this is set log slow time very high thus  turning off  the slow query log  Lower log slow time to enable it  Use the same trick  set to OFF  for log queries not using indexes

[mysql] How can I enable MySQL's slow query log without restarting MySQL?

Examples related to mysql