Procedure to disable binary logs in MariaDB

A few days back, Geekflare was unavailable, and the reason was simple – no space left on the device. Had hundreds of alerts and emails from the audience about the website being down but I was traveling so couldn’t take action on time.

This was logged in Google Cloud logs.

Feb 13 15:58:35 geekflare snapd[1308]: daemon.go:379: started snapd/2.37.1 (series 16; classic) ubuntu/18.04 (amd64) linux/4.15.0-1027-gcp.
Feb 13 15:58:35 geekflare snapd[1308]: main.go:123: system does not fully support snapd: mkdir /tmp/sanity-mountpoint-464926152: no space left on device
Feb 13 15:58:35 geekflare systemd[1]: Started Snappy daemon.

This was a surprise to me as Geekflare is just a simple blog and doesn’t store or process huge data. So, I started investigating and found /var/log/mysql was taking ~50% of total storage.

[email protected]:/var/log# du -sh mysql
9.4G	mysql
[email protected]:/var/log#

And, after listing the files.

-rw-rw---- 1 mysql adm 104860436 Feb 10 19:05 mariadb-bin.000105
-rw-rw---- 1 mysql adm 104869270 Feb 10 22:12 mariadb-bin.000106
-rw-rw---- 1 mysql adm 104868195 Feb 11 01:41 mariadb-bin.000107
-rw-rw---- 1 mysql adm 104859088 Feb 11 04:44 mariadb-bin.000108
-rw-rw---- 1 mysql adm 104862187 Feb 11 07:22 mariadb-bin.000109
-rw-rw---- 1 mysql adm 104860386 Feb 11 09:25 mariadb-bin.000110
-rw-rw---- 1 mysql adm 104867622 Feb 11 11:22 mariadb-bin.000111
-rw-rw---- 1 mysql adm 104871043 Feb 11 13:18 mariadb-bin.000112
-rw-rw---- 1 mysql adm 104873816 Feb 11 15:07 mariadb-bin.000113
-rw-rw---- 1 mysql adm 104858814 Feb 11 17:09 mariadb-bin.000114
-rw-rw---- 1 mysql adm 104858302 Feb 11 19:18 mariadb-bin.000115
-rw-rw---- 1 mysql adm 104859019 Feb 11 21:33 mariadb-bin.000116
-rw-rw---- 1 mysql adm 104860830 Feb 12 00:05 mariadb-bin.000117
-rw-rw---- 1 mysql adm      3298 Feb 12 02:58 mariadb-bin.index
-rw-rw---- 1 mysql adm 104873697 Feb 12 02:58 mariadb-bin.000118
-rw-rw---- 1 mysql adm 104259584 Feb 13 15:24 mariadb-bin.000119
-rw-rw---- 1 mysql adm         0 Feb 13 15:25 mariadb-bin.state
[email protected]:/var/log/mysql#

It was binary logs.

You may think of deleting them to recover the system but keep in mind it will fill up again. So, the best way to prevent this is instruct MariaDB not to log. I don’t have replica implementation so it is ok to disable the binary logging.

Its simple one-line configuration.

  • Login to the serve where MariaDB is running
  • Go to /etc/mysql/conf.d
  • Take a backup of mariadb.cnf
  • Add the following under [mysqld] directive
skip-log-bin
  • Save the configuration file

At this time, you may delete those binary log files and restart the database.