MySql

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") 

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;

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

mysql> purge binary logs before date(now() - interval 2 day);

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?

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