Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.0Mb
PDF (A4) - 33.0Mb
PDF (RPM) - 31.0Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.7Kb
Man Pages (Zip) - 203.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb

MySQL 8.0 Reference Manual  /  ...  /  The General Query Log

Pre-General Availability Draft: 2017-08-22

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

Each line that shows when a client connects also includes using connection_type to indicate the protocol used to establish the connection. connection_type is one of TCP/IP (TCP/IP connection established without SSL), SSL/TLS (TCP/IP connection established with SSL), Socket (Unix socket file connection), Named Pipe (Windows named pipe connection), or Shared Memory (Windows shared memory connection).

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 is in contrast with that of the binary log, for which statements are written after they are executed but before any locks are released. In addition, the query log may contain statements that only select data while such statements are never written to the binary log.

When using statement-based binary logging on a replication master server, statements received by its slaves are written to the query log of each slave. Statements are written to the query log of the master server if a client reads events with the mysqlbinlog utility and passes them to the server.

However, when using row-based binary logging, updates are sent as row changes rather than SQL statements, and thus these statements are never written to the query log when binlog_format is ROW. A given update also might not be written to the query log when this variable is set to MIXED, depending on the statement used. See Section, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”, for more information.

By default, the general query log is disabled. To specify the initial general query log state explicitly, use --general_log[={0|1}]. With no argument or an argument of 1, --general_log enables the log. With an argument of 0, this option disables the log. To specify a log file name, use --general_log_file=file_name. To specify the log destination, use --log-output (as described in Section 5.4.1, “Selecting General Query and Slow Query Log Output Destinations”).

If you specify no name for the general query log file, the default name is host_name.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

To disable or enable the general query log or change the log file name at runtime, 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, the server writes output 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, further logging of queries to the file does not occur unless the FILE log destination is selected. If the destination is NONE, the server writes no queries even if the general log is enabled. Setting the log file name 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). To rename the file and create a new one, use the following commands:

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

On Windows, use rename rather than mv.

You can also rename the general query log file at runtime by disabling the log:

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.

Passwords in statements written to the general query log are rewritten by the server not to occur literally in plain text. Password rewriting can be suppressed for the general query log by starting the server with the --log-raw option. This option may be useful for diagnostic purposes, to see the exact text of statements as received by the server, but for security reasons is not recommended for production use. See also Section, “Passwords and Logging”.

An implication of password rewriting is that statements that cannot be parsed (due, for example, to syntax errors) are not written to the general query log because they cannot be known to be password free. Use cases that require logging of all statements including those with errors should use the --log-raw option, bearing in mind that this also bypasses password rewriting.

Password rewriting occurs only when plain text passwords are expected. For statements with syntax that expect a password hash value, no rewriting occurs. If a plain text password is supplied erroneously for such syntax, the password is logged as given, without rewriting. For example, the following statement is logged as shown because a password hash value is expected:

CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'not-so-secret';

The log_timestamps system variable controls the time zone of timestamps in messages written to the general query log file (as well as to the slow query log file and the error log). It does not affect the time zone of general query log and slow query log messages written to log tables, but rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

User Comments
  Posted by Jason Bibbings on January 14, 2011
On Windows, I could not get the Log Files to work using the default path of "C:/Program Files (x86)/MySQL/MySQL Server 5.1"

This was on Windows Server 2008 using 32-bit MySQL version 5.1.49

Moving the logs out of that directory and into the root C: fixed the issue for me. All I can figure is the logs don't like the parentheses in (x86), I've seen this a couple times before.

Anyway if it helps anyone, my current working my.ini file on Windows now looks like this:

# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = "C:/slowquery.log"

#Set General Log
log = "C:/genquery.log"

  Posted by Greg Schretter on November 16, 2011
Correct most system do not like "()" also the "." might cause issues, but it might be a good idea to have the files written some where else besides root. Maybe C:\MySql\Logs\(different sub-directory names base on category). Root should be locked down on Windows servers. Hope this helps..

  Posted by Jakob Langgaard on April 21, 2012
The reason why the log file won't work in "C:/Program Files (x86)/MySQL/MySQL Server 5.1" is because programs need to be run with administrator privileges to be able to write or modify files within the program files folder.
  Posted by Programmer Old on April 3, 2013
Find "datadir" in "my.ini": for me the line is
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

Likly that something along this path is what you want
  Posted by Sebastian Kaiser on September 9, 2015
Example: Cleanup log:
I am logging into my database itself... but after 1 day, there will be about 180k queries of log. ( in a file, it would be 30MB per day <.<)
This script is an example to keep the table clean ;) even if you forgot it for a week or 2 you can cleanup even then ;)
You need to add an additional column (event_unix) and then you can use this script to keep the log clean... it will update the timestamp into a Unix-timestamp, delete the logs older than 1 day and then (i don't know why, but in my log, the event_time is setted into the actual time) update the event_time into Timestamp from event_unix... sounds a bit confusing, but it's working great :D Would use it as an event, but the event is disallowe the change from general_log...

Commands for the new column:
SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
ALTER TABLE `general_log_temp`
ADD COLUMN `event_unix` int(10) NOT NULL AFTER `event_time`;
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';

cleanup script =>

SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
UPDATE general_log_temp SET event_unix = UNIX_TIMESTAMP(event_time);
DELETE FROM `general_log_temp` WHERE `event_unix` < UNIX_TIMESTAMP(NOW()) - 86400;
UPDATE general_log_temp SET event_time = FROM_UNIXTIME(event_unix);
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';

Hope you like it ;)

Regards, S.K.
Sign Up Login You must be logged in to post a comment.