Writing Logs Into Tables

By default, all logs are disabled or written into files. The general query log and the slow query log can also be written to special tables in the mysql database. During the startup, entries will always be written into files.

Note that EXPLAIN output will only be recorded if the slow query log is written to a file and not to a table.

To write logs into tables, the log_output server system variable is used. Allowed values are FILE, TABLE and NONE. It is possible to specify multiple values, separated with commas, to write the logs into both tables and files. NONE disables logging and has precedence over the other values.

So, to write logs into tables, one of the following settings can be used:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_output = 'FILE,TABLE';

The general log will be written into the general_log table, and the slow query log will be written into the table. Only a limited set of operations are supported for those special tables. For example, direct DML statements (like INSERT) on those tables will fail with an error similar to the following:

To flush data to the tables, use instead of .

To empty the contents of the log tables, can be used.

The log tables use the storage engine by default. This allows an external program to read the files if needed: normal CSV files are stored in the mysql subdirectory, in the data dir. However that engine is slow because it does not support indexes, so you can convert the tables to (but not other storage engines). To do so, first temporarily disable logging:

and are supported.

is supported. , and are supported when logging is disabled, but log tables cannot be partitioned.

Contents of log tables are not logged in the , thus cannot be replicated.

_{This page is licensed: CC BY-SA / Gnu FDL}