I have a MySQL database server that just ran out of disk space. I have 0 disk space left, and MySQL is also not responding. Upon checking the server, I noticed a lot of mysql-bin files, something like ‘mysql-bin.001211’ with sizes around 1 or 2 GB. Is there a way to remove these files to free up some space on the server? How can I do this safely without causing any issues with the database?
Those files are binary logs used by the MySQL database management system. Binary logs record changes made to the database, such as data changes and schema changes. They are used for several purposes, including replication, backup, and recovery. Binary log files can grow quite large over time, especially if the database is heavily used. As a result, it is important to manage these files carefully to avoid running out of disk space. One way to do this is to remove old log files that are no longer needed regularly.
Don’t delete them by yourself. Let MySQL handle the delectation.
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';
This will clear all binary logs created before the specified log file or timestamp."
For example.
PURGE BINARY LOGS TO 'mysql-bin.001211';
Recommended
Alternatively, you can remove all binary logs older than three days.
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
Automatically Remove mysql-bin files.
If you want to automatically remove three days’ older log files, set the command below.
SET GLOBAL expire_logs_days = 3;
Then add this to /etc/my.cnf
[mysqld]
expire_logs_days=3
This variable determines the maximum age of binary logs in days before they are automatically deleted.