MySQL 8 storage grows so fast on Ubuntu, disable your binlog!

My specification:

• MySQL 8.0.41
• Ubuntu server 24.0.1 LTS

An issue in my Ubuntu is the remaining storage shrink so fast. After trying to calculate the size of each folder, i found a strange thing.

du -sh /var/lib/*

/var/lib/mysql has 3.8GiB, however i just have a database with small data in it. I then go into mysql folder, then calculating again with du -sh

du -sh /var/lib/mysql/*

As you see above, it created many binlog file that have 100MB of each file. That means, your mysql binary log is on, that consumes a lot of size.

If you don't need to use binary log, then you can disable it to keep your storage free.

Disable binary log MySQL 8

Before disabling binary log permanently in MySQL 8, let’s remove the existing binary logs, by:

FLUSH BINARY LOGS;
PURGE BINARY LOGS BEFORE NOW();

Now, if you look it back to mysql folder, the binary log files are gone.

ls -al

Next, disable mysql from binary log creation, open mysqld.conf

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following parameter to the [mysqld] section to disable binary log.

skip-log-bin

Save it, then restart your mysql to take the effect.

systemctl restart mysql

After restarted, you can check if binary log is already OFF, by typing this query:

SHOW VARIABLES LIKE 'log_bin'

And my available storage size get back (previously: 2.GiB, after binlog OFF: 6.3GiB)

df -h

Hope this help !

Related