The Ultimate Guide to High Availability with MariaDB

Writing logs into tables

You are viewing an old version of this article. View the current version here.

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

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 the precedence over the other values.

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

SET @@out_format = 'TABLE';
SET @@out_format = 'FILE,TABLE';

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

ERROR 1556 (HY000): You can't use locks with log tables.

To flush data to the tables, use FLUSH TABLES instead of FLUSH LOGS.

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

The log tables use the CSV storage engine by default. This allows to read the files using an external program, 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 MyISAM (but not other storage engines). To do so, first temporarly disable logging:

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

CHECK TABLE is supported.

CREATE TABLE is supported. ALTER TABLE, RENAME TABLE and DROP TABLE are supported when logging is disabled, but log tables cannot be partitioned.

The contents of the log tables is not logged in the binary log thus cannot be replicated.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.