MySQL, MariaDB and PostgreSQL Logs: Linux Database Forensics

What it is

Databases on Linux web and application servers are both targets (data theft, ransom notes left in tables) and tools (SQL injection that writes a web shell with SELECT ... INTO OUTFILE, PostgreSQL COPY ... FROM PROGRAM that runs shell commands as postgres, MySQL user-defined functions loaded from a shared library). The server logs, binary logs and client history files are how you reconstruct that.

What exists depends heavily on configuration. By default, neither MySQL nor PostgreSQL logs every statement, so check the configuration first to know what you can expect to find.

Where it lives

What it proves

• Connection attempts and authentication failures (Access denied for user 'root'@'203.0.113.50' in MySQL/MariaDB; password authentication failed for user in PostgreSQL), when the verbosity allows it.
• Errors produced by SQL injection probes, which PostgreSQL logs with the failing statement by default (log_min_error_statement = error).
• Data changes with exact statements or row images, from binary logs, even when nothing else logged them.
• Operating system actions through the database: files written by INTO OUTFILE/DUMPFILE, programs started by COPY FROM PROGRAM, libraries loaded with CREATE FUNCTION ... SONAME.
• It does not prove which rows were read (SELECT) unless the general log, log_statement = 'all' or an audit plugin was enabled.

Key fields

# MySQL 8 error log (log_error_verbosity 3)
2026-09-20T03:14:07.512031Z 812 [Note] [MY-010926] [Server] Access denied for user 'root'@'203.0.113.50' (using password: YES)

# PostgreSQL (log_line_prefix '%m [%p] %q%u@%d ')
2026-09-20 05:14:09.118 CEST [4302] app@shop ERROR:  syntax error at or near "UNION" at character 57
2026-09-20 05:14:09.118 CEST [4302] app@shop STATEMENT:  SELECT * FROM items WHERE id=1' UNION SELECT ...

Timestamps

MySQL 8 writes error log times in UTC with a Z suffix by default (log_timestamps = UTC). MariaDB, older MySQL and PostgreSQL use the server's local time unless configured otherwise; PostgreSQL prints the zone abbreviation when %m or %t is used. Binary log headers show local time, but SET TIMESTAMP=<epoch> in the decoded output is authoritative.

Retention

MySQL 8 purges binary logs after binlog_expire_logs_seconds (2,592,000 seconds, 30 days, by default). Error logs rotate through distribution logrotate snippets. On RHEL, PostgreSQL's default log_filename = 'postgresql-%a.log' with log_truncate_on_rotation = on overwrites each weekday's file every week, so only seven days survive. Debian's PostgreSQL logs rotate weekly through logrotate.

Collection

tar -C /mnt/evidence -cpf /cases/2026-017/db.tar etc/mysql etc/my.cnf etc/my.cnf.d etc/postgresql \
  var/log/mysql* var/log/mariadb var/log/postgresql var/lib/pgsql/data/log var/lib/pgsql/*/data/log \
  var/lib/mysql/*bin* var/lib/mysql/*.log usr/lib/mysql/plugin usr/lib64/mysql/plugin usr/lib64/mariadb/plugin \
  root/.mysql_history root/.psql_history home/*/.mysql_history home/*/.psql_history \
  var/lib/mysql/.mysql_history var/lib/postgresql/.psql_history var/lib/pgsql/.psql_history 2>/dev/null

UAC's var_log artifact covers logs under /var/log; data-directory logs and binlogs need a custom collector. Collect from a copy or snapshot; do not start the database on the evidence.

Parsing

# Binary logs, row events decoded, one time window
mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime='2026-09-20 03:00:00' \
  --stop-datetime='2026-09-20 05:00:00' binlog.000042 > binlog_decoded.sql

# MySQL/MariaDB: failures, dangerous functions and file writes
zgrep -hE 'Access denied|INTO (OUT|DUMP)FILE|LOAD_FILE|CREATE FUNCTION|SONAME' mysql/*.log*

# PostgreSQL: auth failures and command execution
grep -hE 'authentication failed|FROM PROGRAM|lo_export|CREATE EXTENSION|ALTER SYSTEM' postgresql*.log
pgbadger -f stderr postgresql-*.log -o pg_report.html

Investigator tips

• New files in the plugin directory (lib_mysqludf_sys.so and look-alikes) and CREATE FUNCTION sys_exec in binlogs mean OS command execution as the mysql user. Correlate with SELinux or AppArmor denials for mysqld.
• COPY ... FROM PROGRAM runs commands as postgres; look at that account's home directory, shell history and cron entries, and for processes whose parent is a postgres backend.
• Files written with INTO OUTFILE are owned by mysql and often land in the web root; pair their birth time with web server logs.
• ~/.mysql_history escapes spaces as \040 on builds linked with libedit, and the MySQL client skips statements matching its default ignore patterns (*IDENTIFIED*:*PASSWORD*), so passwords are usually absent.
• Enabling the general log or log_statement = 'all' after an incident starts collecting; note the time you changed it so your own actions are not mistaken for the attacker's.