Enabling the Slow Query Log in MySQL or MariaDB on Linux can help you identify queries that take a long time to execute, which can be crucial for optimizing performance and it might be efficiency issues affecting your server, . Here’s how you can enable the Slow Query Log:

Enable the Slow Query Log on MySQL or MariaDB:

You can enable the Slow Query Log for MySQL or MairaDB Databases. Need to login to your Instannce or Server as the Root user account via SSH.

Open the MySQL configuration file named “my.cnf” with any text editor and add the following lines under the “mysqld” session, here I’m usiing VIM editor,

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1

• slow_query_log enables the slow query log.
• slow_query_log_file specifies the file where the slow queries will be logged.
• long_query_time sets the threshold (in seconds) for what constitutes a slow query. Adjust this value as needed.
• log_queries_not_using_indexes (optional) logs queries that are not using indexes, which can help in optimizing your database.

Next, Create a new file and named mysql-slow-query.log using the command “touch” in /var/log directory and set its user as the MySQL using the command “chown”,

$ sudo touch /var/log/mysql-slow-query.log
 
$ sudo chown mysql:mysql /var/log/mysql-slow-query.log

Restart MySQL or MariaDB database using the command below

service mysql {stop | start | restart | status}

$ sudo service mysql stop 
$ sudo service mysql start