用二进制日志恢复数据

jeff

关于二进制日志，可以看看： MySQL的各种日志 、MySQL binlog_format (Mixed,Statement,Row) 、MySQL binlog日志自动清理及手动删除 等。

这里讲一下怎么从二进制文件恢复数据, 假如不小心执行了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 |

另一个方法是：
shell> mysqlbinlog hostname-bin.000001 >  /tmp/statements.sql
shell> mysqlbinloghostname-bin.000002 >> /tmp/statements.sql
shell> mysql -e "source /tmp/statements.sql"


附几个其他的恢复条件：

根据数据库名来进行还原 -d

在这里是小写的d，请不要把它和mysqldump中的－D搞混了。哈哈。

[kider@mysqlpub.com]# /usr/local/mysql/bin/mysqlbinlog -d test  /var/lib/mysql/mysql-bin.000002|mysql -u root -p

根据条件看一下数据

[kider@mysqlpub.com]#/usr/local/mysql/bin/mysqlbinlog -d test  /var/lib/mysql/mysql-bin.000002
//下面是部分内容，其实也就是一些对数据进行操作的sql语句  #

根据数据库所在IP来分－h

[kider@mysqlpub.com]#/usr/local/mysql/bin/mysqlbinlog -h 192.1681.102  /var/lib/mysql/mysql-bin.000002

根据数据库所占用的端口来分－P

有的时候，我们的mysql用的不一定是3306端口，注意是大写的P

[kider@mysqlpub.com]#/usr/local/mysql/bin/mysqlbinlog -P 13306  /var/lib/mysql/mysql-bin.000002

根据数据库serverid来还原–server-id

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的。

[kider@mysqlpub.com]#/usr/local/mysql/bin/mysqlbinlog –server-id=1  /var/lib/mysql/mysql-bin.000002

注意：上面的几个例子，我都是一个一个说的，其实可以排列组合的。例如

[kider@mysqlpub.com]#/usr/local/mysql/bin/mysqlbinlog –start-position=”2010-09-29 18:00:00″ -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 ｜mysql -u root -p

参考：

http://www.linuxidc.com/Linux/2012-09/70815.htm

整理：mysqlpub.com , 转载请注明。

http://www.mysqlpub.com/thread-59635-1-1.html