今天一同事误删除了一个会员,导致会员个人信息及会员的商品等相关信息均被删除,捣鼓两小时,感觉挺好!特记录之!
环境:
操作系统:centos 6.4 LNMP
2014-4-11号删除之前数据,我想恢复2014-4-2号到2014-4-11号15:30之间的数据,
首先,用mysqlbinlog命令对二进制的日志文件解析成文本文件。如:
进入mysql-bin目录
[root@localhost var]#cd /usr/local/mysql/var
将近期的多个bin-log二进制日志生成txt文件供查找posid值
[root@localhost 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 恢复数据:
[root@localhost 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'
[root@localhost 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!!!!!!!!!
转载于:https://blog.51cto.com/myitlife/1394349