Mysql correctly clears binlog in two ways: mysqlbinlog

Mysql correctly clears binlog in two ways: mysqlbinlog

Mysq correctly clears binlog logs

Preface:

The binlog log in MySQL records data changes in the database, which facilitates data recovery based on time points and locations. However, binlog also increases and occupies a large disk space. Therefore, correct and secure methods should be used to clear unused logs.

Method 1: manually clear binlog

Preparations before cleaning:

① Check which binlog file is used by the master database and slave Database

show master status\G show slave status\G  

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

Start to delete binlog:

Purge master logs before '2017-09-01 17:20:00 '; // Delete the binlog log file 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

Note:

The time and file name cannot be written incorrectly, especially the sequence numbers in the year and file names in the time, to prevent accidental deletion of the binlog in use !!!

Do not delete the binlog in use !!!

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

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

Mysql> show variables like 'expire _ logs_days '; + ------------------ + ------- + | Variable_name | Value | + -------------------- + ------- + | expires | 0 | + ------------------ + ------- + mysql> set global expire_logs_days = 30;

Note:

The expiration time should be set appropriately. For master-slave replication, the expiration time should be determined based on the slave database delay. This prevents the master database from being deleted due to expiration before the binlog is uploaded to the slave database, causing the master to be inconsistent !!!

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!