Two Methods for mysql to Clean binlog Logs Correctly

mysq properly cleans binlog logs

Foreword:

binlog log in MySQL records the changes of data in the database, which is convenient for the recovery of data based on time point and location. However, binlog will increase day by day and occupy a large disk space. Therefore, it is necessary to use correct and safe methods to clean up 1 part of useless logs for binlog.

[Method 1] Manually clean binlog

Preparation before cleaning:

See which binlog files are being used by the master and slave libraries

show master status\G 
show slave status\G  

Before deleting the binlog log, first back up the binlog log in case of 10,000

Start deleting binlog:

purge master logs before'2016-09-01 17:20:00'; // Deletes the log index before the specified date binlog Log file 

Or

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

Note:

Time and filename 1 must not be written wrong, especially the year in the time and the serial number in the filename, in case you accidentally delete the binlog you are using! ! !

Never delete the binlog you are using! ! !

Using this syntax, the corresponding file and the corresponding path in mysql-bin. index are deleted.

[Method 2] By setting the expiration time of binlog, the system automatically deletes binlog files

mysql> show variables like 'expire_logs_days'; 
+------------------+-------+ 
| Variable_name  | Value | 
+------------------+-------+ 
| expire_logs_days |   0  | 
+------------------+-------+ 
mysql> set global expire_logs_days = 30;    # Settings binlog How many days will it expire 

Note:

The expiration time should be set properly. For master-slave replication, it is necessary to see the delay of the slave library to decide the expiration time, so as to avoid deleting the master library binlog before it is transmitted to the slave library due to expiration, resulting in the master never being 1! ! !

Thank you for reading, hope to help everyone, thank you for your support to this site!