how to enable slow query log in MySql

ENABLE MYSQL SLOW QUERY LOG

Mysql

MySQL is an open-source relational database management system. Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

Slow query log

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) 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

Use MySQLTuner script file for analyzes your MySQL performance.

 

MySQLTuner is a Perl script that analyzes your MySQL performance and get the information such as Slow queries, memory usage and logs based on the statistics.
It gives you recommendations which variables you should adjust in order to increase.

 

$ wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl -O /tmp/mysqltuner.pl

 

$ sudo su

$ cd /tmp

$ chmod +x mysqltuner.pl

$ ./mysqltuner.pl