1. 根据误操作时间定位binlog位置
找到数据库的binlog存放位置,当前正在使用的binlog文件里面就有我们要恢复的数据。一般生产环境中的binlog文件都是几百M乃至上G的大小,我们不能逐行去找被删除的数据在什么位置,所以记住误操作的时间很重要,我们可以通过mysqlbinlog命令的--start-datetime参数快速定位数据位置。比如误操作时间为20181104151800,解析出的binlog内容:
[root@cos7-jiang mysql]# mysqlbinlog -vv --start-datetime='2018-11-04 15:18:00' on.000004|more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#700101 8:00:00 server id 1 end_log_pos 120 CRC32 0x199f2da4 Start: binlog v 4, server v 5.6.42-log created 700101 8:00:
00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaQt
nxk=
'/*!*/;
# at 120
#181104 15:18:37 server id 1 end_log_pos 192 CRC32 0x2224f8de Query thread_id=16 exec_time=0 error_code=0
SET TIMESTAMP=1541315917/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#181104 15:18:37 server id 1 end_log_pos 252 CRC32 0x65fbbe3b Table_map: `test`.`person` mapped to number 85
# at 252
#181104 15:18:37 server id 1 end_log_pos 435 CRC32 0x005d1b39 Delete_rows: table id 85 flags: STMT_END_F
BINLOG '
TZ3eWxMBAAAAPAAAAPwAAAAAAFUAAAAAAAEABHRlc3QABnBlcnNvbgAGAw/+DwMDBgoA/gEKAD47
vvtl
TZ3eWyABAAAAtwAAALMBAAAAAFUAAAAAAAEAAgAG/8ABAAAAAmppATEDd2VyqEA0AGpqyADAAgAA
AANsdW8BMQN3ZXKcQjQAJ2zZAMADAAAAAnl1ATAEamVndfombwA3JyEBwAQAAAACZGEBMAdiZWlq
aW5nOgZQAFfZQADABQAAAAJnagEwB2JlaWppbmcSBlAAh+jOAMAGAAAABHlhbmcBMAduYW5qaW5n
cg5MAIdSqwo5G10A
'/*!*/;
### DELETE FROM `test`.`person`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='ji' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='wer' /* VARSTRING(10