Enabling query log in MySQL 5.5.28

Enabling query log in  MySQL Version : 5.5.28

MySQL has very strong system to log queries and events, It allows you to log 
1. General queries - General record of mysqld activities.
2. Binary log  - These are nothing but events describing the changes in database e.g. table creation. It plays important role during database replication.

3. Error logs - It logs all critical errors during of running MySQL server. MySQL is smart , if it finds any table that needs automatic repair then it updates error log file with respected message.

4. Slow query log - MySQL can store SQL statements which takes more time than long_query_time seconds, defined in my.cnf file, which is configuration file of MySQL server. The default value of this variable is 10. This is very important step in order to optimize the MySQL server performance by identifying slow queries.

That's not enough about log, but just gave you rough idea about its log system, lets see how to enable different logs in MySQL.

1. General Query Log - 
OS : Ubuntu 12.04.1 LTS
MySQL version : 5.5.28

1. Locate and edit my.cnf file. We need to change two variables in this file to enable general query log.  
    a.  sudo gedit /etc/mysql/my.cnf
    b.  Locate this variable in the file : general_log_file
    c.  It must have some default value, this variable is nothing but the path of log file.
    d.  The default value is "/var/log/mysql/mysql.log", you can change it as per your wish.
    e.  Now locate variable "general_log", and set it to 1. The means turning on the log system.
    f.  Now restart the MySQL server by running : sudo service mysql restart
    d. now if you open the mysql.log file, you can see all SQL queries in it. :)

Will update the steps for other log system soon. :)


Post a Comment

Share this post with your friends