Disable bin logs for MySQL on Ubuntu

MySQL 8 writes every change to your databases to binary logs, or binlogs. MySQL uses them for replication and for replaying changes after a restore. Ploi uses neither, because database backups in Ploi are dumps made with mysqldump. On a busy server the binlogs can still grow to many gigabytes.

You can purge the binlogs once, keep them for a shorter time, or turn binary logging off. Whichever you pick, purge first. Once binary logging is off, MySQL won't purge the old files for you anymore.

Check how much space the binlogs use

Connect to your server over SSH and run:

sudo sh -c 'du -ch /var/lib/mysql/binlog.* | tail -n 1'

MySQL names the files binlog.000001, binlog.000002 and so on. It starts a new file every time it restarts and every time the current file reaches 1 GB. By default MySQL 8 deletes binlogs after 30 days.

Log in to MySQL

Log in with the ploi database user:

mysql -u ploi -p -h 127.0.0.1

MySQL asks for the password. That is the database password you got when Ploi created the server. The root database user has the same password and works too.

Purge the binary logs

Run this inside MySQL:

PURGE BINARY LOGS BEFORE NOW();

MySQL deletes every binlog except the one it is writing to right now, and updates its list of binlogs. Check the result with:

SHOW BINARY LOGS;

One file should be left. To keep the most recent days, for example the last 3, purge up to that date instead:

PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

Don't delete binlogs with rm while binary logging is on. MySQL keeps a list of its binlogs in binlog.index and expects every file on that list to exist. If the server is a replication source, check that your replicas have read the logs before you purge them.

Keep binlogs for a shorter time

If you need binary logging, for example for your own replication setup, let MySQL delete the files sooner. Open /etc/mysql/mysql.conf.d/mysqld.cnf and add this line under [mysqld]. The value is in seconds, 259200 is 3 days.

binlog_expire_logs_seconds = 259200

Restart MySQL to apply it:

sudo service mysql restart

Older guides use expire_logs_days for this. MySQL 8.4 removed that setting and refuses to start when it is in the configuration file, so use binlog_expire_logs_seconds.

Turn off binary logging

Purge the binary logs first, as described above. Then open /etc/mysql/mysql.conf.d/mysqld.cnf and add this line under [mysqld]:

skip-log-bin

Restart MySQL:

sudo service mysql restart

Log in to MySQL again and check the setting:

SHOW VARIABLES LIKE 'log_bin';

The value should be OFF. If it still says ON, check that you edited the right file and restarted MySQL.

Without binlogs you can't replicate this server, and you can't replay changes made after your last backup. A restore brings the database back to the moment that backup was made.

Turned off binary logging without purging?

Then the old binlogs are still on disk, and PURGE BINARY LOGS can't remove them anymore. It finishes without an error and leaves every file where it is. SHOW BINARY LOGS answers with You are not using binary logging.

MySQL doesn't use these files anymore, so you can delete them yourself:

sudo sh -c 'rm -f /var/lib/mysql/binlog.[0-9]* /var/lib/mysql/binlog.index'

Only do this when log_bin is OFF. If you turn binary logging back on later, MySQL starts over at binlog.000001.

MariaDB

MariaDB on Ubuntu doesn't write binlogs unless you turned them on. If you did, log in the same way and run the same PURGE BINARY LOGS BEFORE NOW();. MariaDB keeps its binlogs wherever log_bin points, which you can look up with SHOW VARIABLES LIKE 'log_bin_basename';. Restart it with sudo service mariadb restart.

Let Ploi clean them up

Ploi's server insights also look for binlogs. When a server has a lot of binlog files, the insight offers an automatic fix. It purges the binlogs, turns off binary logging and restarts MySQL or MariaDB for you.