Skip navigation links

Additional languages
5.3.3 The Update Log »
Section Navigation      [Toggle]

5.3.2. 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.

Older versions of the mysql.server script (from MySQL 3.23.4 to 3.23.8) pass a --log option to safe_mysqld to enable the general query log. If you need better performance when you start using MySQL in a production environment, you can remove the --log option from mysql.server or change it to --log-bin. See Section 5.3.4, “The Binary Log”.

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 update log and the binary log, which are written after the query is executed but before any locks are released. (Also, the query log contains all statements, whereas the update and binary logs do not contain statements that only select data.)

To enable the general query log, start mysqld with the --log[=file_name] or -l [file_name] option.

If no file_name value is given for --log or -l, the default name is host_name.log in the data directory.

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

On Windows, you cannot rename the log file 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.

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

The general query log should be protected because logged statements might contain passwords. See Section 5.6.6.1, “Administrator Guidelines for Password Security”.


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.