MySQL

Delete mysql-bin files safely

The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:

PURGE BINARY LOGS TO ‘binlogname’;
PURGE BINARY LOGS BEFORE ‘datetimestamp’;

These will clear all binary logs before the binlog or timestamp you just specified.

For example, if you login to mysql run


mysql> PURGE BINARY LOGS TO 'mysql-bin.000223';

this will erase all binary logs before ‘mysql-bin.000223’.

If you run


mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:


mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf


[mysqld]
expire-logs-days=3

and mysqld will delete them logs for you
SHOW SLAVE STATUS\G

This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run


PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

To clean up Binary Log on Master Server


shell> mysql -u username -p
mysql> RESET MASTER;

To clean up Binary Log on Slave Server


mysql -u username -p
mysql> RESET SLAVE;

Remove MySQL Binary Log with PURGE BINARY LOGS Statement
PURGE BINARY LOGS statement can remove Binary Log based on date or up to a Binary Log sequence number
Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015


shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';

Alternatively, you can remove the binary older than a specific date.


shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';

Remove MySQL Binary Log with mysqladmin flush-logs Command
Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.


shell> mysqladmin -u username -p flush-logs

Keep MySQL Binary Log for X Days

All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf


expire_logs_days = 7

Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.


nano /etc/my.cnf
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin


If you drive is 100% full

Mysql error log shows –


ERROR 3 (HY000): Error writing file './mysql-bin.~rec~' (Errcode: 28)

and the commands do not work, try this:

Stop mysql or kill the process
Zero out the first log file
remove the line for the first binary file in mysql-bin.index
restart mysql
Then run the commands to purge