Configuring Efficient MySQL Log Rotation with logrotate

At 11 p.m. on a Tuesday, a production MySQL server running a SaaS application ground to a halt — not from a bad query, not from a replication lag spike, but because the root filesystem hit 100% capacity. The culprit: /var/log/mysql/mysql-slow.log, which had been growing unchecked for seven months and had ballooned to 94 GB. The engineers had configured slow query logging months earlier during a performance investigation, enabled it, and never set up log rotation. Disk exhaustion from unmanaged MySQL log files is one of the most preventable production outages, and it remains surprisingly common because MySQL generates several distinct log files — each requiring a slightly different rotation strategy.

MySQL Log Types That Need Rotation

Before writing a single logrotate config, you need to know which files MySQL is actually writing and where they live on your system. MySQL generates four distinct categories of log files, each with different growth rates, operational purposes, and rotation requirements.

Error Log

The error log records server start/stop events, critical InnoDB errors, replication errors, and any warnings at your configured verbosity level. On a quiet production server it grows slowly — typically a few megabytes per month — but on a server with replication issues or memory pressure it can grow much faster. The path is set by log_error in my.cnf, and it defaults to /var/log/mysql/error.log on Debian/Ubuntu systems or /var/lib/mysql/hostname.err on RPM-based systems.

SHOW VARIABLES LIKE 'log_error';
-- +---------------+---------------------------+
-- | Variable_name | Value                     |
-- +---------------+---------------------------+
-- | log_error     | /var/log/mysql/error.log  |
-- +---------------+---------------------------+

Slow Query Log

The slow query log is the highest-growth log on active servers. Every query exceeding long_query_time (default 10 seconds, but set to 1–2 seconds on most tuned servers) generates an entry. On a server handling thousands of queries per second with a 1-second threshold, this log can grow at hundreds of megabytes per day during peak load or performance regressions. Always plan for aggressive rotation here.

SHOW VARIABLES LIKE 'slow_query_log%';
-- +---------------------+------------------------------------+
-- | Variable_name       | Value                              |
-- +---------------------+------------------------------------+
-- | slow_query_log      | ON                                 |
-- | slow_query_log_file | /var/log/mysql/mysql-slow.log      |
-- +---------------------+------------------------------------+

General Query Log

The general query log records every SQL statement sent to the server. It is extremely high volume — on any production server with real traffic it grows at gigabytes per hour. It should almost never be enabled in production, and when it is enabled (for auditing or debugging), it must be paired with aggressive rotation. Check whether it is on before assuming it is off:

SHOW VARIABLES LIKE 'general_log%';
-- +------------------+------------------------------------+
-- | Variable_name    | Value                              |
-- +------------------+------------------------------------+
-- | general_log      | OFF                                |
-- | general_log_file | /var/log/mysql/mysql.log           |
-- +------------------+------------------------------------+

Binary Logs

Binary logs (binlogs) are fundamentally different from the other log types. They are written to a directory as a numbered sequence of files (mysql-bin.000001, mysql-bin.000002, etc.) controlled entirely by MySQL's own internal rotation mechanism. logrotate cannot safely manage binary logs because doing so would break MySQL's internal log index and corrupt point-in-time recovery chains. Binary log management is covered in its own section below.

How logrotate Works with MySQL

logrotate is a Linux utility that reads configuration files from /etc/logrotate.d/ and runs as a daily cron job (or systemd timer). When it rotates a log file, it renames the current file (e.g., error.log → error.log.1) and creates a new empty file in its place. The critical issue for MySQL is that the server still holds an open file descriptor to the old renamed file and will keep writing to it — until you tell MySQL to re-open its log files.

This is why every MySQL logrotate configuration must include a postrotate script. The two options are:

• Send SIGHUP to mysqld — on some configurations this causes MySQL to reopen log files, but it is less reliable and behavior differs across versions.
• Execute mysqladmin flush-logs — explicit, reliable, and version-agnostic. This triggers MySQL to close and reopen all file-based logs, starting fresh on the new empty file.

Configuring logrotate for MySQL Error Log and Slow Query Log

