Overview of MariaDB Logs

An introductory guide to the various logs available in MariaDB, including the Error Log, General Query Log, Slow Query Log, and Binary Log, and how to enable or disable them.

There are many variables in MariaDB that you can use to define what to log and when to log.

This article will give you an overview of the different logs and how to enable/disable logging to these.

Note that storage engines can have their logs too: for example, InnoDB keeps an Undo Log and a Redo Log which are used for rollback and crash recovery. However, this page only lists MariaDB server logs.

Error Log

• Always enabled

• Usually a file in the data directory, but some distributions may move this to other locations.

• All critical errors are logged here.

• One can get warnings to be logged by setting log_warnings.

• With the mysqld_safe --syslog option one can duplicate the messages to the system's syslog.

General Query Log

• Enabled with --general-log

• Logs all queries to a file or table.

• Useful for debugging or auditing queries.

• The super user can disable logging to it for a connection by setting SQL_LOG_OFF to 1.

Slow Query Log

• Enabled by starting mysqld with --slow-query-log

• Logs all queries to a file or table.

• Useful to find queries that causes performance problems.

• Logs all queries that takes more than long_query_time to run.

• One can decide what to log with the options --log-slow-admin-statements, --log-slow-slave-statements, log_slow_filter or log_slow_rate_limit.

• One can change what is logged by setting log_slow_verbosity.

• One can disable it globally by setting global.slow_query_log to 0

• In 10.1 one can disable it for a connection by setting local.slow_query_log to 0.

Binary Log

• Enabled by starting mysqld with --log-bin

• Used on machines that are, or may become, replication masters.

• Required for point-in-time recovery.

• Binary log files are mainly used by replication and can also be used with mariadb-binlog to apply on a backup to get the database up to date.

• One can decide what to log with --binlog-ignore-db=database_name or --binlog-do-db=database_name.

• The super user can disable logging for a connection by setting SQL_LOG_BIN to 0. However while this is 0, no changes done in this connection will be replicated to the slaves!

• For examples, see Using and Maintaining the Binary Log.

Examples

If you know that your next query will be slow and you don't want to log it in the slow query log, do:

SET LOCAL SLOW_QUERY_LOG=0;

If you are a super user running a log batch job that you don't want to have logged (for example mariadb-dump), do:

SET LOCAL SQL_LOG_OFF=1, LOCAL SLOW_QUERY_LOG=0;

mariadb-dump (previously mysqldump) since MariaDB 10.1 will add this automatically to your dump file if you run it with the --skip-log-queries option.

Parsing Reference for Tool Developers

To support the development of log parsers (e.g., Fluentd, Logstash, Splunk), the following tables standardize identification fields and structural characteristics across all MariaDB Server logs.

Cross-Log Field Standardization

The following data points are represented differently across various logs but refer to the same internal identifiers.

Standardized Name	General/Slow Log Field	Audit Log Field	Error Log Field	Binary Log (Text)
Thread ID	thread_id	connectionid	Thread ID	at {ID}
Server ID	server_id	N/A (Uses Hostname)	N/A (Uses Process ID)	server id

Structural Characteristics for Parsers

Log Type	Primary Format	Record Start Indicator	Multi-line Support
Error Log	Positional String	Timestamp (YYYY-MM-DD)	No (Except Stack Traces)
General Query Log	Positional String	Timestamp	Yes (SQL Payloads)
Slow Query Log	Tagged Header	# User@Host:	Yes (Always)
Binary Log (Text)	Tagged Header	# at {Position}	Yes (Event Chunks)
Enterprise Audit	CSV	N/A (Standard Line)	No

Parser Developer Checklist

• Handle Multi-line Records: Assume that General and Slow Query logs will contain newlines within SQL statements.

• Standardize Identifiers: Always map connectionid and thread_id to a single thread_id field in your monitoring backend to allow cross-log correlation.

• Identify Record Starts: For Slow Query logs, use # User@Host: as the only reliable indicator of a new record.

• Monitor Metadata Impacts: Note that settings like binlog_row_image can change the number of columns present in Binary Log events.

See Also

• MariaDB audit plugin

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