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

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

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

Environment

Release : Any

Component : Spectrum

Resolution

NOTE: Starting from DX NetOps Spectrum 21.2.4, the default root password for MySql is "MySqlR00t". For DX NetOps Spectrum versions prior to 21.2.4, the default root password is "root". In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

- Log into the system as the user that owns the Spectrum installation

- If on Windows, start a bash shell by running "bash -login"

- cd to the $SPECROOT/mysql/bin directory and enter the following command to log into mysql:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD>

- Enter the following command at the mysql prompt:

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)

- 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';