The Default Debian/Ubuntu Configuration

On Debian and Ubuntu, installing the mysql-server package creates a logrotate config at /etc/logrotate.d/mysql-server. It is a reasonable starting point but often needs tuning for production environments:

# /etc/logrotate.d/mysql-server (Debian/Ubuntu default — review before trusting)
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/error.log {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
        test -x /usr/bin/mysqladmin || exit 0
        if [ -f `cat /var/run/mysqld/mysqld.pid` ]; then
            mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
        fi
    endscript
}

A Production-Ready MySQL logrotate Config

The default config has several weaknesses: it does not rotate the slow query log, uses a fragile PID file check, and does not compress older archives. Here is a production-ready replacement that handles both the error log and slow query log with appropriate retention:

# /etc/logrotate.d/mysql
# Production logrotate config for MySQL error log + slow query log

/var/log/mysql/error.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        # Flush logs only if MySQL is running
        if mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf ping &>/dev/null 2>&1; then
            mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf flush-error-log
        fi
    endscript
}

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    sharedscripts
    postrotate
        if mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf ping &>/dev/null 2>&1; then
            mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf flush-slow-log
        fi
    endscript
}

Key Directives Explained

delaycompress — Compresses the rotated log on the next rotation cycle rather than immediately. This keeps the most recently rotated file readable without decompression, which is useful when you need to inspect yesterday's log after an overnight incident. Always pair it with compress.

missingok — Silently skips the rotation if the log file does not exist. This prevents logrotate from emitting errors when MySQL has the general log or slow log disabled.

notifempty — Skips rotation for empty log files. Avoids creating empty compressed archives when MySQL had no activity to log.

create 640 mysql adm — After rotating, creates a new log file with permissions 640, owned by user mysql and group adm. MySQL must be able to write to it; the adm group allows tools like logwatch to read it without running as root.

# /etc/mysql/logrotate.cnf — credentials for logrotate postrotate scripts
[client]
user     = logrotate_user
password = your_password_here
host     = 127.0.0.1

Rotating the Slow Query Log More Aggressively

On high-traffic servers, daily rotation may not be sufficient for the slow query log. Use size-based rotation to cap file size regardless of age:

/var/log/mysql/mysql-slow.log {
    size 500M       # Rotate when file exceeds 500 MB
    rotate 5        # Keep 5 compressed archives (2.5 GB max)
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        if mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf ping &>/dev/null 2>&1; then
            mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf flush-slow-log
        fi
    endscript
}

Handling Binary Logs Separately

Binary logs must not be managed by logrotate. Deleting or renaming binlog files outside of MySQL's control corrupts the binlog index file (mysql-bin.index), which MySQL relies on to track which binlogs exist. This breaks point-in-time recovery, replica replication streams, and tools like Percona XtraBackup that read the index to determine which binlogs to include in a backup.

expire_logs_days and binlog_expire_logs_seconds

MySQL manages its own binary log purging through two variables. On MySQL 8.0, binlog_expire_logs_seconds is preferred over the deprecated expire_logs_days:

# MySQL 8.0+ — set in /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
binlog_expire_logs_seconds = 604800   # 7 days (7 * 86400)

# MySQL 5.7 — use expire_logs_days instead
[mysqld]
expire_logs_days = 7

With this setting, MySQL automatically purges binlog files older than 7 days whenever a new binlog is created (on server restart, when max_binlog_size is reached, or when FLUSH BINARY LOGS is executed).

Manual Purging with PURGE BINARY LOGS

For immediate disk reclamation without waiting for the expiry timer, use PURGE BINARY LOGS. Always check your replicas first — never purge a binlog that a replica has not yet consumed:

# Check what replicas have consumed — run on the primary
SHOW SLAVE HOSTS;
-- Or on MySQL 8.0:
SHOW REPLICAS;

-- Check the replica's current binlog position
SHOW SLAVE STATUS\G
-- Look for: Relay_Master_Log_File and Exec_Master_Log_Pos

-- Safe purge: remove all binlogs before a specific file
PURGE BINARY LOGS TO 'mysql-bin.001523';

