Detailed explanation of the correct deletion method of MYSQL logs and detailed explanation of mysql logs

Detailed explanation of the correct deletion method of MYSQL logs and detailed explanation of mysql logs

This article describes how to delete MYSQL logs in detail. We will share this with you for your reference. The details are as follows:

1. Search:

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

2. Delete the bin-log (before removing the mysql-bin.000003 without containing the mysql-bin.000003)

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

3. query results (only one record is available now .)

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

(Other deleted formats are used !)

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

Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.

For example:

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

Clear binlog 3 days ago

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

The date argument for the BEFORE variable can be in the 'yyyy-MM-DD hh: mm: ss' format. Both MASTER and BINARY are synonyms.

If you have an active slave server that is currently reading one of the logs you are trying to delete, this statement does not work, but fails with an error. However, if the slave server is slave and you happen to have cleared one of the logs it wants to read, the slave server cannot be copied after it is started. This statement can be run safely when the slave server is being copied. You do not need to stop them.

To clear logs, follow these steps:

1. Use show slave status on each SLAVE server to check which log it is reading.

2. Use show master logs to obtain a series of LOGS on the MASTER server.

3. Identify the earliest logs among all slave servers. This is the target log. If all slave servers are updated, this is the last log in the list.

4. Back up all the logs you want to delete. (This step is optional, but is recommended .)

5. Clear all logs, but not the target logs.

Set in contab:
Copy codeThe Code is as follows: 0 1 *** 'mysql-uroot-e' purge master logs before DATE_SUB (NOW (), INTERVAL 3 DAY );''