Enable slow query log without restarting MySQL / MariaDB server

We noticed load on one of our production servers recently and had to enable slow query log for our MariaDB server. Since this was a server in production, we feared that if we tried restarting the MariaDB server that it won’t come back online or might take longer than acceptable. Diagnosing with the help of the slow query log can also help speed up your website.

So we went in and enabled the slow query log without restarting the server, this is how we did it.

Start by logging into your server via SSH and make sure the folder for the log file is created, if not then do this:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

Next, log into your MySQL server as root or a user with superuser privileges:

#If you have authentication set
mysql -u root -p

#Otherwise
mysql

Set the log file location:

MariaDB [(none)]> SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Set the slow query duration, how long should a query run to be logged. For our purposes we’re setting it at 2 seconds, so a query which takes longer than 2 seconds to process will be logged

MariaDB [(none)]> SET GLOBAL long_query_time = 2;

Next, we enable our new settings

MariaDB [(none)]> SET GLOBAL slow_query_log = 'ON';
MariaDB [(none)]> FLUSH LOGS;

Head over to /var/log/mysql and if you should see queries in the log file taking longer than 2 seconds to complete.

These settings will not survive a restart, to make them permanent edit the mysql config file.

On  our server, it was at /etc/my.cnf.d/server.conf, locate and add the following in the [mysql] section

[mysqld]

slow_query_log = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

That’s it, you’re done!