Server — MariaDB — Log
MariaDB maintains several distinct logs and each one answers a different question. Treating them as interchangeable or ignoring all but one is how you end up knowing a database problem exists without having the information to diagnose it. This article covers the three logs that matter for February’s operational posture: the error log, the slow query log, and the audit log. The binary log is covered separately.
Configuration for two of these was already set in the config article and touched on in the network article. This article brings everything together in one place, explains what each log actually contains, and covers how to read them usefully rather than just confirming they exist.
The error log
What it contains
The error log is where MariaDB records problems. Startup and shutdown events, failed storage engine operations, corrupted table warnings, authentication failures at the server level, and anything the server considers worth flagging to an administrator. On a healthy server it should be nearly empty outside of startup messages.
Configuration
Set in /etc/mysql/mariadb.conf.d/50-server.cnf:
log_error = /var/log/mysql/error.log
This should already be set from the config article. On Ubuntu, MariaDB also logs to the systemd journal by default, so startup errors appear in journalctl -u mariadb as well.
Reading it
sudo tail -n 50 /var/log/mysql/error.log
Log lines are timestamped and prefixed with a severity level. The levels you will encounter:
[Note]— informational, expected startup and shutdown messages. Safe to ignore in routine checks.[Warning]— something unexpected that did not prevent operation. Worth reading and investigating if it appears repeatedly.[ERROR]— something failed. Always read these.
A clean error log on a running server looks like this: a cluster of [Note] lines from the last startup, nothing since. If there are recent [Warning] or [ERROR] lines, read them before proceeding with any other work.
To watch the log in real time during a restart or while testing a configuration change:
sudo tail -f /var/log/mysql/error.log
What to look for
InnoDB recovery messages appear after an unclean shutdown, such as a power loss. They indicate that InnoDB ran its crash recovery process on startup. A few recovery messages followed by a clean start are normal. Repeated recovery messages on consecutive startups, or recovery messages followed by further errors, indicate a deeper problem with the InnoDB data files.
Table corruption warnings look like Table './database/tablename' is marked as crashed. These require attention. Run mysqlcheck to repair:
sudo mysqlcheck -u root --all-databases --auto-repair
Authentication errors at the server level are distinct from the per-connection authentication failures captured in the audit log. If the error log shows repeated authentication subsystem failures, the mysql.user table or the privilege system may be damaged.
Log rotation
Ubuntu’s logrotate handles MariaDB log rotation automatically via /etc/logrotate.d/mysql-server. The default configuration rotates weekly and keeps seven rotations. Confirm it is in place:
cat /etc/logrotate.d/mysql-server
After rotation, MariaDB is sent a FLUSH LOGS signal to reopen the log file. This happens automatically via the postrotate script in the logrotate configuration.
The slow query log
What it contains
The slow query log records every query that takes longer than a configurable threshold to execute. Its purpose is performance diagnosis: it tells you which queries are slow, how slow they are, how many rows they examined, and whether they used indexes.
Configuration
Set in /etc/mysql/mariadb.conf.d/50-server.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 0
long_query_time = 2 logs any query taking more than two seconds. On a lightly loaded homelab server, two seconds is a generous threshold: a query that takes two seconds on February is genuinely slow. Lower it to 0.5 or even 0.1 if you want more visibility.
log_queries_not_using_indexes is intentionally set to 0 here. Enabling it on a server with active applications generates enormous log volume even for fast queries, because many legitimate queries do not need indexes. Enable it temporarily when investigating a specific performance problem, then disable it again.
Reading it
Each entry in the slow query log looks like this:
# Time: 2026-05-10T09:14:22.123456Z
# User@Host: powerdns[powerdns] @ localhost []
# Query_time: 3.145621 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 48221
SET timestamp=1746865662;
SELECT * FROM records WHERE name='february.home.arpa' AND type='A';
The fields that matter:
Query_time is the total execution time in seconds. This is the number that triggered the log entry.
Rows_examined is how many rows MariaDB had to look at to produce the result. If Rows_examined is orders of magnitude larger than Rows_sent, the query is doing a full or near-full table scan. This almost always means a missing or unused index.
Lock_time is how long the query waited for a lock. High lock times relative to query time indicate contention between concurrent queries, which is unusual on February but worth knowing how to identify.
For analysing multiple slow queries at once, mysqldumpslow summarises the log by grouping similar queries:
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log
-s t sorts by total time, -t 10 shows the top ten offenders. This gives a clear picture of which query patterns are consuming the most cumulative time.
What to do with a slow query
Once you have identified a slow query, the next step is understanding why it is slow. Run EXPLAIN on it:
sudo mariadb -e "EXPLAIN SELECT * FROM powerdns.records WHERE name='february.home.arpa' AND type='A'\G"
The output shows how MariaDB plans to execute the query. The type column tells you the access method: ALL means a full table scan and is almost always bad. ref or range means an index is being used. The rows column shows how many rows MariaDB estimates it will examine.
On a homelab server running standard applications like PowerDNS, slow queries are usually a sign that something has gone wrong with the schema or indexes rather than a query that was written poorly. Running mysqlcheck and checking the application’s table definitions is the first diagnostic step.
Log rotation
The slow query log is not included in Ubuntu’s default MariaDB logrotate configuration. Add it manually to /etc/logrotate.d/mysql-server or create a separate file:
/var/log/mysql/mariadb-slow.log {
weekly
rotate 4
compress
missingok
notifempty
create 640 mysql adm
postrotate
if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}
The audit log
What it contains
The audit log was configured in the network article and records connection and disconnection events: who connected, from where, when, and whether authentication succeeded or failed. This article covers reading it in more depth and tuning it for operational use.
Configuration recap
The relevant settings in 50-server.cnf:
plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = CONNECT
server_audit_file_path = /var/log/mysql/audit.log
server_audit_file_rotate_size = 10485760
server_audit_file_rotations = 5
Reading it
The audit log is CSV with the following fields in order: timestamp, server hostname, username, client hostname, client IP, connection ID, query ID, operation, database, query text, return code.
20260510 14:23:01,february,powerdns,localhost,,5,0,CONNECT,powerdns,,0
20260510 14:23:04,february,powerdns,localhost,,5,0,DISCONNECT,powerdns,,0
The return code at the end of each line is the most important field for routine monitoring. A return code of 0 is success. Any non-zero return code on a CONNECT event is a failed authentication attempt.
To isolate failed logins:
sudo grep CONNECT /var/log/mysql/audit.log | awk -F',' '$11 != 0'
To see connection activity for a specific user:
sudo grep CONNECT /var/log/mysql/audit.log | grep 'powerdns'
To see all connections from an unexpected host:
sudo grep CONNECT /var/log/mysql/audit.log | awk -F',' '$4 != "localhost" && $4 != ""'
On a correctly configured February this last command should return nothing. If it returns entries, something is connecting from outside the loopback interface.
Extending audit scope
The CONNECT scope records only authentication events. If you need to audit schema changes or privilege grants, for example after adding a new application or during a security review, extend the scope temporarily:
sudo mariadb -e "SET GLOBAL server_audit_events = 'CONNECT,QUERY_DDL,QUERY_DCL';"
QUERY_DDL captures CREATE, ALTER, DROP, and similar schema changes. QUERY_DCL captures GRANT, REVOKE, and CREATE USER. Neither generates high log volume on a server where schema changes are infrequent. Set it back to CONNECT when the review is complete:
sudo mariadb -e "SET GLOBAL server_audit_events = 'CONNECT';"
Note that SET GLOBAL changes take effect immediately but do not persist across restarts. To make a change permanent, update 50-server.cnf as well.
Putting it together
The three logs answer different questions, and the right time to read each one is different.
Read the error log after every restart, after any configuration change, and whenever something is behaving unexpectedly. It should be clean. If it is not, that takes precedence over everything else.
Read the slow query log when applications are slow or when adding a new service to understand its query patterns. On a lightly loaded server it may stay empty for months, which is fine. An empty slow query log is good news.
Read the audit log periodically and after any change to user accounts or application configuration. Look for failed logins and unexpected connection sources. On a stable February it should show a predictable rhythm of known users connecting and disconnecting.
None of these requires daily attention. All of them require occasional attention. The discipline of reading logs periodically, rather than only when something is already broken, is what separates a server you understand from one that occasionally surprises you.