Fixing MySQL disk-space

We saw a sudden increase in disk usage last month.

We store all persistent data on /data-volume. I checked what is using the space using:

sudo du -h --max-depth 1 /data-volume | sort -h

It showed MySQL folder was using around 150 GB. It was huge. Last week it was ~120 GB.

I was very concerned about this increase and dug more into it. I thought it was because of the full-text search index. But I wanted to be sure.

I was searching for a way to get stats around what is using space inside MySQL. Interestingly, the easiest solution to this was to do list the directory contents here too.

sudo du -h --max-depth 1 /data-volume/mysql | sort -h

It showed only ~18GB being used by production_db. The directory size was however ~120GB.

Doing ls on the directory revealed lots of binlog files. Some of these were in GBs.

Psssst! It was the event logs that were taking up the space.

The binlog files are used for replication. The updates are communicated between the primary and replica servers using the binlog files.

Earlier versions of MySQL (<8.0.11) retained binlog files of the last 5 days. However, now the default is 30 days. This was taking up most of the space.

We purged the old binlog files using:

PURGE BINARY LOGS BEFORE '2021-10-10 22:46:26';

This freed up around ~80 GB.

We also updated the default value for binlog_expire_logs_seconds to retain logs only for 15 days going forward.