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

0 votes
asked Mar 8, 2010 by mmattax

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?

8 Answers

0 votes
answered Jan 8, 2010 by jonathan

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

0 votes
answered Jan 13, 2010 by mikeytown2

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.

0 votes
answered Mar 8, 2010 by ian-gregory

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.

0 votes
answered Mar 8, 2010 by martin

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.

0 votes
answered Mar 24, 2011 by nitin

For slow queries on version < 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.

0 votes
answered Mar 14, 2015 by sivaprabu-ganesan

Find log enabled or not?

SHOW VARIABLES LIKE '%log%';

Set the logs:-

SET GLOBAL general_log = 'ON'; 

SET GLOBAL slow_query_log = 'ON'; 
0 votes
answered Jan 21, 2016 by gerard

This should work on mysql > 5.5

SHOW VARIABLES LIKE '%long%';

SET GLOBAL long_query_time = 1;

0 votes
answered Sep 15, 2017 by dilraj-singh

If you want to enable general error logs and slow query error log

To start logging in table instead of file

mysql > set global log_output = “TABLE”; To enable general and slow query log

mysql > set global general_log = 1; mysql > set global slow_query_log = 1; Table name in which logging is done by default

mysql > select * from mysql.slow_log; mysql > select * from mysql.general_log; For more details visit this link

http://easysolutionweb.com/technology/mysql-server-logs/

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...