Symptom: The website became slower and slower, and eventually became completely inaccessible. Upon investigation, the disk was found to be full. A detailed check confirmed that MySQL binary logs (binlogs) had grown too large and consumed all the disk space.
Analysis and Solution: When this kind of issue occurs, you should typically log into the server to check disk, memory, and process usage using commands like top, df -h, and free -m. This revealed that the disk space was exhausted. Further investigation with du -sh on suspicious directories showed that MySQL’s binlog was occupying excessive space. Here is how to clean up the binlog:
1) Set the expire_logs_days retention period for automatic deletion
Check the current log retention days:
show variables like '%expire_logs_days%';
This defaults to 0, meaning logs never expire. You can make a temporary change by setting the global parameter:
set global expire_logs_days=7;
This sets BINLOGs to be kept for only 7 days. Since this parameter will revert to default after the next MySQL restart, you need to set it in my.cnf:
expire_logs_days = 7
2) Manually delete BINLOG (purge binary logs)
This is used to delete all binary logs listed in the log index before a specified log name or date. These logs will also be removed from the log index file.
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
For example:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);