How to enable slow query logs in MySQL without restarting it?

Products

• CA Spectrum
• CA eHealth

Issue/Introduction

For troubleshooting purposes, it can be necessary to discover what queries in MySQL are running slow.

Environment

Release : 10.3.x, 10.4.x

Component : Spectrum

Resolution

Here are the steps:

1- Connect on MySQL: 

Linux:

- Go to $SPECROOT/mysql/bin directory;

- Run the following command:

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot

Windows: 

- open a bash -login window;

- Run the following command:

./mysql -uroot -proot

2- Once connected, run the following commands:

mysql> SET GLOBAL long_query_time = X;    (where X is the number of seconds to consider a query as slow)

mysql> SET GLOBAL slow_query_log_file = '/tmp/slow_queries.log';  (if linux)

or

mysql> SET GLOBAL slow_query_log_file = 'C:\\tmp\\slow_queries.log';  (if windows)

PS: All the queries that take more than the X seconds configured above, will be logged into this file.

mysql> SET GLOBAL slow_query_log = 'ON'; (as soon as you run this command, you should see the 'slow_queries.log' file being created on the path configured above)

3- Once you have collected the desired data, you can disable the log generation by running the following command:

mysql> SET GLOBAL slow_query_log = 'OFF';