How to Delete MySQL Binlog Files and Recover Data from Logs

If you install MySQL, you’ll most likely enable the binlog feature. The benefit of enabling it is that it makes future data recovery easier, but the downside is that log files grow quickly and can soon eat up your disk space.

Therefore, we need to regularly or manually delete log files that have become too large.

The methods for deleting log files are roughly as follows:

1. Set the Log Retention Period expire_logs_days for Automatic Deletion

#This sets BINLOGs to be kept for only 7 days. This parameter will default back to its original setting the next time MySQL restarts, so you need to set it in my.cnf.  

2. Manually Delete BINLOGs (purge binary logs)

1. #Used to delete all binary logs listed in the log index before the specified log or date. These logs are also removed from the record in the log index file.  
2.  
3. PURGE {MASTER | BINARY} LOGS TO ‘log_name’  
4. PURGE {MASTER | BINARY} LOGS BEFORE ‘date’  
5.  
6. #For example:  
7.  
8. PURGE MASTER LOGS TO ‘mysql-bin.010′;  
9. PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00′;  
10. PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  
11.  

Others:

1. View the current list of log files: show binary logs; 

The purpose of using binlog logs is to facilitate data recovery, so we not only need to know how to delete them but also how to use the logs to recover data.

Using binlog to Recover Data:

Use the mysqlbinlog command to restore logs

※Appendix   
#1. For usage of mysqlbinlog on MySQL 5.0, refer to: official documentation   
#2. If you don’t know the storage location of the log-bin files, you can use the following command to check:   
mysql -uroot -ppassword -e 'SHOW BINLOG EVENTS /G'   
#3. When you only need to recover partial records, you can use time points or position points for positioning, for example:   
mysqlbinlog –start-date="2005-04-20 9:01:00" –stop-date="2005-04-20 10:00:01" /var/log/mysql/bin.123456|mysql -uroot -ppassword[code]   
#This means restoring data from the morning of April 20th between 9 AM and 10 AM.   
mysqlbinlog –start-position="368301" –stop-position="368312"/var/log/mysql/bin.123456| mysql -uroot -pmypwd   
#This means redoing the operations between position points 368301 and 368312. (The position points can be the `at xxx` nodes seen using mysqlbinlog)