Binary logs eating up your space? Turning off logging and purging logs

I work a lot with developers who have non prod testing environments for their application. Frequently we set up replication for testing something and then the developer forgets about it. Weeks later the test system is full of binary logs because they are not expiring them or they performed an operation that wrote a lot of binary log files.

You may see a large amount of bin files in the MySQL data directory named "server-bin.n" or mysql-bin.00000n, where n is a number that increments. These are typically in a directory at /var/lib/mysql.

The binary log stores all the DDL and DML operations (INSERT, UPDATE, DELETE, CREATE, DROP...).

The binary log can be used for Data Recovery if you haven't taken a backup in the last few hours and you need to revert back to a specific point in the time before something happened within the last time you took a backup. The binary logs also allows replication to work by recording the DDL/DML statements so they can replayed on the slave servers.

 If you are not using replication and don't care about performing a data recovery then you should not have bin logging turned on and should turn it off by editing the my.cnf file.  Look for an entry called "log_bin" and comment it out. There may also be entires related to  binary logging that start "bin" or "expire_log_days" or "max_binlog_size". These can all be commented out or removed (be sure to backup the file before making changes).

After saving the file restart MySQL.

If you want to simply leave binary logging on but purge the files there are two ways to do this.

1. Purge up to a specific file

Example:
PURGE BINARY LOGS TO 'mysql-bin.09';

2. Purge up to date

Example:
PURGE BINARY LOGS BEFORE '2015-12-17 11:11:11';