5.2.1 Selecting General Query and Slow Query Log Output Destinations

As of MySQL 5.1.6, MySQL Server provides flexible control over the
destination of output to the general query log and the slow query
log, if those logs are enabled. Possible destinations for log
entries are log files or the general_log and
slow_log tables in the mysql
database. Either or both destinations can be selected. (Before
MySQL 5.1.6, the server uses only log files as the destination for
general query log and slow query log entries.)

Note

For new installations of MySQL 5.1.6 or higher, the log tables
are created during the installation procedure along with the
other system tables. If you upgrade MySQL from a release older
than 5.1.6 to MySQL 5.1.6 or higher, you must upgrade the system
tables after upgrading to make sure that the log tables exist.
See Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.

Currently, logging to tables incurs significantly more server
overhead than logging to files. If you enable the general log or
slow query log and require highest performance, you should use
file logging, not table logging.

Log control at server startup.
The --log-output option specifies
the destination for log output, if logging is enabled. This option
does not in itself enable the logs. Its syntax is
--log-output[=value,...]:

If --log-output is given with a
value, the value should be a comma-separated list of one or
more of the words TABLE (log to tables),
FILE (log to files), or
NONE (do not log to tables or files).
NONE, if present, takes precedence over any
other specifiers.

If --log-output is omitted or
given without a value, the default is FILE.
(For MySQL 5.1.6 through 5.1.20, the default logging
destination is TABLE.)

The general_log system variable
controls logging to the general query log for the selected log
destinations. If specified at server startup,
general_log takes an optional
argument of 1 or 0 to enable or disable the log. To specify a file
name other than the default for file logging, set the
general_log_file variable.
Similarly, the slow_query_log
variable controls logging to the slow query log for the selected
destinations and setting
slow_query_log_file specifies a
file name for file logging. If either log is enabled, the server
opens the corresponding 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.
Prior to MySQL 5.1.29, the --log
and --log-slow-queries options
enable the general query log and slow query log. Either option may
be given with a file name argument to specify a log file name to
override the default.

Examples:

To write general query log entries to the log table and the
log file, use
--log-output=TABLE,FILE to
select both log destinations and
--general_log to enable the
general query log.

To write slow query log entries only to the log file, use
--log-output=FILE to select
files as the log destination and
--slow_query_log to enable the
slow query log. (In this case, because the default log
destination is FILE, you could omit the
--log-output option.)

Log control at runtime. The
system variables associated with log tables and files enable
runtime control over logging:

The global log_output system
variable indicates the current logging destination. It can be
modified at runtime to change the destination.

The global general_log and
slow_query_log variables
indicate whether the general query log and slow query log are
enabled (ON) or disabled
(OFF). You can set these variables at
runtime to control whether the logs are enabled.

The global general_log_file
and slow_query_log_file
variables indicate the names of the general query log and slow
query log files. As of MySQL 5.1.29, you can set these
variables at server startup or at runtime to change the names
of the log files. Before MySQL 5.1.29, you can set these
variables only at runtime, but the
--log and
--log-slow-queries options can be given
with a file name argument at startup to change the log file
names from their default values.

To disable or enable general query logging for the current
connection, set the session
sql_log_off variable to
ON or OFF.

The use of tables for log output offers the following benefits:

Log entries have a standard format. To display the current
structure of the log tables, use these statements:

Log contents are accessible through SQL statements. This
enables the use of queries that select only those log entries
that satisfy specific criteria. For example, to select log
contents associated with a particular client (which can be
useful for identifying problematic queries from that client),
it is easier to do this using a log table than a log file.

Logs are accessible remotely through any client that can
connect to the server and issue queries (if the client has the
appropriate log table privileges). It is not necessary to log
in to the server host and directly access the file system.

The log table implementation has the following characteristics:

In general, the primary purpose of log tables is to provide an
interface for users to observe the runtime execution of the
server, not to interfere with its runtime execution.

By default, the log tables use the CSV
storage engine that writes data in comma-separated values
format. For users who have access to the
.CSV files that contain log table data,
the files are easy to import into other programs such as
spreadsheets that can process CSV input.

Beginning with MySQL 5.1.12, the log tables can be altered to
use the MyISAM storage engine. You cannot
use ALTER TABLE to alter a log
table that is in use. The log must be disabled first. No
engines other than CSV or
MyISAM are legal for the log tables.

To disable logging so that you can alter (or drop) a log
table, you can use the following strategy. The example uses
the general query log; the procedure for the slow query log is
similar but uses the slow_log table and
slow_query_log system
variable.

Partitioning of log tables is not permitted beginning with
MySQL 5.1.20, and not recommended before that.

Before MySQL 5.1.64, mysqldump does not
dump the general_log or
slow_query_log tables for dumps of the
mysql database. As of 5.1.64, the dump
includes statements to recreate those tables so that they are
not missing after reloading the dump file. Log table contents
are not dumped.