5.11.1. Server Log Tables
Before MySQL 5.1.6, the server writes general query log and slow
query log entries to log files. As of MySQL 5.1.6, the server's
logging capabilities for these logs are more flexible. Log
entries can be written to log files (as before) or to the
general_log and slow_log
tables in the mysql database. If logging is
enabled, either or both destinations can be selected.
The --log-output option specifies where log
output should be written, if logging is enabled. It does not
itself enable the logs. The syntax for this option is
--log-output[=value,...]:
If --log-output is given with a value, the
value can 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 effect is the same as
--log-output=TABLE. That is, the table
destination is selected.
The
--log[=file_name]
option, if given, enables logging to the general query log for
the selected log destinations. Similarly, the
--log-slow-queries[=file_name]
option, if given, enables logging to the slow query log for the
selected destinations. For both options, the filename is ignored
unless the FILE destination is selected.
If you specify the --log or
--log-slow-queries option, the server opens the
corresponding log file and writes startup messages to it.
However, query logging to the file does not occur unless the
FILE log destination is selected
Examples:
If you had the server configured for logging to log files
formerly, use --log-output=FILE to preserve
this behavior after an upgrade to MySQL 5.1.6 or higher.
To write general query log entries to the log table and the
log file, use --log-output=TABLE,FILE and
--log.
To write general and slow query log entries only to the log
tables, use --log-output=TABLE,
--log and
--log-slow-queries. (In this case, because
the default log destination is TABLE, you
could omit the --log-output option.)
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.
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:
SHOW CREATE TABLE mysql.general_log;
SHOW CREATE TABLE mysql.slow_log;
Log contents are accessible via SQL statements. This enables
the use of queries that select only those log entries that
satisfy specific criteria. For example, this can make it
easier to identify problem queries from a particular client.
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's not necessary to
log in to the server host and directly access the
filesystem.
Log entries can be expired by using TRUNCATE
TABLE.
The log tables use the CSV storage engine
that writes data in comma-separated values format. For those
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.
