DEV Community

Cover image for Clear out MySql/MariaDB binary log files
Kinjal
Kinjal

Posted on

Clear out MySql/MariaDB binary log files

I came across the following error while resroting a databsae in MariaDB.

ERROR 3 (HY000) at line 87066: Error writing file '/tmp/MLvCaXNh' (Errcode: 28 "No space left on device") 
Enter fullscreen mode Exit fullscreen mode

This is due to the excesive binary logs which are generated by database server.

From MariaDB documentation - The binary log contains a record of all changes to the databases, both data and structure, as well as how long each statement took to execute. It consists of a set of binary log files and an index.

It is required to periodically clear out those logs to avoid runninng into the problem of No space left on device.

Clearing old log files also helps the process of database replication.

List all the binary logs:

mysql> show binary logs;
Enter fullscreen mode Exit fullscreen mode

And the query to delete all old files that were generated 2 days back:

mysql> purge binary logs before date(now() - interval 2 day);
Enter fullscreen mode Exit fullscreen mode

You can only delete the files that are older than the oldest file that is used by the slaves.

The diskspace error should be gone after clearing the logs.

Where these log files are stored?
mysql-binary-log

Usually log files are stored in /opt/mariadb/data.
Alt Text

Reference:
PURGE BINARY LOGS

Discussion (0)