Why, oh why, is this so complicated?!
Well, it’s not… just a bit confusing when you don’t know where to look.
TL,DR: (Ubuntu 16.04, otherwise YMMV, read below)
Add the following to your active configuration file for mysql
[mysqld]
slow_query_log
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
What could go wrong?
Wrong configuration file:
on Ubuntu 16.04 the file can be in /etc/mysql/my.cnf or /etc/mysql/conf.d/mysql.cnf or even /etc/mysql/mysql.conf.d/mysqld.cnf
to find which one is “active”, run: mysqld --verbose --help |grep -A 1 "Default options"
the result is something like the following:
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
if you open /etc/mysql/my.cnf, you will find at the bottom:
!includedir /etc/mysql/conf.d/
That’s where the actual files are stored.
Wrong variable names:
If you follow different tutorials on the Internet you will find configurations that mention:
log_slow_queries=/var/log/mysql/slow-query.log
DO NOT use that, it’s deprecated.
the new name of the variable is `slow_query_log_file` for the actual log file, and YOU SHOULD have `slow_query_log` as a boolean variable (ON, 1, or just mentioned in the file as per the code above).
Troubleshooting
Tail the /var/log/mysql/error.log in a separate terminal, see what’s failing. Example:
[Warning] option 'slow_query_log': boolean value '/var/log/mysql/mysql-slow.log' wasn't recognized. Set to OFF.
Obviously the variable is set wrongly to the filename. They are now 2 separate variables.