Enable MySQL General Query

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