mysql binlog找回数据
精选
转载
- 转自http://blog.chinaunix.net/uid-24086995-id-3218270.html
-
- #开发用工具操作数据库，误删除了一个表，时间大概在2012-05-18，删除表数据后，自增字段从1开始了，判断工具是做了truncate操作，到binlog里面搜
- -bash-3.2$ mysqlbinlog mysql-bin.000423 -vv |grep watch_list|grep truncate
- truncate table `watch_lists`
- #找到确实存是做了truncate操作。把BINLOG导出，找到操作的准确时间点
- -bash-3.2$ mysqlbinlog mysql-bin.000423 -vv --result-file=/home/bruce/watch_list.txt
- #120518 21:11:03 server id 21528 end_log_pos 273662669 Query thread_id=56426579 exec_time=1 error_code=0
- SET TIMESTAMP=1337393463/*!*/;
- truncate table `watch_lists`
- /*!*/;
- # at 273662669
- #120518 21:11:03 server id 21528 end_log_pos 273662696 Xid = 15287740170
- COMMIT/*!*/;
- #时间点在2012-05-18 21:11:03
- #需要找回的数据在备份时间点T1到这个时间点T2之间的数据。备份时间在2012-05-18 12:30:00
- #把这段时间的操作记录取出
- -bash-3.2$ mysqlbinlog mysql-bin.000423 -vv --start-datetime='2012-05-18 12:30:00' --stop-datetime='2012-05-18 21:11:03' --result-file=/home/bruce/watch_list.txt
- #取出SQL语句
- -bash-3.2$ grep -i watch_lists watch_list.txt
- INSERT INTO `watch_lists` (user_id,watch_@@@@,watch_@@@@,watch_@@@@,watch_@@@@,added_@@@@) VALUES ('13946','2','569226','@@@@','1','20120518185639')
- INSERT INTO `watch_lists` (user_id,watch_@@@@,watch_@@@@,watch_@@@@,watch_@@@@,added_@@@@) VALUES ('2147','2','2841877','@@@@','1','20120518185639')
