Disabling mysql binary logging

We are currently working on a php (laravel) application, that makes a lot of temporary writes to our mysql database, because we need the data in our models.

A couple of days ago, we started to have trouble with space on our staging server, that was setup with laravel forge.
We checked everything, but neither the project folder it self was full, nor the database size itself seemed out of the ordinary.

We were really looking for the problem and couldn’t find it. For that reason we started to check the folder sizes using my favorite bash command:

du -h / -d 1

Which showed a var/lib/mysql/ folder size of over 15 GB.

When I ran ll in this folder I got the following result:

 $ sudo ls /var/lib/mysql -l
 total 9416464
 …
 -rw-r — — — 1 mysql mysql 503 Mar 21 09:10 binlog.000001
 -rw-r — — — 1 mysql mysql 1073764606 Mar 23 13:12 binlog.000002
 -rw-r — — — 1 mysql mysql 1073811686 Mar 25 10:09 binlog.000003
 -rw-r — — — 1 mysql mysql 1073784664 Mar 26 07:33 binlog.000004
 -rw-r — — — 1 mysql mysql 1073759290 Mar 26 15:48 binlog.000005
 -rw-r — — — 1 mysql mysql 1073846533 Mar 30 13:10 binlog.000006
 -rw-r — — — 1 mysql mysql 1073774707 Apr 1 08:08 binlog.000007
 -rw-r — — — 1 mysql mysql 1074212971 Apr 1 23:50 binlog.000008
 -rw-r — — — 1 mysql mysql 1073771720 Apr 3 06:37 binlog.000009
 -rw-r — — — 1 mysql mysql 875431943 Apr 3 10:07 binlog.000010
 -rw-r — — — 1 mysql mysql 160 Apr 3 06:37 binlog.index
 …

This happened, because by default our server configuration was set to create logs, that could be shared in a mysql-cluster. In this case our mysql instance was set up to be a master instance and would save these logs to transport them over to slave instances.

In our case, we didn’t have any slave instances and so it did not really make sense to keep .

Disable Mysql Logging

Clear old binlog files

By default on forge mysql is setup with binary logs, that track changes and prepare to save them to a mysql cluster. In most cases we don’t need that and since our application writes a lot of temporary data to the database, these logs can get really huge.

If you already created these mysql logs, because you forgot to disable the logs, you can reset them by logging into the mysql console with the root mysql user:

mysql -uroot -p

Then use the following mysql commands to clear:

RESET MASTER;

or if you don’t want to delete all, but only older ones:

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 2 DAY) + INTERVAL 0 SECOND;

Disable binlogging

To make sure this doesn’t happen in the future again, you can just disable logging.

Open the my my.cnf file:

sudo vim /etc/mysql/my.cnf

And then add the following line into the file:

disable-log-bin # This line will disable the logs completely
# If you want to keep them for a certain amount of time, you can also just set a max time or size limit
# expire_logs_days        = 3
# max_binlog_size         = 500M

After this is done, restart the mysql server again:

sudo service mysqld restart

Warning: If you have added this to mysql and restarted the server, RESET MASTER or PURGE BINARY LOGS command will be ignored. Make sure to clear the logs, before you add this to the mysql.cnf file or remove it temporarily, purge and then add it again.

Better solution

This solution already helped us and we didn’t have any trouble with running out of space on our server so far, BUT a better solution for this would definitely be to not write that much into mysql. It’s not made for that really and we will probably use a different database for this in the future, but as a quick fix, this really helped us out until we have the time to refactor.