-- Purge all binlogs older than a specific date
PURGE BINARY LOGS BEFORE '2026-02-16 00:00:00';

-- List current binlogs and their sizes before purging
SHOW BINARY LOGS;

Testing and Verifying Your logrotate Config

Dry Run with -d

Before relying on your logrotate config in production, test it with the -d flag, which prints what logrotate would do without making any changes:

# Dry run — shows actions without executing them
logrotate -d /etc/logrotate.d/mysql

# Sample output:
# reading config file /etc/logrotate.d/mysql
# Allocating hash table for state file, size 15360 B
# Handling 2 logs
# rotating pattern: /var/log/mysql/error.log  after 1 days (14 rotations)
# empty log files are not rotated, old logs are removed
# considering log /var/log/mysql/error.log
#   log does not need rotating (log has been already rotated)

Force Rotation with -f

Force an immediate rotation to verify the full cycle works end to end, including the postrotate flush:

# Force rotation immediately (ignores the rotation schedule)
logrotate -f /etc/logrotate.d/mysql

# Verify MySQL is writing to the new (current) log file
ls -lah /var/log/mysql/
# error.log        (new, small)
# error.log.1      (yesterday's, not yet compressed)
# error.log.2.gz   (older, compressed)

# Confirm MySQL's active slow log file path
mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

# Check that MySQL is writing new entries to the current file
mysql -e "SELECT 1 + 1;"
tail -5 /var/log/mysql/mysql-slow.log

Checking the logrotate State File

logrotate tracks when it last rotated each file in its state file. If rotation is not happening on schedule, inspect the state to see the last rotation timestamp:

cat /var/lib/logrotate/status | grep mysql
# "/var/log/mysql/error.log" 2026-2-22
# "/var/log/mysql/mysql-slow.log" 2026-2-22

Common Pitfalls and How to Avoid Them

MySQL Still Writing to the Renamed File

Symptom: after rotation, error.log is 0 bytes but error.log.1 keeps growing. This means the postrotate script failed silently. Check whether mysqladmin ping succeeds with the credentials file you are using, and whether the flush command is reaching MySQL:

# Test the exact command your postrotate script will run
mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf flush-error-log
# If this fails, postrotate is failing too

# Check MySQL error log for any flush-related messages
grep -i "flush" /var/log/mysql/error.log | tail -20

Permission Errors Creating the New Log File

If logrotate cannot create the new log file after rotation, MySQL will fail to write logs. Ensure the directory permissions allow the logrotate user (root, typically) to create files with the correct ownership:

ls -lah /var/log/mysql/
# drwxr-x---  2 mysql adm  4096 Feb 23 02:00 .

# logrotate runs as root and creates the file with the owner specified in 'create'
# If create is specified, the new file is created before the postrotate script runs

Logrotate Not Running Because of SELinux or AppArmor

On systems with SELinux or AppArmor, the postrotate script's mysqladmin call may be blocked. Check audit logs:

# SELinux audit log check
ausearch -m avc -ts recent | grep mysqladmin

# AppArmor denials
grep mysqladmin /var/log/syslog | grep -i deny

Forgetting dateext for Multiple Rotations Per Day

If you rotate more than once per day (using size-based rotation with an hourly cron), logrotate will fail to create a second rotated file with the same name. Use dateext to append a timestamp to rotated filenames:

/var/log/mysql/mysql-slow.log {
    size 500M
    rotate 10
    dateext
    dateformat -%Y%m%d-%H%M%S   # e.g., mysql-slow.log-20260223-143000.gz
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        if mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf ping &>/dev/null 2>&1; then
            mysqladmin --defaults-extra-file=/etc/mysql/debian.cnf flush-slow-log
        fi
    endscript
}

Working with JusDB on MySQL Operations

JusDB handles MySQL operational tasks for engineering teams — log rotation, disk monitoring, binary log purging, and backup verification. Our DBAs set up proactive disk alerting so log files never fill your production servers.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• MySQL Binary Log Management: Purging, Rotation, and Best Practices
• MySQL Slow Query Log: Finding and Fixing Slow Queries
• MySQL Performance Schema: A Practical Guide