how to enable slow query log in MySql

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.

1️⃣ Enable Slow Query Log Permanently (Service Restart)

Edit MySQL configuration file:

• /etc/mysql/my.cnf Or /etc/mysql/mysql.cnf Or /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following lines under [mysqld]:

• slow_query_log = 1
• slow_query_log_file = /var/log/mysql/slow.log
• long_query_time = 2
• log_queries_not_using_indexes = 1  # (Optional) Log queries without indexes

sudo systemctl restart mysql

Verify Slow Query Log is Enabled

• SHOW VARIABLES LIKE 'slow_query_log%';
• SHOW VARIABLES LIKE 'long_query_time';

View Slow Query Logs

• tail -f /var/log/mysql/slow.log

2️⃣ Enable Slow Query Log Temporarily (Without Restart)

1) Login in mysql 

2) Enable/on slow query log 

$  SET GLOBAL slow_query_log = 'ON';

3) Set slow query time in Sec example (1 sec, 2 sec, 3 sec 4 sec....)

$  SET GLOBAL long_query_time = 1;

4) Set slow query log path

$ SET GLOBAL slow_query_log_file = '/var/log/mysql/slow_query.log';

5) Exit from the Mysql 

$ exit

6) Check slow query file in path

$ tail -f /var/log/mysql/slow_query.log

OR

$ cat /var/log/mysql/slow_query.log