How do I enable the MySQL slow query log?

Question

My MySQL version details are

@N.B. things are, if you make your google search nowadays, this question is the second result. The turns of life... — Alter Lagos, Nov 14 '17 at 03:03

score 155 · Answer 1 · edited May 04 '18 at 21:50

Version 5.1.6 and above:

1. Enter the MySQL shell and run the following command:

set global slow_query_log = 'ON';

2. Enable any other desired options. Here are some common examples:

Log details for queries expected to retrieve all rows instead of using an index:

   set global log_queries_not_using_indexes = 'ON'

Set the path to the slow query log:

  set global slow_query_log_file ='/var/log/mysql/slow-query.log';

Set the amount of time a query needs to run before being logged:

   set global long_query_time = 20;
     (default is 10 seconds)

3. Confirm the changes are active by entering the MySQL shell and running the following command:

show variables like '%slow%';

Versions below 5.1.6:

Edit the /etc/my.cnf file with your favorite text editor vi /etc/my.cnf
Add the following line under the “[mysqld]” section. Feel free to update the path to the log file to whatever you want:

log-slow-queries=/var/log/mysql/slow-query.log

3. Enable additional options as needed. Here are the same commonly used examples from above:

Set the amount of time a query needs to run before being logged:

  `long_query_time=20
  (default is 10 seconds)`

Log details for queries expected to retrieve all rows instead of using an index:

 `log-queries-not-using-indexes`

4. Restart the MySQL service:

service mysqld restart

5. Confirm the change is active by entering the MySQL shell and running the following:

show variables like '%slow%';

Update:1

According to MySQL docs, the error #1193 occurs when you use wrong code for SQLSTATE.

Message: Unknown system variable %s

And, as you can see on the same page, the SQLSTATE 99003 is not defined.

refer this link:

what to do above error is display while "set global slow_query_log = 'ON';" command execute over phpmyadmin — John, Mar 24 '14 at 13:00
When set the amount of time a query needs to run before being logged, if error occured: ERROR 1232 (42000): Incorrect argument type to variable , use this instead : `set global long_query_time = 5;` — Jack47, Feb 22 '17 at 03:19
set global long_query_time = '20'; make error in mysql, don't quote the number please — S. Ali Mihandoost, Jun 28 '17 at 09:51
Once you have it enabled, you'll quickly realize that it's really hard to read. I made this to help me turn the MySQL slow query log into a couple of digestible spreadsheets: https://github.com/CherryCircle/MySQLSlowLogAnalyzer — Ryan Shillington, Jan 24 '20 at 23:28
Beware that restarting the MySQL service sometimes resets the variables. — WoodrowShigeru, Jul 14 '21 at 16:00

score 8 · Answer 2 · answered Mar 24 '14 at 12:26

8

If your server is above 5.1.6 you can set the slow query log in the runtime itself. For which you have to execute this queries.

set global log_slow_queries = 1;
set global slow_query_log_file = <some file name>;

Or alternatively you can set the this options in the my.cnf/my.ini option files

log_slow_queries = 1; 
slow_query_log_file = <some file name>;

answered Mar 24 '14 at 12:26

Sathish D

Got error #1238 - Variable 'log_slow_queries' is a read only variable – John Mar 24 '14 at 12:57
Since MySQL version less that 5.1 check this http://stackoverflow.com/questions/2403793/how-can-i-enable-mysqls-slow-query-log-without-restarting-mysql – Sathish D Mar 24 '14 at 13:00
Since 5.6.1, `log_sloq_queries` is replaced by `slow_query_log` - https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_slow_query_log – Zathrus Writer Aug 28 '17 at 20:18

2 Answers2