Log Rotation Strategies

In this lesson, you will learn how to prevent your database servers from running out of disk space by implementing automated log rotation and compression strategies.

The Problem: Infinite Log Growth

In MySQL and MariaDB, database logs—such as the error log, the slow query log, and the general query log—grow indefinitely as they record activity. If left unchecked, these files can consume every available byte on your partition, leading to an immediate database crash. Unlike application data, logs are often ephemeral; while we need them for troubleshooting, we rarely need every entry from three years ago sitting on our production disk.

The Strategy: Rotate and Compress

Log rotation is the process of moving the current log file to an archived state, creating a fresh file for new entries, and purging or compressing the oldest files to reclaim storage.

The standard tool for this in Linux ecosystems is logrotate. Instead of relying on the database engine to manage its own file sizes, we delegate this to the operating system.

Implementing Log Rotation

To rotate logs effectively without stopping the database, you must ensure the database engine "re-opens" the log files after they are moved. You can achieve this by configuring a logrotate script with a postrotate command:

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    sharedscripts
    postrotate
        mysql -e 'FLUSH SLOW LOGS;'
    endscript
}

In this example:

• daily: Logs are rotated once per day.
• rotate 7: Keeps seven days of logs before deleting the oldest one.
• compress: Uses gzip to shrink logs, often saving 90% or more of disk space.
• postrotate: This is critical. It sends a command to MySQL to flush the logs, which forces the server to close the current file descriptor and start writing to the newly created file. Without this signal, the database might continue writing to the "deleted" or moved log file, preventing space from being freed.

By combining compression with a strict retention policy, you ensure your database instances remain stable, predictable, and within their allocated disk capacity.

Key Takeaway: Automate log lifecycle management using logrotate combined with database flush commands to ensure logs are compressed and reclaimed without service interruption.