Enable MySQL’s slow query log without a restart – onlinecode

Enable MySQL’s slow query log without a restart – onlinecode

In this post we will give you information about Enable MySQL’s slow query log without a restart – onlinecode. Hear we will give you detail about Enable MySQL’s slow query log without a restart – onlinecodeAnd how to use it also give you demo for it if it is necessary.

In this post, I will tell you how to enable MySQL’s slow query log to see which query takes longer to execute.

This is happen because of coding mistakes and using sloq query log you can easily find out those queries and make them easy to debug your application faster.

There are two way for getting slow query log.

Either you can directly edit your my.cnf file and restart your mysql service or you can go through MySQL CLI via mysql.


Using MySQL CLI

Create slow query log file.


mkdir /var/log/mysql/
touch /var/log/mysql/mysql-slow.log
chown -R mysql:mysql /var/log/mysql/



Log in to the MySQL CLI via mysql.

$ mysql


Now you have mysql-sloq.log file which is need to set for slow query log.

mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; 

Now set the limit so that query is logged to the slow query log after limit exceeds.

mysql> SET GLOBAL long_query_time = 10; 

Above example will logs each query that exceeds 10 seconds in duration.


Now you will have to enable the sloq query log.


mysql> SET GLOBAL slow_query_log = 'ON';
mysql> FLUSH LOGS; 


If you wish, you can log only those query that do not use indexes.

mysql> SET GLOBAL log_queries_not_using_indexes = 'YES'; 


Using my.cnf file

To enable slow query log you can also make changes in my.cnf file.,


[mysqld]
...
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 10
log_queries_not_using_indexes = YES



To check if these settings are working fine, run following query from mysql.

mysql> SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

You will get following output :


+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+


mysql> SHOW GLOBAL VARIABLES LIKE 'slow_%';


+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+-------------------------------+
	

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';


+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+



If you need to disable the logging, use following query.


mysql> SET GLOBAL slow_query_log = 'OFF';
mysql> FLUSH LOGS; 


You can directly disable logging in my.cnf


[mysqld]
...
slow_query_log = 0
long_query_time = 10
log_queries_not_using_indexes = YES


Now you can see all query in log file that take long time to execute.

Label :

MySQL

How To

Apache

Web Development

Database

Hope this code and post will helped you for implement Enable MySQL’s slow query log without a restart – onlinecode. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

6  +  2  =  

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US