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.
- Enable or disable log can be done at runtime also.
- 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;