How to delete mysql bin

When using the MySQL database, we may encounter some usage problems, one of which is that the bin log is too large. When the bin log occupies too much disk space, these files need to be cleaned and deleted in time to maintain the normal operation of the database. This article will introduce how to delete the MySQL bin log.

What is bin log?

In the MySQL database, the bin log (binary log) is a file that records database operation logs. It contains all update, insert and delete operations in the database. Through these bin log files, database administrators can implement functions such as disaster recovery, data migration, and monitoring user behavior. However, since the bin log records all database operations, this also means that these files may occupy a relatively large space and need to be cleaned up in time.

How to delete bin log?

In MySQL, there are two ways to delete bin logs: manual deletion and automatic deletion.

Manually delete bin log

The method to manually delete bin log is through MySQL command. First, you need to query the currently used bin log file. You can execute the following command:

mysql> SHOW BINARY LOGS;

After executing the above command, output similar to the following will appear:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107       |
| mysql-bin.000002 | 254       |
| mysql-bin.000003 | 116       |
| mysql-bin.000004 | 131       |
+------------------+-----------+

The above output list shows the currently enabled bin The name and size of the log file. Next, in order to delete bin logs with a length within a certain range, you can perform the operation through the following command:

mysql> PURGE BINARY LOGS TO 'mysql-bin.000003';

The above command will delete all log files previously generated in the 'mysql-bin.000003' log file, This log file is not included.

Automatically delete bin logs

In order to automatically delete bin log files, you can use the configuration file to achieve this. Add the following lines to the MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf to automatically clean up the bin log:

expire_logs_days=7
binlog_expire_logs_seconds=2592000

Among them, the expire_logs_days variable defines the bin log deletion time period as 7 days, the binlog_expire_logs_seconds variable defines the maximum storage time of bin logs as 2592000 seconds, which is 30 days.

It should be noted that these parameter values ​​need to be modified according to actual needs. For example, if you want to delete bin logs from 1 month ago, you can set expire_logs_days to 30.

Conclusion

When using a MySQL database, it is very important to understand and manage bin log files. Excessive log files may affect database performance or even cause a crash. Through the method described in this article, administrators can manage and clear unnecessary bin log files to ensure that the database is always in good condition.

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