Enable slow log query on MySQL

Question.

I want to Monitor the query which is taking longer execution time and killing server resource badly. Pls note that “set sql_log_off = ‘ON’; will not work on older version of Mysql 5.0.x.

Solution.

1. Create mysql log directory and permit mysql user to write on it
#mkdir /var/log/mysql
#touch /var/log/mysql/log-slow-queries.log
#chown mysql.mysql -R /var/log/mysql
2. Then stop the mysql server

3. Running mysql with slow log query logging enabled.
# mysqld_safe –log-slow-queries=/var/log/mysql/mysql-slow.log&
Or you can add the lines on my.cnf to enabling logging all the time mysql starts

and added the two lines set-variable=long_query_time=1 and log-slow-queries=/var/log/mysql/log-slow-queries.log under mysqld daemon.

then save

The following script will create a slow log query for the testing purpose

We can enable general query log for quick debugging on latest Mysql revision. What we have to do is just execute the command from the terminal. As it is a logging of all queries executed on the mysql, the file size will be increased drastically, So use it wisely. enable and disable for a short period.

This command will start to write sql query executed on a log file under data directory (/var/lib/mysql)

If you want to store the query on a table
you may choose this option

The above will showing enabling general_log at run time. But this is not a good approch to set for a long time since general logs are growing up rapidly. The best way is set this on a “my.cnf” file

How do I enable general_log in Mysql using my.cnf

Edit the file “my.cnf”

Here,
long_query_time, log-slow-queries : Used for catching mysql slow log when a query is taking more than 1 second to execute.
log : Used to logging all the mysql queries executed by database engine and this is only for debugging purpose.

You need to turn iff thus feature immediately after your testing. Otherwise this file would take bigger size and causing disk space issue.