When manually executing logrotate, everything works perfectly:

# logrotate -v -f /etc/logrotate.d/mysql

But automated rotations via cron leave us with empty new log files while MySQL continues running. The rotated logs show proper compression:

-rw-r--r-- 1 mysql mysql      0 Aug  7 10:13 /var/log/mysqld.log
-rw-r--r-- 1 mysql mysql     20 Aug  4 04:04 /var/log/mysqld.log-20120804.gz

The key issue lies in MySQL's log file handle retention. When logrotate's create directive makes a new file, MySQL doesn't automatically switch to writing to it. The flush-logs command in our postrotate script isn't properly authenticated.

While /root/.my.cnf contains proper credentials:

[mysqladmin]
user            = root
password        = pa$$w0rd

Cron jobs don't inherit the same environment variables and may fail to locate this file.

Modify the logrotate configuration to explicitly provide credentials:

/var/log/mysql-slow.log /var/log/mysqld.log {
    daily
    rotate 7
    dateext
    compress
    missingok
    create 644 mysql mysql
    sharedscripts
    postrotate
        /usr/bin/mysqladmin --defaults-file=/root/.my.cnf flush-logs
    endscript
}

For systems where root access isn't available:

postrotate
    if [ -e /var/lib/mysql/mysql.sock ]; then
        /usr/bin/mysqladmin --user=admin --password=correct-horse-battery-staple flush-logs
    fi
endscript

After implementation, verify with:

# grep "Log rotation" /var/log/mysqld.log
# ls -la /var/log/mysql*

For clustered environments, consider adding error handling:

postrotate
    for i in {1..3}; do
        if /usr/bin/mysqladmin --defaults-file=/root/.my.cnf flush-logs; then
            logger "MySQL logs flushed successfully"
            break
        else
            sleep $i
        fi
    done
endscript

When running logrotate manually via command line, everything works as expected:

# logrotate -v -f /etc/logrotate.d/mysql

However during automated cron execution at 4 AM, while the files get rotated, MySQL stops writing new errors to the freshly created log file:

-rw-r--r-- 1 mysql mysql      0 Aug  7 10:13 /var/log/mysqld.log
-rw-r--r-- 1 mysql mysql     20 Aug  4 04:04 /var/log/mysqld.log-20120804.gz
-rw-r--r-- 1 mysql mysql     20 Aug  5 04:04 /var/log/mysqld.log-20120805.gz

The core issue stems from permission and context problems during automated execution:

• Cron runs with different environment variables than interactive shell
• The mysqladmin flush-logs command in postrotate fails silently
• File creation permissions (644 mysql mysql) may not propagate properly

Here's the corrected configuration that works reliably:

/var/log/mysql-slow.log /var/log/mysqld.log {
    daily
    rotate 7
    dateext
    compress
    missingok
    sharedscripts
    create 640 mysql mysql
    postrotate
        # Explicitly specify config file with full path
        /usr/bin/mysqladmin --defaults-file=/root/.my.cnf flush-logs
        # Alternatively for systemd systems:
        # systemctl restart mysqld || true
    endscript
}

Several critical changes were made:

1. Changed file permissions to 640 (more secure for log files)
2. Added full path to the mysqladmin command
3. Explicitly specified the config file location
4. Added alternative systemd restart option

To test the solution:

# Simulate logrotate execution
sudo logrotate -v -f /etc/logrotate.d/mysql

# Check active log file
ls -la /var/log/mysqld.log

# Generate test error
mysql -e "THIS WILL CAUSE AN ERROR"

# Verify error appears in log
tail -20 /var/log/mysqld.log

For systems using systemd, consider these enhancements:

postrotate
    # For modern MySQL installations
    if [ -d /run/systemd/system ]; then
        systemctl kill -s HUP mysqld.service >/dev/null 2>&1 || true
    else
        /usr/bin/mysqladmin --defaults-file=/root/.my.cnf flush-logs
    fi
endscript

When troubleshooting logrotate issues:

• Check mail to root (cron errors often go there)
• Test with logrotate -d for dry-run debugging
• Examine /var/lib/logrotate.status for last run times
• Add su mysql mysql directive for permission context