usr bin mysql被删除_mysql通过bin-log日志恢复误删除数据

今天一同事误删除了一个会员，导致会员个人信息及会员的商品等相关信息均被删除，捣鼓两小时，感觉挺好！特记录之！

环境：

操作系统：centos 6.4  LNMP

2014-4-11号删除之前数据，我想恢复2014-4-2号到2014-4-11号15：30之间的数据，

首先，用mysqlbinlog命令对二进制的日志文件解析成文本文件。如：

进入mysql-bin目录

[[email protected] var]#cd /usr/local/mysql/var

将近期的多个bin-log二进制日志生成txt文件供查找posid值

[[email protected] var]#/usr/local/mysql/bin/mysqlbinlog mysql-bin.000019 > /mysql-bin.000019.txt

找到两个时间点之间的posid ，如下方的at后的值

BEGIN

/*!*/;

# at 2871466

#140411 15:13:58 server id 1  end_log_pos 2871608 Querythread_id=13378exec_time=0error_code=0

SET TIMESTAMP=1397200438/*!*/;

DELETE FROM `wuxichuan_lrgd`.`ecs_sessions` WHERE expiry < 1397198638

/*!*/;

# at 2871608

#140411 15:13:58 server id 1  end_log_pos 2871687 Querythread_id=13378exec_time=0error_code=0

SET TIMESTAMP=1397200438/*!*/;

COMMIT

通过posid 恢复数据：

[[email protected] var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000019 --start-pos=4768240 --stop-pos=4772798 | mysql -f -uroot -ppassword

--no-defaults   和 mysql -f 命令强制跳过错误提示！

ERROR 1062 (23000) at line 1282948: Duplicate entry ‘353‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1296933: Duplicate entry ‘1600‘ for key ‘PRIMARY‘

[[email protected] var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000018 --start-pos=4 --stop-pos=85648 | mysql -f -uroot -pzgzszj@#2013

ERROR 1062 (23000) at line 47: Duplicate entry ‘495‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 709: Duplicate entry ‘4619‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 972: Duplicate entry ‘4620‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1005: Duplicate entry ‘4621‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1169: Duplicate entry ‘4622‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1202: Duplicate entry ‘4623‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1267: Duplicate entry ‘4624‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1340: Duplicate entry ‘4625‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1448: Duplicate entry ‘4626‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1517: Duplicate entry ‘4627‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 1996: Duplicate entry ‘3170‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 2011: Duplicate entry ‘3170‘ for key ‘PRIMARY‘

ERROR 1062 (23000) at line 2078: Duplicate entry ‘9826‘ for key ‘PRIMARY‘

出现这些提示，说明成功，congratunations！！！！！！！！！

原文：http://myitlife.blog.51cto.com/2289784/1394349