Trace the slow query log in MySQL

SLOW_QUERY_LOG variable controls logging to the slow query log for the selected destinations. slow_query_log_file specifies a file name for file logging.

1. Enable or disable log can be done at runtime also.
2. SQL statements that take more than long_query_time seconds to execute

mysql> SELECT @@long_query_time;
+-------------------+
| @@long_query_time |
+-------------------+
|         10.000000 |
+-------------------+
1 row in set (0.00 sec)

Check the value of parameter slow query log

Note: slow_query_log value 0 for disable or 1 for enable.

mysql> SELECT @@slow_query_log;
+------------------+
| @@slow_query_log |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

Enable or disable the slow_query_log in MySQL

-- Enable the slow query Log 
mysql> SET GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

--Disable the slow query log 
mysql> SET GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.00 sec)

Check the location of Slow Query log file

Note: -- C:\ProgramData\MySQL\MySQL Server 8.0\Data\slowquery.log

mysql> SELECT @@slow_query_log_file;
+-------------------------+
| @@slow_query_log_file   |
+-------------------------+
|           slowquery.log |
+-------------------------+
1 row in set (0.00 sec)

Change the location of slow query log file

mysql> SET GLOBAL slow_query_log_file='MYSQL-SLOW.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@slow_query_log_file;
+-----------------------+
| @@slow_query_log_file |
+-----------------------+
| MYSQL-SLOW.log        |
+-----------------------+
1 row in set (0.00 sec)

log_output system variable specifies the destination for log output.
Choose value from TABLE , FILE or NONE. If you choose TABLE then check the structure of table.

SET GLOBAL log_output='FILE';

SET GLOBAL log_output='TABLE';
-- IF you set to TABLE.
SHOW CREATE TABLE mysql.general_log;
SHOW CREATE TABLE mysql.slow_log;