MySQL bin-log PURGE 清除日志

我有一个每天进行大量数据更新的程序,写数据库,用的是innoDB,而且开了bin-log,这段时间,写了几百G的日志,太大了,前一段时间设置过 expire-logs-days =3,但没有生效,不知为什么。

后来实在受不了,上网找了找,原来有另一个方法清除bin-log,且看中文版:

PURGE {MASTER | BINARY} LOGS TO ‘log_name’

PURGE {MASTER | BINARY} LOGS BEFORE ‘date’

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除,这样被给定的日志成为第一个。

例如:

PURGE MASTER LOGS TO ‘mysql-bin.010’;

PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00’;

清除3天前的 binlog

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

BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。

如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。

要清理日志,需按照以下步骤:

1. 在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。

2. 使用SHOW MASTER LOGS获得主服务器上的一系列日志。

3. 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的,这是清单上的最后一个日志。

4. 制作您将要删除的所有日志的备份。(这个步骤是自选的,但是建议采用。)

5. 清理所有的日志,但是不包括目标日志

本文来自: (www.91linux.com) 详细出处参考:http://www.91linux.com/html/article/database/mysql/20080622/12727.html

以下是MySQL手册的英文原版:

13.6.1.1. 

PURGE MASTER LOGS

Syntax

PURGE {MASTER | BINARY} LOGS TO '

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

Deletes all the binary logs listed in the log index prior to the specified log or date. The logs also are removed from the list recorded in the log index file, so that the given log becomes the first.

Example:

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

The

BEFORE

variant’s

date

argument can be in

'YYYY-MM-DD hh:mm:ss'

format.

MASTER

and

BINARY

are synonyms.

This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. However, if a slave is dormant and you happen to purge one of the logs it has yet to read, the slave will be unable to replicate after it comes up.

To safely purge logs, follow this procedure:

  1. On each slave server, use
    SHOW SLAVE STATUS

    to check which log it is reading.

  2. Obtain a listing of the binary logs on the master server with
    SHOW BINARY LOGS

    .

  3. Determine the earliest log among all the slaves. This is the target log. If all the slaves are up to date, this is the last log on the list.
  4. Make a backup of all the logs you are about to delete. (This step is optional, but always advisable.)
  5. Purge all logs up to but not including the target log.

You can also set the

expire_logs_days

system variable to expire binary log files automatically after a given number of days (see Section 5.2.3, “System Variables”). If you are using replication, you should set the variable no lower than the maximum number of days your slaves might lag behind the master.

Tags: , ,

Leave a Reply

Your email address will not be published.

*