How to Disable Binary Logging in MariaDB and MySQL?

Procedure to disable binary logs in MariaDB and MySQL database.

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.

root@geekflare:/var/log# du -sh mysql
9.4G	mysql
root@geekflare:/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
root@geekflare:/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 to instruct MariaDB not to log. I don’t have replica implementation so it is ok to disable the binary logging.

It is a simple one-line configuration.

If using MariaDB

• Login to the server 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.

If using MySQL

• Login to the MySQL server
• Go to /etc/my.cnf.d/
• Add the following in mysql-server.cnf file in [mysqld] directive

skip-log-bin

• Save the file and restart the MySQL server

service mysqld stop
service mysqld start

Now, you can delete those binaries files under /var/lib/mysql. Hope this helps.

If you are running a critical application then you may also want to consider going for managed MySQL hosting who take care of performance optimization.