MySQL 大量日志的清理方法

我不是女神ヾ 2022-06-18 11:08 317阅读 0赞

MySQL server运行有1年多时间，最近无法正常运行，启动也失败，后来检查了一下日志，发现没有空间写日志，只好找清理日志的方法，本来想直接删除那些日志，可是仔细一想，这些日志都是严格编号的，万一删除一个，导致mysql无法运行岂不误事。在网上search一下发现很多人都有同样的问题，现在把这些解决方便抄录过了，供大家参考。

我最后执行如下命令，就是只保留最近3天的日志，

**mysql>   PURGE MASTER LOGS BEFORE DATE\_SUB( NOW( ), INTERVAL 3 DAY);**

还有个问题就是我吧innodb这个engine添加到我的mysql中了（

14.7. 插入存储引擎

能够使用存储引擎之前，必须使用 INSTALL PLUGIN语句将存储引擎 plugin（插件）装载到 mysql。例如，要想加载 **example**引擎，首先应加载 ha\_example.so模块：

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

文件.so必须位于MySQL服务器库目录下（典型情况下是installdir/lib）。

）

**其他blog的解决办法**

binlog就有40多G，原来根源出在这里，查看了一下my.cnf，看到binlog的size是1G就做分割，但没有看到删除的配置，在mysql里show了一下variables，  
        mysql> show variables like '%log%';  
查到了  
| expire\_logs\_days                 | 0                                      |  
这个默认是0，也就是logs不过期，这个是一个global的参数，所以需要执行  
  
set global expire\_logs\_days=8;  
这样8天前的log就会被删除了，如果有回复的需要，请做好备份工作，但这样设置还不行，下次重启mysql了，配置又恢复默认了，所以需在my.cnf中设置  
  
expire\_logs\_days = 8  
这样重启也不怕了，

另外还有一种手动的删除binlog的方法，转自 [http://www.ourmysql.com/archives/293][http_www.ourmysql.com_archives_293]  
想要恢愎数据库以前的资料,执行:show binlog events;  
由于数据量很多，查看起来很麻烦，光打开个文件就要闪半天，所以应该适当删除部分可不用的日志。  
并且如果使用的时间足够长的话，会把我的硬盘空间都给吃掉  
1.登录系统，/usr/bin/mysql  
使用mysql查看日志  
mysql> show binary logs;  
\+—————-+———–+  
| Log\_name        | File\_size |  
\+—————-+———–+  
| ablelee.000001 | 150462942 |  
| ablelee.000002 | 120332942 |  
| ablelee.000003 | 141462942 |  
\+—————-+———–+  
2.删除bin-log(删除ablelee.000003之前的而没有包含ablelee.000003)  
mysql> purge binary logs to ‘ablelee.000003′;  
Query OK, 0 rows affected (0.16 sec)  
3. 查询结果(现在只有一条记录了.)  
mysql> show binlog events\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
    Log\_name: ablelee.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)  
(ablelee.000001和ablelee.000002已被删除)  
mysql> show binary logs;  
\+—————-+———–+  
| Log\_name        | File\_size |  
\+—————-+———–+  
| ablelee.000003 |        106 |  
\+—————-+———–+  
1 row in set (0.00 sec)  
(删除的其它格式运用!)  
　 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. 清理所有的日志，但是不包括目标日志。  
  
下面讲一下怎么从二进制文件恢复数据, 假如不小心执行了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"**

[http_www.ourmysql.com_archives_293]: http://www.ourmysql.com/archives/293