Mysql日志恢复

参考http://de.cel.blog.163.com/blog/static/514512362012011103429896/

参考http://blog.163.com/zhao_jw/blog/static/180587366201110293630648/

grep -w:精确全词匹配 -B3：获取符合条件行的前三行

利用 MySql日志文件 恢复数据  

1. 以前我错误的认为mysql的日志可以恢复到任何时间的状态，其实并不是这样，这个恢复是有前提的，就是你至少得有一个从日志记录开始后的数据库备份，通过日志恢复数据库实际上只是一个对以前操作的回放过程而已，不用想得太复杂，既然是回放你就得注意了，如果你执行了两次恢复那么就相当于是回放了两次，后果如何你自己应该清楚了吧。 

2. 要想通过日志恢复数据库，在你的my.cnf文件里应该有如下的定义，log-bin=mysql-bin，这个是必须的.binlog-do-db=db_test,这个是指定哪些数据库需要日志，如果有多个数据库就每行一个，如果不指定的话默认就是所有数据库. 
[mysqld] 
log-bin=mysql-bin 
binlog-do-db=db_test 
binlog-do-db=db_test2 

3.删除二进制日志: 
a.mysql> system ls -ltr /var/lib/mysql/bintest*; 
mysql>reset master(清空所有的二进制日志文件) 
b.purge master logs to 'bintest.000006';(删除bintest.000006之前的二进制日志文件) 
c.purge master logs before '2007-08-10 04:07:00'(删除该日期之前的日志) 
d.在my.cnf 配置文件中[mysqld]中添加: 
expire_logs_day=3设置日志的过期天数,过了指定的天数,会自动删除 


4.下面就是恢复操作了 
特别提示，mysql每次启动都会重新生成一个类似mysql-bin.000003的文件，如果你的mysql每天都要重新启动一次的话，这时候你就要特别注意不要选错日志文件了。 

(注意：下面有一些技巧，这些东西才是最宝贵的哟，普通的东东手册上都有，这可是我摸索出来的哟，别人我都不告诉他。 

技巧1 : 
在下面你将看到

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd 

类似的语句，但是它一次只能操作一个日志文件，如果你变通一下变成这样 

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.0* | mysql -u root -pmypwd 

那么它基本上就会表示出的所有的日志文件了，这样可解决你忘记在哪一个日志文件中的问题，当然你也可以用这种写法更完美,

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -pmypwd ，

看到[0-9]*这个东东了吧，它表示以数字开头的任何字符，方便吧！ 

技巧2: 
你可以通过--one-database 参数选择性的恢复单个数据库，example在下面，爽吧。 

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test 

技巧3: 
如果你老人家已经使用过 /usr/local/mysql5/bin/mysqlbinlog --start-date="2005-04-20 9:55:00" /var/data/mysql5/mysql-bin.0* > /home/db/tt.sql 类似的语句将日志导成了ASCII文本文件，那么你就可以直接在phpmyadmin或者其它什么乱七糟八的的客户端里执行这个文件文件就行了，因为它本身就是一个标准的sql文件，比如想让文件里面的某些语句不执行，OK，it's easy,找到它们删除即可，然后再放进去执行就OK滴啦！这个可是灰常灰常的爽哟。。。。。。 

技巧4: 
我来给大家讲一下，下面这条语句都做了什么 

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test 

这是把mysql-bin.000001这个二进制文件里的内容转换成ASCII文件(也就是sql语句)，直接通过管道操作符"|"传输给 mysql这个程序，然后过滤掉其它数据库的语句，只在db_test里执行。 

技巧5: 
着了，多打了一个技巧，现在暂时没内容，等以后再加吧！！！ 
) 



下面部份摘录自网上。 


如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息，参见5.11.3节，“二进制日志”。对于 mysqlbinlog的详细信息，参见mysql手册8.6节，“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(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入： 


mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd 


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


mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/mysql-bin.000001 | 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/mysql-bin.000001 > /tmp/mysql_restore.sql 


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


mysqlbinlog --stop-position="368312" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd 
mysqlbinlog --start-position="368315" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd 


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

mysqlbinlog抽取某个表的信息  

The second step was to restore the data from the time of the backup (which was about 10 hours ago) up to the point of the crash. The binary log was already spread across two files at that time. So I had to extract all the data manipulating statements for the database holding the crashed table from those two binlog files to a text file.

mysqlbinlog --database=db_name --start-position=102655866 mysql1-bin.000312 > restore.sql
mysqlbinlog --database=db_name mysql1-bin.000313 >> restore.sql
    The start-position is of course the position of the binlog at the time of the backup. Now I could do a search for all statements affecting the crashed table and feed them to mysql again.
    grep -B3 -w table_name restore.sql | egrep -v '^--$' > restore_table.sql
    emacs restore_table.sql
    mysql db_name < restore_table.sql
    As I knew that all those statements didn't contain any newlines I used a simple approach with grep (the -B3 giving me the lines with the meta information just before the actual statement), quickly checked the resulting file in a text editor (where I deleted the ALTER TABLE statement, too, to not have the crash happen again) and ran the queries.
    That's it. The table was now in exactly the same state as it was before the crash.

 mysqlbinlog mysql-bin.012001>a.sql

grep -B3 -w tblauction a.sql >re.sql

就查找出了关于表tblauction的相关DML操作语句