Developer's Notepad

When you are developing any application with database, it is very helpful to have log of all database queries. This is also helpful if you use any ORM (Object Relational Mapping) tool. By looking at query log, you can actually see which queries are being executed against the database, how long they perform to fetch results, etc. This information will help developers to write better database queries.

I have searched across web about how to enable general query logging in MySQL. There are many resources on that topic but it takes a little effort to find an exact answer. Even MySQL documentation is not that helpful. So here is the straight forward answer to that question. I am using my.cnf in this configuration.

Open my.cnf and add following lines:

general_log = 1 
log_output = TABLE 

Here we are using MySQL table for all general logs. If you want to use a file, please use the following settings instead. Also make sure that the user under which MySQL server is running has write access to log file.

general_log = 1
log_output = FILE
general_log_file = /full/path/to/general_log.log

Now we need to create table in MySQL database for general logging. Please login to MySQL database root and using following SQL commands create general_log table if it does not exist.

CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(11) NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Now restart the MySQL server to enable all changes. If you don’t want to restart the server and apply changes, execute following commands in MySQL shell,

SET GLOBAL log_output='TABLE'; 
SET GLOBAL general_log='ON';

Note: If you want to use file based logging, use log_output = ‘FILE’ instead.

Execute few queries and let the server run for some times. Then login to MySQL server and run select queries on general_log table. You will find many helpful information here. You can also extract query data to file and analyze it.

If you have any suggestion, please let me know.