删除MySQL二进制日志的3种方法

By admin

- One minute read - 172 words

1.RESET MASTER

****可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个新的二进制日志文件

2.PURGE MASTER LOGS

语法 PURGE {MASTER | BINARY} LOGS TO ‘log_name‘ PURGE {MASTER | BINARY} LOGS BEFORE ‘date‘ 用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

(1).用reset master命令删除所有日志，新日志重新从000001开始编号

(2).用purge master logs to ‘mysq-bin.**’ 命令可以删除指定编号前的所有日志

(3).用purge master logs to before ‘YYYY-MM-DD HH24:MI:SS’命令可以删除’YYYY-MM-DD HH24:MI:SS’之前的产生的所有日志

(4).可以在my.cnf中指定–expire_logs_days=#，此参数设置了binlog日志的过期天数

例如：

PURGE MASTER LOGS TO ‘mysql-bin.010’; PURGE MASTER LOGS BEFORE ‘2003-04-02 22:46:26’; BEFORE变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同义词。 注意: 如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。 要清理日志，需按照以下步骤：

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

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

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

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

  5. 清理所有的日志，但是不包括目标日志。下面讲一下怎么从二进制文件恢复数据, 假如不小心执行了drop table xxx_db, 假如你保留了完整的二进制日志的话, 先不要冒汗, 这是可以恢复的.先看看日志

mysqlbinlog /diskb/bin-logs/xxx_db-bin.000001

找到执行create table xxx_db之后和drop table xxx_db之前的position, 假如是20, 1000.

mysqlbinlog –start-position=”4″ –stop-position=”1000″ /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root

伴随着一大堆的ERROR 1062 (23000) at line 12355: Duplicate entry ‘139’ for key 1, 数据库就这样恢复了, 不过–start-position=”20″是不行的, 必须从–start-position=”4″开始, 为什么要强制从4开始, 这个问题我也暂时没有搞清楚.还有一种办法是根据日期来恢复

mysqlbinlog –start-datetime=”2009-09-14 0:20:00″ –stop-datetim=”2009-09-15 01:25:00″ /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root

如果create table xxx_db和drop table xxx_db之间的时间相距是一年, 或者在不同的二进制日志中, 且位置相距好远, 就等着失眠吧! 做好备份, 小心操作才是正路啊…如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们。下面是一个说明什么是 不安全 的例子：

shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!

shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!

使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个 mysql 进程结束时，服务器撤销临时表。当第2个 mysql 进程想使用该表时，服务器报告 “不知道该表”。

要想避免此类问题，使用一个连接来执行想要处理的所有二进制日志中的内容。下面提供了一种方法：

shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

另一个方法是：

shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql

shell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sql

shell> mysql -e "source /tmp/statements.sql"