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
slow_log tables in the
database. Either or both destinations can be selected.
Before MySQL 5.5.7, 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-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
--log-output is omitted, the
default logging destination is
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
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
FILE log destination is selected.
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
FILE, you could omit the
Log control at runtime. The system variables associated with log tables and files enable runtime control over logging:
variable indicates the current logging destination. It can be
modified at runtime to change the destination.
indicate whether the general query log and slow query log are
ON) or disabled
OFF). You can set these variables at
runtime to control whether the logs are enabled.
variables indicate the names of the general query log and slow
query log files. You can set these variables at server startup
or at runtime to change the names of the log files.
To disable or enable general query logging for the current
connection, set the session
sql_log_off variable to
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 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
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.
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
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
SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE mysql.general_log ENGINE = MyISAM; SET GLOBAL general_log = @old_log_state;
TRUNCATE TABLE is a valid
operation on a log table. It can be used to expire log
RENAME TABLE is a valid
operation on a log table. You can atomically rename a log
table (to perform log rotation, for example) using the
USE mysql; DROP TABLE IF EXISTS general_log2; CREATE TABLE general_log2 LIKE general_log; RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;
As of MySQL 5.5.7,
is a valid operation on a log table.
LOCK TABLES cannot be used on a
Entries written to the log tables are not written to the binary log and thus are not replicated to slave servers.
Partitioning of log tables is not permitted.
Before MySQL 5.5.25, mysqldump does not
slow_query_log tables for dumps of the
mysql database. As of 5.5.25, 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.