Enable MySQL General Query And Slow Query Log

MySQL has a query logging feature. In order to use it you have to first enable it.

Enabling the general query log

Step 1: Set your log file in /etc/my.cnf

vi /etc/my.cnf

In the [mysqld] section specify the general log file name:

log=/var/log/mysqld.general.log

Step 2: Create the file and make sure it is owned by the system user mysql

touch /var/log/mysqld.general.log
chown mysql.mysql /var/log/mysqld.general.lo

Step 3: Enable the general log in the MySQL client. Connect to the MySQL server using the MySQL client and execute this query.

SET GLOBAL general_log = 'ON';

Step 4: Restart mysqld and watch the logs

/etc/init.d/mysqld restart

To watch the logs use the tail command.

tail -f /var/log/mysqld.general.log

A sample entry in my general query log looks like:

[root@localhost ~]# 
[root@localhost ~]# tail -f /var/log/mysqld.general.log 
091012 13:52:53     2 Query SELECT `profile`.* FROM `profile` WHERE (user_id = 1) LIMIT 1
      2 Query SELECT `u`.`user_id`, `u`.`email`, `u`.`status`, `u`.`mode`, `u`.`hash`, `u`.`created`, `u`.`alt_email`, `u`.`host_created`, `u`.`ip_created`, `u`.`user_timezone`, `p`.*, `r`.`name` AS `primaryRoleName`, `urp`.`email` AS `reportsToEmail`, `b`.`branch_name` FROM `user` AS `u`
 LEFT JOIN `profile` AS `p` ON p.user_id = u.user_id
 LEFT JOIN `role` AS `r` ON r.role_id = p.primary_role
 LEFT JOIN `user` AS `urp` ON urp.user_id = p.reports_to
 LEFT JOIN `branch` AS `b` ON b.branch_id = p.branch_id WHERE (u.user_id = '1')
      2 Query SELECT `profile`.* FROM `profile` WHERE (user_id = 1) LIMIT 1
      2 Query SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices\"') LIMIT 1
      2 Query SELECT * FROM organization_details
      2 Quit 

Enabling the slow query log

Similarly, you can enable the slow log queries. MySQL reports how much time it took to execute a query.
Step 1: Enable slow log query, set a slow log query log file, and set the log_query_time in my.cnf.
Edit the MySQL configuration file.

vi /etc/my.cnf

In the [mysqld] section add/edit the following variables

long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysqld.slow.query.log

We are setting the log_query_time variable to 0. Any MySQL query taking more than 0 seconds will be logged. Step 2: Create the file /var/log/mysqld.slow.query.log and set the system user mysql as the owner.

touch /var/log/mysqld.slow.query.log
chown mysql.mysql /var/log/mysqld.slow.query.log

Step 3: Restart MySQL server

/etc/init.d/mysqld restart

Step 4: Watch the slow query log using the tail command.

tail -f /var/log/mysqld.slow.query.log

A sample entry on my server looks like this:

[root@localhost ~]# tail -f /var/log/mysqld.slow.query.log 
SET timestamp=1255345490;
SELECT `privilege`.* FROM `privilege` WHERE ( name = 'view service invoices\"') LIMIT 1;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000273  Lock_time: 0.000104 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
SELECT * FROM organization_details;
# User@Host: biz_1[biz_1] @ localhost []
# Query_time: 0.000048  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1255345490;
# administrator command: Quit;

If you observe the log entry, the last query took 0.000048 seconds.