Welcome to reprint, but please quote the original source at the beginning or end.

background

In the middle of this year, our company reached an in-depth cooperation with Tencent, involving a large amount of data. Sure enough, after talking about cooperation, I am still responsible for the implementation of the “commander of everything”.

Specific process:

  1. Sift out some data from our existing products
  2. Data cleaning
  3. (because the boss said that the future cooperation is not only with Tencent)
  4. Output to Tencent (real-time processing into the format required by Tencent) and record the output time locally

Because data cleaning + processing takes time and space, I temporarily made a high-performance desktop computer as the server (yes, Ben “Commander of Everything” is also responsible for assembling the computer), and configured:

  • CPU: AMD Ryzen 5 3600
  • MEM: 32GB
  • SSD: 1TB

The problem

Still in the process of data cleaning and processing, suddenly found that the program script unexpectedly quit.

A large number of binlog. XXXXXXX files were found in the /var/lib/mysql directory. Ncdu analysis showed that these files occupied about several hundred GB of disk space.

(There were no screenshots before dealing with the problem at that time, so I could only take a few pictures of the graph when WRITING this article. When writing this article, the cooperation was in the final output process, so the database write operation was only related to the record output time, and the amount of data was much smaller.)

why

Binlog records all database TABLE structure changes (e.g. CREATE, ALTER TABLE…). And table data changes (INSERT, UPDATE, DELETE…) Binary log.

Binlog does not record operations such as SELECT and SHOW because they do not modify the data itself, but you can query the general log to see all statements executed by MySQL.

Binary log files are classified into two types: binary log index files (file name extension:.index), which record all binary files; binary log files (file name extension:.00000*), which record all DDL and DML statements (except data query statements) of the database.

So to simplify the description, the more database writes, the more frequent the binlog records, and even the explosion of the author encountered.

To solve

Since the binlog is a log, as long as we make sure (or pretend to make sure) that everything we did was correct and we don’t need to recover from the binlog, we delete it.

Removed manually

Delete binlog.0* from /var/lib/mysql.

This method is not recommended because manual deletion does not update binlog.index, which is used to speed up the search for binlog files.

Deletes the binlog before the specified number

mysql> PURGE MASTER LOGS TO 'binlog.000860'; Query OK, 0 rows affected (0.01sec)Copy the code

Deletes the binlog before the specified date

mysql> PURGE MASTER LOGS BEFORE '2020-11-11 11:11:11';
Query OK, 0 rows affected (0.19 sec)
Copy the code

Clear all binlogs

mysql> RESET MASTER;
Query OK, 0 rows affected (0.09 sec)
Copy the code

Configuring Automatic Clearing

mysql> set global expire_logs_days=7;
Copy the code

Only the binlog files generated within the last 7 days are retained.

Or modify the MySQL configuration file, set expire_logs_days to 7, and restart the service to take effect permanently.