Following are 3 different ways to enable the slow log: 2 on mysqld startup and 1 while it’s running
1. Enable it in the config
That’s the most obvious way. You should locate your my.cnf config file (usually in /etc/ for most unix/linux based operating systems) and in it under the [mysqld] section add:
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
where log_slow_queries indicates the full file path in which the slow queries are going to be saved, long_query_time is the execution time in seconds after which the query will be considered as slow query and logged into the slow log. So, the example configuration above means that each query slower than 2 seconds is going to be logged into the /var/log/mysql/mysql-slow.log file.
If you want to go even further and catch any queries that aren’t using indexes, you could also add:
Now just start/restart the mysql daemon so the changes to take effect.
2. Using mysqld options
Another way to enable the slow log is by adding specific options to the mysqld startup command. For instance, the following will have the same effect as the example given above (for mysql versions up to 5.1.28):
mysqld --log-slow-queries=/var/log/mysql/mysql-slow.log --long_query_time=2
or for mysql versions 5.1.29 and newer (–log-slow-queries is depricated since mysql 5.1.29):
mysqld --slow_query_log=1 --slow_query_log_file=/var/log/mysql/mysql-slow.log --long_query_time=2
keep in mind that the exact options may vary depending on your exact mysql version, so if the above doesn’t work out, check your version mysql manual
3. Enable the slow log while mysqld is running
As of mysql 5.1.29 the slow log can be enabled dynamicly, so if you need to gather slow log just for short period of time and don’t want to restart mysqld, then you could do it by changing few global variables (e.g. setting these via the mysqlclient):
SET GLOBAL slow_query_log=1;
SET GLOBAL slow_query_log_file='/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time=2;
and later when you are done can disable it again by setting slow_query_log to 0:
SET GLOBAL slow_query_log=0;
Important: if for some reason the slow log is not created, make sure that mysqld has permissions to write in the log folder (/var/log/mysql/ in the examples above).