The correct deletion method of MYSQL logs is explained in detail

  • 2021-01-02 22:00:51
  • OfStack

This article details the correct way to delete MYSQL logs. To share for your reference, the details are as follows:

1. Look for:


MySQL> show binary logs;
+ -- -- - -+ -- � +
| Log_name | File_size |
+ -- -- - -+ -- � +
| mysql-bin.000001 | 150462942 |
| mysql-bin.000002 | 125 |
| mysql-bin.000003 | 106 |
+ -- -- - -+ -- � +

2. Delete ES7en-ES8en (delete mysql-ES10en.000003 without including ES11en-ES12en.000003)


mysql> purge binary logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.16 sec)

3. Query results (now only 1 record).


mysql> show binlog events/G
*************************** 1. row ***************************
Log_name: mysql-bin.000003
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 106
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(mysql-bin.000001 and mysql-bin.000002 Has been deleted )
mysql> show binary logs;
+ -- -- - -+ -- � +
| Log_name | File_size |
+ -- -- - -+ -- � +
| mysql-bin.000003 | 106 |
+ -- -- - -+ -- � +
1 row in set (0.00 sec)

(Delete other format to use!)


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

Removes all base 2 logs listed in the log index prior to the specified log or date. These logs are also deleted from the list recorded in the log index file, so that the given log becomes the first.

Such as:


PURGE MASTER LOGS TO 'mysql-bin.010 ' ;
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00 ' ;

Clear binlog from 3 days ago


PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);

The date argument to the BEFORE variable can be in 'ES33en-ES34en-ES35en hh:mm:ss' format. MASTER and BINARY are synonyms.

If you have an active slave server that is currently reading log 1 that you are trying to delete, this statement will not work but will fail with an error. However, if the slave server is inactive and you happen to clean up one of the logs it wants to read, the slave server cannot replicate after startup. This statement can run safely while the slave server is replicating. You don't need to stop them.

To clean up the logs, follow these steps:

1. On each slave server, use SHOW SLAVE STATUS to check which log it is reading.

2. Use SHOW MASTER LOGS to get series 1 logs on the primary server.

3. Determine the earliest log among all slave servers. This is the target log. This is the last log on the list if all the slave servers are updated.

4. Make a backup of all the logs you will delete. (This step is optional, but recommended.)

5. Clean up all logs, but not the target logs.

Set on contab:

0 1 * * *  `mysql -uroot -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);'`

For more information about MySQL, please refer to: MySQL Log Tips, MySQL Transaction Tips, MySQL Stored Procedure Tips, MySQL Database Lock Tips and MySQL Common Functions.

I hope this article has been helpful to you with the MySQL database.


Related articles: