Logs and more logs, who has time to read them ?

While working on some new features in MariaDB 10.1, I noticed that a normal user couldn't disable the slow query log, which I thought was a bit silly.

While implementing and documenting this feature, I noticed that the information about the different logs is quite spread around and it's not that trivial to find out how to enable/disable the different logs.

To solve this, I created a new MariaDB kb entry, "Overview of the MariaDB logs that I hope MariaDB and MySQL users will be find useful.

Here follows a copy of the kb entry. If you have any comments or things that could be added, please do it in the kb entry so that it will benefit as many as possible!

Overview of MariaDB logs

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.

The 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-statments, --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.

The binary log

• Enabled by starting mysqld with --log-bin
• Normally only used on machines that are, or may become, replication masters.
• Binary log files are mainly used by replication and can also be used 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 mysqldump), do:

SET LOCAL SQL_LOG_OFF=1, LOCAL SLOW_QUERY_LOG=0;

mysqldump in MariaDB 10.1 will do this automatically if you run it with the --disable-log-querys option.

See also

• MariaDB audit plugin