标题：用bin日志中恢复MySQL数据库
出处：BIWEB开源PHP WMS系统创始人ArthurXF肖飞的blog
时间：Mon, 18 Apr 2011 16:57:14 +0000
作者：ArthurXF
地址：http://www.bizeway.net/read.php/588.htm

内容：
如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog：用于处理二进制日志文件的实用工具”。

要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为–log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

SHOW BINLOG EVENTS G

你还可以从命令行输入下面的内容：

mysql –user=root -pmy_pwd -e ‘SHOW BINLOG EVENTS G’

将密码my_pwd替换为服务器的root密码。

1. 指定恢复时间

对于MySQL 4.1.4，可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2006年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

该命令将恢复截止到在–stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog –start-date=”2005-04-20 10:01:00″ /var/log/mysql/bin.123456

| mysql -u root -pmypwd

在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2. 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

mysqlbinlog –start-date=”2005-04-20 9:55:00″ –stop-date=”2005-04-20 10:05:00″

/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456

mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间
 

3.指定数据库名
如果一台服务器上有多个库，指定数据库名将不会干扰其他的数据库。格式如下：
mysqlbinlog  --database="5217u" --start-date="2012-04-01 00:00:00" --stop-date="2012-05-01 08:00:00"  mysql-bin.000088 > restore.sql

切记database是库名不是表名。

4.整理干净的sql文档
虽然我们可以通过上面种种条件指定恢复相应的数据，不过mysalbinlog生成的sql文件会有很多其他的代码。造成sql文件过大，打开缓慢甚至打不开。
我这里给出下列vi命令，来精简sql文件。

删除所有#号开头的行
:%s/^#.*//g
删除所有SET开头的行
:%s/^SET.*//g
删除所有BEGIN的行
:%s/^BEGIN//g
删除所有COMMIT的行
:%s/^COMMIT//g
删除所有的/*!*/;的行
:%s/\/\*\!\*\/;//g
删除/*!..*/的行
:%s/\/\*\!.*//g
删除空行
:g/^$/d

下面是我清除问题数据留底的，读者不能直接使用，不过可以借鉴里面的，跨行多行删除的正则表达式写法
注意：跨行多行的写法只支持vim,vi是不支持的
:%s/^REPLACE INTO biweb_d\_.\{-})$//g
:%s/^REPLACE INTO biweb_u\_.\{-})$//g
:%s/^REPLACE INTO biweb_m\_.\{-})$//g
:%s/^REPLACE INTO biweb_n\_.\{-})$//g
:%s/^REPLACE INTO biweb_a\_.\{-})$//g
:%s/^REPLACE INTO biweb_f\_.\{-})$//g
:%s/^REPLACE INTO biweb_task\_.\{-})$//g
:%s/^UPDATE biweb_d\_.\{-}'$//g
:%s/^UPDATE biweb_a\_.\{-}'$//g
:%s/^UPDATE biweb_u\_.\{-}'$//g
:%s/^UPDATE biweb_m\_.\{-}'$//g
:%s/^DELETE FROM biweb_m.*//g
:%s/^DELETE FROM biweb_d.*//g
:%s/^DELETE FROM biweb_a.*//g
:%s/^UPDATE biweb_task.*//g
:%s/^UPDATE biweb_n\_.\{-})$//g
:%s/^UPDATE biweb_n.*//g
:%s/^ALTER TABLE `biweb_a.*//g
:%s/^UPDATE `5217u`.`biweb_a.*//g
:%s/^UPDATE `5217u`.`biweb_u.*//g
:%s/^UPDATE `5217u`.`biweb_m.*//g
:g/^$/d




Generated by Bo-blog 2.0.3 sp1