MariaDB logs: errors, slow queries, and Galera events

Find the MariaDB error and slow query logs, configure safe log capture, and read Galera WSREP status alongside the node logs.

Best practices
Illustration of log events and data analysis

A Galera node rejoins, and checkout queries start taking longer. The node's error log can show when the transfer began. The slow query log can show which statements crossed a threshold. To see whether the node is ready now or spending time in flow control, check its wsrep status. Each signal covers a different part of the incident.

This guide uses MariaDB Server 10.11 or later for the slow-log option names, with file logging examples checked on a standalone MariaDB 11.8 server. The Galera section follows its documentation. Deployment packages and managed services can set their own defaults.

Find where MariaDB writes its error log

Start with the running server. Use SHOW VARIABLES LIKE 'log_error'; to check the file setting. On Unix, an unset log_error sends the server's error output to stderr. If systemd captures it, look in the journal for the actual service unit, for example journalctl -u mariadb. In a container, check how its runtime handles stderr.

[mariadb]
log_error = /var/log/mariadb/error.log
log_warnings = 2

This option file fragment sends errors to a file. Create the parent directory first, and make sure the MariaDB service account can write there and to any existing log file. MariaDB documents 2 as the default for log_warnings, but the effective value can differ. The setting only affects some warnings. If you increase it for a connection issue, record the old setting and restore it when you have the evidence. After moving a log file during rotation, have MariaDB reopen it before assuming new entries will appear at the original path. Error log · Log rotation

Start with a slow query log you can interpret

Add this to a server option file for a controlled test. Use a writable directory and check existing destinations before setting log_output = FILE, because that variable also controls the general query log.

[mariadb]
log_output = FILE
log_slow_query = 1
log_slow_query_file = /var/log/mariadb/slow.log
log_slow_query_time = 1
log_queries_not_using_indexes = OFF
log_slow_rate_limit = 1

The example logs statements over the one-second threshold that meet the server's other slow-log conditions. It turns off the no-index override and leaves rate limiting off. On MariaDB 10.11 and later, log_slow_query, log_slow_query_file, and log_slow_query_time are the current names. Older configurations use slow_query_log, slow_query_log_file, and long_query_time; those names are still aliases in the newer versions.

If the log is too noisy, log_slow_filter can restrict it by features such as a full scan, filesort, or an internal temporary table. Be careful with not_using_index. It can make a fast query eligible even when its duration is below the threshold. log_slow_min_examined_row_limit can exclude small scans. If you raise log_slow_rate_limit, treat the log as a sample. Starting with MariaDB 11.7, very slow statements over log_slow_always_query_time can bypass the rate limit. A line count from the file is not the total number of slow queries.

File output can include more detail through log_slow_verbosity. query_plan records plan-related details and explain can include EXPLAIN output for supported statements. Table output does not support these verbosity options. Use mariadb-dumpslow to group similar statements in a file, or query mysql.slow_log when table output is configured. Test any other analyzer on a sample that includes the fields your MariaDB version emits. Slow query log · Server variables · mariadb-dumpslow

Use the general query log for a short capture

The general log records incoming statements and connections. A statement appears when MariaDB receives it. The entry does not say that the statement succeeded or its transaction committed.

SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('general_log', 'general_log_file', 'log_output');

If a short diagnostic capture needs the general log, record its original state and output destination. In a test instance where it starts OFF and the destination is already FILE, a harmless probe can confirm the path.

SET GLOBAL general_log = ON;
SELECT 1 AS learn_general_log_probe;
SET GLOBAL general_log = OFF;

Restore the original setting at the end. Changing log_output to TABLE would also send enabled slow-query logs to their table. Restrict access to both logs. They can contain SQL literals and generate substantial write volume. General query log · Table output

Keep binary log retention tied to recovery

For a file-based binlog on MariaDB 10.6.1 or later, the following illustrates a seven-day expiration setting.

[mariadb]
log_bin = /var/log/mariadb/mariadb-bin
binlog_expire_logs_seconds = 604800
max_binlog_size = 1G

Choose the window from your base backup schedule, point-in-time recovery requirement, and the longest time a replica may be behind or disconnected. Expiration is checked at events such as startup or rotation; it is not a wall-clock deletion guarantee. MariaDB manages these files and their index. Do not rotate them with the system log-rotation tool or delete them by hand. Maintain the binary log

Reconstruct the Galera incident across nodes

Line up the donor and joiner logs by event time. Look for WSREP membership changes, snapshot transfers (SST), and incremental transfers (IST). An SST uses a state snapshot; IST can catch up with missing writesets when the donor still has the relevant history. If an SST fails, also look at the logs of the configured transfer method.

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'wsrep_cluster_status',
  'wsrep_cluster_size',
  'wsrep_ready',
  'wsrep_connected',
  'wsrep_local_state_comment',
  'wsrep_local_recv_queue_avg',
  'wsrep_flow_control_paused'
);

In a Galera deployment, check wsrep_cluster_status for the Primary component, wsrep_ready for query readiness, and wsrep_local_state_comment for the node's local state. wsrep_flow_control_paused is a time fraction since the last FLUSH STATUS; compare it with the receive queue and status from the other nodes. A quiet error log does not show whether flow control is active.

If the error and slow logs are files, configure DataKit file collection with paths the collector can read and separate sources for each format. Keep node and cluster identifiers, test multiline SQL on an actual sample, and verify the timestamps and time zones. Synchronize the node clocks separately. A Pipeline can normalize timestamp fields but cannot correct a drifting clock. With those fields in place, a Guance log detection rule can count a specific repeated WSREP failure per node or cluster. If errors and slow logs need different retention, check the log index routing and storage policy. Galera status variables · Guance log detection · Log indexes

Related reading

Get a tailored plan

Contact us

Join the community

Scan with WeChat
to join the community

Try Guance

Start online and pay only for what you use.

Get started

Choose a Guance plan

Code hosting