Introduction MySQL slow query log files grow continuously and need regular rotation. When logrotate or manual rotation fails with permission errors, the slow query log can consume all available disk space, and MySQL may fail to write new log entries, losing valuable performance data.

Symptoms - `logrotate` reports `error: error renaming /var/log/mysql/slow.log to /var/log/mysql/slow.log.1: Permission denied` - MySQL error log shows `Could not use /var/log/mysql/slow.log for logging (error 13 - Permission denied)` - Slow query log file grows beyond expected size, filling disk partition - `ls -la /var/log/mysql/` shows files owned by `root` instead of `mysql` - MySQL stops logging slow queries after failed rotation

Common Causes - logrotate running as root creates new log file owned by root - MySQL process running as `mysql` user cannot write to root-owned file - SELinux context preventing MySQL from accessing rotated log files - Manual log file creation with wrong ownership - Incorrect `create` directive in logrotate configuration

Step-by-Step Fix 1. **Check current log file ownership and permissions": ```bash ls -la /var/log/mysql/slow* # Expected: -rw-r----- mysql adm slow.log ```

  1. 1.**Fix file ownership":
  2. 2.```bash
  3. 3.sudo chown mysql:adm /var/log/mysql/slow.log
  4. 4.sudo chmod 640 /var/log/mysql/slow.log

# Fix all rotated files too sudo chown mysql:adm /var/log/mysql/slow.log.* ```

  1. 1.**Fix the logrotate configuration":
  2. 2.`
  3. 3.# /etc/logrotate.d/mysql-slow
  4. 4./var/log/mysql/slow.log {
  5. 5.daily
  6. 6.rotate 14
  7. 7.compress
  8. 8.delaycompress
  9. 9.missingok
  10. 10.notifempty
  11. 11.create 640 mysql adm
  12. 12.sharedscripts
  13. 13.postrotate
  14. 14.if test -x /usr/bin/mysqladmin && \
  15. 15./usr/bin/mysqladmin ping &>/dev/null
  16. 16.then
  17. 17./usr/bin/mysqladmin flush-slow-log
  18. 18.fi
  19. 19.endscript
  20. 20.}
  21. 21.`
  22. 22.**Test the rotation manually":
  23. 23.```bash
  24. 24.# Run logrotate in debug mode
  25. 25.sudo logrotate -d /etc/logrotate.d/mysql-slow

# Force rotation sudo logrotate -f /etc/logrotate.d/mysql-slow

# Verify MySQL can still write to the new file sudo -u mysql touch /var/log/mysql/slow.log ```

  1. 1.**If MySQL stopped logging, re-enable slow query log":
  2. 2.```sql
  3. 3.-- Check slow log status
  4. 4.SHOW VARIABLES LIKE 'slow_query%';

-- Toggle to re-open the log file SET GLOBAL slow_query_log = 'OFF'; SET GLOBAL slow_query_log = 'ON';

-- Verify SHOW VARIABLES LIKE 'slow_query_log_file'; ```

Prevention - Always use `create 640 mysql adm` in logrotate configurations - Test log rotation in staging before deploying to production - Monitor slow query log file size alongside disk usage - Use `mysqladmin flush-slow-log` instead of file rename for rotation - Include logrotate checks in deployment validation - Set up monitoring for slow query log file age and size - Consider using `syslog` for slow query logging instead of file-based logging