CODEWITHSUNDEEP
mysqlmysqlbinlog
Mohamed Ben HEnda
Mohamed Ben HEnda

Reputation: 2766

Delete mysql-bin.**** files from /var/log/mysql

In the /var/log/mysql I found that are many large files

-rw-rw----  1 mysql adm  104875724 Nov 16  2016 mysql-bin.002982
    -rw-rw----  1 mysql adm  104900467 Nov 16  2016 mysql-bin.002983
 ...............
    -rw-rw----  1 mysql adm  104919093 Nov 23  2016 mysql-bin.003118
    -rw-rw----  1 mysql adm  104857817 Nov 23  2016 mysql-bin.003119
    -rw-rw----  1 mysql adm  104858056 Nov 23  2016 mysql-bin.003120
    -rw-rw----  1 mysql adm    9184221 Nov 23  2016 mysql-bin.003121
    -rw-rw----  1 mysql adm  104907549 Nov 23  2016 mysql-bin.003122
 ......
    -rw-rw----  1 mysql adm       6272 Nov 25  2016 mysql-bin.index

Can I delete them?

Update

I don't use a replication for the database

Upvotes: 4

Views: 9816

Answers (3)

alexanderlz
alexanderlz

Reputation: 589

better not do it manually, you can do it through mysql.

PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';`

for example to delete everything before a week ago run:

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

or (even better) edit the my.cnf and set this parameter

[mysqld]
expire_logs_days=7

Upvotes: 8

damn sure So
damn sure So

Reputation: 116

These large files are MYSQL BINARY LOG, stores query event such as add, delete and update in a very details way. The Binary Log is used for two main purposes:

  • For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the events contained in its binary log to its replicas, which execute those events to make the same data changes that were made on the source.
  • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

There are several ways to remove or clean up MySQL Binary Log, it’s not recommend to clean up the file manually, you need to use PURGE BINARY LOGS statement to safely purge binary log files:

  1. On each replica, use SHOW SLAVE STATUS to check which log file it is reading.
  2. Obtain a listing of the binary log files on the replication source server with SHOW BINARY LOGS.
  3. Determine the earliest log file among all the replicas. This is the target file. If all the replicas are up to date, this is the last log file on the list.
  4. Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)
  5. Purge all log files up to but not including the target file.

Since you have not set up a backup, do the following:
you can also remove the binary older than a specific date, such as 2019-04-02 22:46:26,

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

PURGE BINARY LOGS statement structure:

PURGE { BINARY | MASTER } LOGS {
TO 'log_name'
| BEFORE datetime_expr
}

It's directly from the official website

Upvotes: 2

SantiMunoz
SantiMunoz

Reputation: 21

You can also delete them with a 1 liner.

It deletes all binary logs safely through MySQL except for the ones you are using in your current session.

purge binary logs before curdate();

Upvotes: 1

Related Questions