Skip navigation links

Additional languages
5.2.4 The Binary Log »
Section Navigation      [Toggle]

5.2.3. The General Query Log

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

Before 5.1.6, the general query log destination is always a file. To enable the general query log file, use the --log[=file_name] or -l [file_name] option. To enable the general query log as of MySQL 5.1.6, start mysqld with the --log[=file_name] or -l [file_name] option, and optionally use --log-output to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”). As of MySQL 5.1.29, --log and -l are deprecated: Use --general_log to enable the general query log, and optionally --general_log_file=file_name to specify a log filename. --general_log takes an optional argument of 1 or 0 to enable or disable the log.

If you specify no filename for the general query log, the default name is host_name.log in the data directory. If you specify a filename that is not an absolute pathname, the server writes the file in the data directory.

When --log or -l is specified, the --general_log option also may be given as of MySQL 5.1.12 to specify the initial general query log state. With no argument or an argument of 0, the option disables the log. If omitted or given with an argument of 1, the option enables the log.

For runtime control of the general query log, use the global general_log and general_log_file system variables. Set general_log to 0 (or OFF) to disable the log or to 1 (or ON) to enable it. Set general_log_file to specify the name of the log file. If a log file already is open, it is closed and the new file is opened.

When the general query log is enabled, output is written to any destinations specified by the --log-output option or log_output system variable. If you enable the log, the server opens the log file and writes startup messages to it. However, logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, no queries are written even if the general log is enabled. Setting the log filename has no effect on logging if the log destination value does not contain FILE.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands: 

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> cp host_name-old.log backup-directory
shell> rm host_name-old.log

Before 5.1.3, you cannot rename a log file on Windows while the server has it open. You must stop the server and rename the file, and then restart the server to create a new log file. As of 5.1.3, this applies only to the error log. However, a stop and restart can be avoided by using FLUSH LOGS, which causes the server to rename the error log with an -old suffix and open a new error log.

As of MySQL 5.1.12, you can disable the general query log at runtime: 

SET GLOBAL general_log = 'OFF';

With the log disabled, rename the log file externally; for example, from the command line. Then enable the log again: 

SET GLOBAL general_log = 'ON';

This method works on any platform and does not require a server restart.

The session sql_log_off variable can be set to ON or OFF to disable or enable general query logging for the current connection.


User Comments

Posted by Paul Eckert on October 23 2003 5:35am[Delete] [Edit]

Note that if you use --log=/var/log/mysqld.log and the mysql user doesn't have privileges in that directory, you can work around this by:
(1) becoming root:
su
(2) changing to the target directory:
cd /var/log
(3) creating the log initially
touch mysqld.log
(4) allowing anyone to write to it:
chmod 777 mysqld.log
(5) restarting mysql

This solved a problem I was having where mysqld would not create the initial log file even with the proper command-line args, but wouldn't complain about it, either. It would just silently go on.

Posted by [name withheld] on November 12 2003 9:14am[Delete] [Edit]

> (4) allowing anyone to write to it:
> chmod 777 mysqld.log
It would be better to change the file's owner to "mysql" or whatever user your server is running as (perhaps "nobody"), instead of making it world-writable.
chown mysql mysqld.log

Posted by Herb Rubin on April 16 2004 11:17am[Delete] [Edit]

Add a log statement to your /etc/my.cnf file instead of the command line:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log=/var/log/mysqld.log

Posted by Mike Chirico on June 6 2004 7:15pm[Delete] [Edit]

/etc/my.cnf

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.log
log = /usr/local/var/mysqlLOG.log


Above the "binlog" or binary log is /usr/local/var/mysqlLOGb.log
which works as follows in 4.1 and above

mysql> show binlog events;

or

mysql> show binlog events from 201 limit 2;

Reference (TIP 24, TIP 25)
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download


The above link shows examples, plus how to create a C API that will run queries on the log.

It appears that the binary log and the text log are not the same. The text log lists the username along with the event, whereas the bin-log does not. However, the bin-log has the advantage of listing the event number. Again see link above for a full example.

Mike Chirico



Posted by Karen Owen on May 31 2006 12:33pm[Delete] [Edit]

If you are coming from a 5.0 environment to 5.1, the behaviour of the 'log' parameter in the my.cnf options file is different. Simply placing log=/directory_name/query.log
in my.cnf and then restarting will not log queries to the operating system file called query.log. It appears that in 5.1.9, by default, queries will be logged to the new mysql.general_log table in the mysql schema if you don't have the new log-output parameter set, but *not* to the file at the OS level. If you want your queries to be logged to an OS file as well as to a schema table, then use the following option in your options file:

log-output = FILE,TABLE

If you only want to log to an OS file and not a table, to save space in the database, then simply use:

log-output = FILE

Without this option (at least in 5.1.6), the only messages logged to query.log are server startups.

Posted by Tomer Petel on October 16 2006 6:58pm[Delete] [Edit]

Well you should also mention that when using safe_mysqld, you will be running as the mysql user, which may not have write access to /var/log/ Thus, you may want to set the log parameter to log to /home/mysql/mysqld.log or change permissions of the mysql user. Really should have been mentioned in this article, though

Posted by Axel Berhardt on December 4 2006 9:46pm[Delete] [Edit]

seems to me logging does not work at all in mysql-5.1.12-beta-win32 !!!
Took me a day to believe that. Returned to 5.0.27

Add your own comment.