How to delete binary log in mysql

3 methods: 1. Delete all logs with the syntax "RESET MASTER;". After deletion, new binary logs will be created again; 2. Delete logs according to the number with the syntax "PURGE MASTER LOGS TO ' file name.number';", all binary logs with numbers less than the specified "number'" will be deleted; 3. Delete logs based on the creation time, the syntax is "PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Mysql method of deleting binary logs

After the binary log is started, a new binary log file will be generated every time the MySQL service is restarted. .

The binary log records a large amount of information. If the binary log is not cleaned for a long time, a lot of disk space will be wasted. There are many ways to delete binary logs. Here are several ways to delete binary logs.

Method 1: Delete all binary logs

Use the RESET MASTER statement to delete all binary logs. The form of the statement is as follows:

RESET MASTER;

Log in After opening the MySQL database, you can execute this statement to delete all binary logs. After all binary logs are deleted, MySQL will re-create new binary logs with numbers starting from 000001.

Method 2: Delete binary logs based on number

Each binary log file is followed by a 6-digit number, such as 000001. Logs up to the specified binary log number can be deleted using the PURGE MASTER LOGS TO statement. The basic syntax form of this statement is as follows:

PURGE MASTER LOGS TO 'filename.number';

This statement will delete all binary logs with numbers less than filename.number.

Example:

//删除bin-log.000002之前的二进制日志
purge binary logs to 'bin-log.000002';

Method 3: Delete binary logs based on creation time

Use PURGE The MASTER LOGS TO statement can delete binary logs created before the specified time. The basic syntax format of this statement is as follows:

PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

where "hh" is the 24-hour hour. This statement will delete all binary logs created before the specified time.

Example: Delete the binary log created before 2019-12-20 15:00:00

PURGE MASTER LOGS TO '2019-12-20 15:00:00";

After the code is executed, all the binary logs created before 2019-12-20 15:00:00 Binary logs will be deleted.

In addition, we can also set the expire_logs_days parameter to set automatic cleanup. Its default value is 0, which means that the expired automatic deletion function is not enabled.

show variables like 'expire_logs_days';

If the automatic cleaning function is enabled, binary log files that exceed this number of days will be automatically deleted. Automatic deletion usually occurs when MySQL starts or when FLUSH logs are flushed.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to delete binary log in mysql. For more information, please follow other related articles on the PHP Chinese website!