MySQL AB :: MySQL 5.1 Reference Manual :: 5.11.1 Selecting General Query and Slow Query Log Output Destinations

5.11.1. Selecting General Query and Slow Query Log Output Destinations

Before MySQL 5.1.6, the server uses log files as the destination for general query log and slow query log entries, if those logs are enabled. As of MySQL 5.1.6, the server provides more flexible control over the destination for log output. 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 the destination for log output, if logging is enabled. The option 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. (This differs from the default destination of using files before MySQL 5.1.6.)
If --log-output option also sets the value of the global log_output system variable, which can be modified at runtime to change the logging destination for a server while it executes.

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.

Also, the default log destination changes from files to tables as of MySQL 5.1.6. If you had the server configured for logging to log files formerly, use --log-output=FILE to preserve this behavior after an upgrade to 5.1.6 or higher.

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:

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 the --log option to turn on the general query log.
To write general and slow query log entries only to the log tables, use --log-output=TABLE to select tables as the log destination and the --log and --log-slow-queries options to enable both logs. (In this case, because the default log destination is TABLE, you could omit the --log-output option.)

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, it becomes easier to select log contents associated with a particular client, which can be useful for identifying problematic queries from that 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.

By default, 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.

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.

The use of DROP TABLE for log tables is similarly restricted: It cannot be used to drop a log table that is in use. The log must be disabled first.

To disable logging so that you can modify a log table, you can use the following strategy:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
SET GLOBAL slow_query_log = @old_log_state;

Beginning with MySQL 5.1.12, FLUSH TABLES ignores log tables. To flush the log tables, use FLUSH LOGS instead.

Beginning with MySQL 5.1.13, you can atomically rename a log table (to perform log rotation, for example) using the following strategy:

USE mysql;
CREATE TABLE IF NOT EXISTS general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;

User Comments

Posted by Ricky Sun on December 31 2006 2:28am

[Delete] [Edit]

As of mysql-server-5.1.12-beta:

Database table: mysql.slow_log SEEMS to be the place holding all queries INSTEAD OF just slow queries as pre-defined in ${mysql_dbdir}/my.cnf:

log-slow-queries
long_query_time = 2
log-queries-not-using-indexes

I was able to verify the slow_log.query_time field value and out of 12K entries, 1 and only one query took about 2 sec to finish which is an obvious table scan against a over 100K rows table with a field not FULLTEXT indexed (rather it's indexed outside of Mysql server scope, using SphinxSearch), but that was intentionally run as this: select * from tp where content like '%contraband%'; .

Almost all other entries were taking merely sub-one-tenth-of-a-sec to finish.

Meanwhile, mysql.general_log has ZERO entries.
This, as reported thru phpMyAdmin web interface, is quite disturbing.

Add your own comment.