Database Log File Rotation and Cleanup Tutorial: Complete MySQL, PostgreSQL, and MariaDB Maintenance for Linux VPS in 2026

Database log files consume significant disk space over time. A typical MySQL production server generates 2-5GB of log data monthly. Without proper rotation, these files eventually fill your VPS storage and crash your applications.

This tutorial walks through complete database log file rotation setup for MySQL, PostgreSQL, and MariaDB on Linux VPS environments. You'll configure automated cleanup, set retention policies, and monitor disk usage effectively.

Understanding Database Log File Types

Different database engines create distinct log file types. Each type requires specific rotation strategies.

MySQL generates binary logs (/var/lib/mysql/mysql-bin.*), error logs (/var/log/mysql/error.log), slow query logs, and general query logs. Binary logs alone can reach gigabytes within days on busy systems.

PostgreSQL creates WAL files (/var/lib/postgresql/*/main/pg_wal/), system logs, and CSV logs when logging is enabled. WAL files typically consume 16MB each. They multiply rapidly during high-write operations.

MariaDB follows MySQL's log structure but adds audit logs and optimizer trace logs in recent versions. The audit plugin can generate substantial log volumes when enabled for compliance monitoring.

MySQL Log Rotation Configuration

MySQL requires both server-level and system-level rotation configuration for complete log management.

First, configure MySQL binary log retention in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M
binlog_cache_size = 32K

The binlog_expire_logs_seconds setting automatically removes binary logs older than 7 days. Restart MySQL to apply changes:

sudo systemctl restart mysql

Create a logrotate configuration for MySQL error logs at /etc/logrotate.d/mysql-server:

/var/log/mysql/*.log {
  daily
  rotate 30
  compress
  delaycompress
  notifempty
  missingok
  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
}

This configuration rotates logs daily and keeps 30 days of compressed logs. It also flushes MySQL logs after rotation.

MySQL Slow Query Log Rotation

Slow query logs require special handling. You need to preserve performance data while managing disk space:

/var/log/mysql/slow-query.log {
  weekly
  rotate 8
  compress
  delaycompress
  notifempty
  missingok
  create 640 mysql mysql
  postrotate
    /usr/bin/mysql -e "SELECT @@global.slow_query_log_file; SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL slow_query_log = 'ON';"
  endscript
}

PostgreSQL Log Rotation Setup

PostgreSQL log rotation involves both WAL file management and system log rotation configuration.

Configure WAL retention in /etc/postgresql/*/main/postgresql.conf:

wal_keep_size = 1GB
max_wal_size = 2GB
min_wal_size = 80MB
wal_level = replica

Enable log rotation within PostgreSQL:

log_rotation_age = 1d
log_rotation_size = 100MB
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on

Restart PostgreSQL to apply configuration changes:

sudo systemctl restart postgresql

Create system-level logrotate configuration at /etc/logrotate.d/postgresql-common:

/var/log/postgresql/*.log {
  daily
  rotate 14
  compress
  delaycompress
  notifempty
  missingok
  create 640 postgres postgres
  postrotate
    /usr/lib/postgresql/*/bin/pg_ctl reload -D /var/lib/postgresql/*/main -s
  endscript
}

PostgreSQL WAL Archive Cleanup

Implement automated WAL archive cleanup to prevent storage overflow:

#!/bin/bash
# /usr/local/bin/pg_wal_cleanup.sh

WAL_ARCHIVE_DIR="/var/lib/postgresql/wal_archive"
RETENTION_DAYS=7

find "$WAL_ARCHIVE_DIR" -name "*.wal" -type f -mtime +$RETENTION_DAYS -delete
find "$WAL_ARCHIVE_DIR" -name "*.backup" -type f -mtime +$RETENTION_DAYS -delete

echo "$(date): WAL archive cleanup completed" >> /var/log/postgresql/wal_cleanup.log

Make the script executable and add to crontab:

sudo chmod +x /usr/local/bin/pg_wal_cleanup.sh
echo "0 2 * * * /usr/local/bin/pg_wal_cleanup.sh" | sudo crontab -

MariaDB Log Management

MariaDB follows MySQL patterns with additional considerations for audit logs and performance schema.

Configure MariaDB log retention in /etc/mysql/mariadb.conf.d/50-server.cnf:

[mariadb]
expire_logs_days = 7
max_binlog_size = 100M
log_error = /var/log/mysql/mariadb-error.log
slow_query_log_file = /var/log/mysql/mariadb-slow.log

Create logrotate configuration at /etc/logrotate.d/mariadb:

/var/log/mysql/*.log {
  daily
  rotate 30
  compress
  delaycompress
  notifempty
  missingok
  create 640 mysql mysql
  postrotate
    if [ -x /usr/bin/mariadb-admin ]; then
      /usr/bin/mariadb-admin flush-logs
    fi
  endscript
}

MariaDB Audit Log Rotation

When using MariaDB audit plugin, configure separate rotation for audit logs:

/var/lib/mysql/server_audit.log {
  weekly
  rotate 52
  compress
  delaycompress
  notifempty
  missingok
  copytruncate
  create 660 mysql mysql
}

Automated Log Monitoring and Alerting

Implement proactive monitoring to prevent log-related disk space issues. This catches problems before they impact operations.

Create a monitoring script at /usr/local/bin/db_log_monitor.sh:

#!/bin/bash

# Set thresholds
DISK_THRESHOLD=85
LOG_SIZE_THRESHOLD=1048576  # 1GB in KB

# Check disk usage
DISK_USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | sed 's/%//')

if [ "$DISK_USAGE" -gt "$DISK_THRESHOLD" ]; then
    echo "ALERT: Database partition usage at ${DISK_USAGE}%" | mail -s "Database Disk Alert" admin@example.com
fi

# Check individual log file sizes
find /var/log/mysql /var/log/postgresql -name "*.log" -size +${LOG_SIZE_THRESHOLD}k -exec ls -lh {} \; > /tmp/large_logs.txt

if [ -s /tmp/large_logs.txt ]; then
    echo "Large database log files detected:" | cat - /tmp/large_logs.txt | mail -s "Large Database Logs" admin@example.com
fi

Schedule the monitoring script to run every 6 hours:

0 */6 * * * /usr/local/bin/db_log_monitor.sh

For VPS environments with multiple databases, HostMyCode VPS provides dedicated resources. This ensures consistent log rotation performance without resource contention.

Log Compression and Archival Strategies

Implement efficient compression and archival to balance storage usage with data accessibility requirements.

Configure advanced compression in logrotate with multiple compression algorithms:

/var/log/mysql/archive/*.log {
  monthly
  rotate 12
  compress
  compresscmd /usr/bin/xz
  compressext .xz
  compressoptions -9
  delaycompress
  notifempty
  missingok
}

Create a log archival script for long-term storage:

#!/bin/bash
# /usr/local/bin/archive_old_logs.sh

ARCHIVE_DIR="/backup/log_archive/$(date +%Y-%m)"
SOURCE_DIRS="/var/log/mysql /var/log/postgresql"

mkdir -p "$ARCHIVE_DIR"

for dir in $SOURCE_DIRS; do
    find "$dir" -name "*.log.*.gz" -mtime +30 -exec mv {} "$ARCHIVE_DIR/" \;
done

# Create monthly archive tarball
tar -czf "/backup/log_archive/database_logs_$(date +%Y-%m).tar.gz" "$ARCHIVE_DIR"
rm -rf "$ARCHIVE_DIR"

Database Log Analysis and Retention

Balance storage efficiency with analytical needs by implementing intelligent retention policies.

Different log types require different retention strategies. Error logs need immediate access for troubleshooting. Slow query logs provide historical performance insights. Binary logs enable point-in-time recovery.

Configure retention based on log criticality:

# Critical logs - 30 days local + archive
/var/log/mysql/error.log
/var/log/postgresql/postgresql.log

# Performance logs - 14 days local
/var/log/mysql/slow-query.log
/var/log/postgresql/pg_log/

# Binary/WAL logs - 7 days local (handled by database)
/var/lib/mysql/mysql-bin.*
/var/lib/postgresql/*/main/pg_wal/

For database-intensive applications requiring solid log management, HostMyCode database hosting offers optimized storage configurations. These include automated backup integration.

Troubleshooting Common Log Rotation Issues

Address frequent problems that prevent effective log rotation in production environments.

Permission issues often prevent rotation. Ensure logrotate runs as appropriate user:

sudo -u mysql logrotate -d /etc/logrotate.d/mysql-server

Database locks can block log flushing. Test rotation during maintenance windows:

sudo logrotate -f /etc/logrotate.d/mysql-server

Storage constraints may prevent compression. Monitor available space before rotation:

df -h /var/log/mysql
df -h /var/lib/mysql

Verify rotation functionality with test runs:

sudo logrotate -d /etc/logrotate.d/postgresql-common

Performance Impact and Optimization

Log rotation operations can impact database performance during execution. Schedule rotations during low-traffic periods.

Monitor rotation performance impact:

time sudo logrotate -f /etc/logrotate.d/mysql-server

For high-traffic databases, consider staggered rotation schedules. This distributes I/O load.

PostgreSQL handles internal rotation more gracefully than external logrotate operations.

Use ionice to reduce rotation priority:

ionice -c 3 logrotate /etc/logrotate.d/mysql-server

Our database performance monitoring guide covers techniques for tracking rotation impact on query performance.

Frequently Asked Questions

How much disk space do database logs typically consume?

MySQL binary logs average 100-500MB daily on moderate-traffic sites. PostgreSQL WAL files consume 16MB per segment with 3-10 segments typical for small databases. High-traffic applications can generate 1-5GB of logs daily across all log types.

Can I safely delete old database log files manually?

Never delete active log files or binary logs needed for replication. Use database-specific commands like MySQL's PURGE BINARY LOGS or PostgreSQL's pg_archivecleanup for safe removal. Manual deletion can break replication and recovery capabilities.

What happens if log rotation fails during peak traffic?

Failed rotation can fill disk space and crash databases. Implement monitoring alerts for disk usage above 80%. Configure logrotate with notifempty and missingok options to handle edge cases gracefully. Consider emergency cleanup scripts for crisis situations.

Should I compress database logs immediately or delay compression?

Use delaycompress in logrotate to avoid compressing the most recent rotated log. This allows immediate access for troubleshooting while still saving space on older logs. Compression typically reduces log sizes by 70-90%.

How do I rotate logs for containerized databases?

Mount log directories as volumes to enable host-based rotation. Configure container logging drivers appropriately. For Kubernetes, use tools like Fluentd or configure pod-level log rotation policies. Avoid in-container rotation which can cause resource conflicts.