Two ways to correctly clean up mysql binlog logs

mysql correctly cleans up binlog logs

Preface:

The binlog in MySQL records the changes in the data in the database, which facilitates the time-based and location-based recovery of the data. However, the binlog will also grow larger and take up a lot of disk space. Therefore, it is necessary to use the correct and safe method to clean up some useless logs.

[Method 1] Manually clean up binlog

Preparation before cleaning:

① Check which binlog file the master and slave are using

show master status\G 
show slave status\G

② Before deleting the binlog log, back up the binlog log first, just in case

Start deleting binlog:

purge master logs before'2016-09-01 17:20:00'; //Delete the binlog log files in the log index before the specified date

or

purge master logs to'mysql-bin.000022'; //Delete the binlog log file in the log index of the specified log file

Notice:

The time and file name must not be written incorrectly, especially the year in the time and the serial number in the file name, to prevent the binlog in use from being accidentally deleted! ! !

Never delete a binlog that is in use! ! !

Using this syntax will delete the corresponding files and the corresponding paths in mysql-bin.index.

[Method 2] Set the binlog expiration time to enable the system to automatically delete the binlog file

mysql> show variables like 'expire_logs_days'; 
+------------------+-------+ 
| Variable_name | Value | 
+------------------+-------+ 
| expire_logs_days | 0 | 
+------------------+-------+ 
mysql> set global expire_logs_days = 30; #Set the number of days for binlog to expire

Notice:

The expiration time should be set appropriately. For master-slave replication, the expiration time should be determined based on the delay of the slave database to avoid the master database binlog being deleted due to expiration before being transferred to the slave database, resulting in inconsistency between the master and the slave! ! !

Thank you for reading, I hope it can help you, thank you for your support of this site!