Shell ScriptingAuditDatabase CentricAdvancedMay 2026

Shell Scripting — Database Centric: Database Audit Logging from Shell

Capture who changed what and when — shell-level database operation logging, MySQL binary log parsing, change data capture, audit trail formatting, and compliance-ready log management.

Audit trails answer "who changed what and when" — a compliance requirement for PCI-DSS, HIPAA, and SOC 2, and an operational necessity for debugging data corruption. Shell-level audit logging captures database events that application code misses: direct SQL changes, imports, migrations, and DBA activity.

1 Shell-level database change logging

BASH

#!/usr/bin/env bash
# db_audit.sh — Wrap database operations with audit logging

AUDIT_LOG="/var/log/myapp/db-audit.log"
DB_CONF="/etc/myapp/mysql.conf"

log_audit() {
  local action="$1" details="$2"
  printf '%s\t%s\t%s@%s\t%s\t%s\n'     "$(date --iso-8601=seconds)"     "${action}"     "${USER:-unknown}"     "${HOSTNAME}"     "${details}"     "${AUDIT_SESSION_ID:-no-session}"   >> "${AUDIT_LOG}"
}

# Wrapper that logs every database operation
db_exec() {
  local sql="$1"
  local session_id="${AUDIT_SESSION_ID:-$(uuidgen 2>/dev/null || date +%s%N)}"
  
  log_audit "DB_QUERY_START" "${sql:0:200}"
  
  local result
  if result=$(mysql --defaults-file="${DB_CONF}" -BNs myapp -e "${sql}" 2>&1); then
    log_audit "DB_QUERY_OK" "rows=$(echo "${result}" | wc -l)"
    echo "${result}"
  else
    log_audit "DB_QUERY_FAIL" "${result:0:200}"
    echo "ERROR: ${result}" >&2
    return 1
  fi
}

# ── Audit migration execution ──────────────────────────────
audit_migrate() {
  local migration_file="$1"
  export AUDIT_SESSION_ID="migrate-$(date +%s)"
  
  log_audit "MIGRATION_START" "file=$(basename "${migration_file}")"
  
  if mysql --defaults-file="${DB_CONF}" myapp < "${migration_file}"; then
    log_audit "MIGRATION_OK" "file=$(basename "${migration_file}")"
  else
    log_audit "MIGRATION_FAIL" "file=$(basename "${migration_file}")"
    return 1
  fi
}

# ── Parse MySQL general log for sensitive queries ──────────
grep -E "(UPDATE|DELETE|DROP|TRUNCATE|ALTER)" /var/log/mysql/mysql-general.log |   grep -v "information_schema" |   awk '{printf "[%s %s] %s: %s\n",$1,$2,$3,substr($0,index($0,$4))}' |   tee -a "${AUDIT_LOG}"

2 Change data capture from binary logs

BASH

# ── Parse MySQL binary log for data changes ───────────────
# Requires binary logging enabled (log_bin=ON in my.cnf)

# List recent binary log files
mysql --defaults-file="${DB_CONF}" -e "SHOW BINARY LOGS"

# Dump binary log to human-readable SQL
mysqlbinlog   --start-datetime="$(date -d '1 hour ago' '+%Y-%m-%d %H:%M:%S')"   --stop-datetime="$(date '+%Y-%m-%d %H:%M:%S')"   /var/lib/mysql/mysql-bin.* |   grep -E "^(INSERT|UPDATE|DELETE|ALTER|DROP|CREATE)" |   head -50

# ── Monitor binary log size (alert if growing too fast) ────
BINLOG_SIZE=$(du -sb /var/lib/mysql/mysql-bin.* 2>/dev/null |   awk '{s+=$1} END{print s}')
BINLOG_MB=$(( BINLOG_SIZE / 1048576 ))
(( BINLOG_MB > 5000 )) &&   echo "WARN: Binary logs using ${BINLOG_MB}MB — consider purging"

# Purge binary logs older than 7 days
mysql --defaults-file="${DB_CONF}"   -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY)"

echo "  Binary log cleanup complete"

✔ ✔ Audit loggi — Write audit logs to a separate filesystem from the application database. Include: timestamp, user, host, action, object (table/file), session ID. For compliance, store audit logs immutably (append-only, no delete). Use MySQL binary logging or PostgreSQL WAL for complete change data capture. Forward audit logs to a SIEM or centralised logging system (ELK, Splunk) for alerting.