MySQL的delete误操作的快速恢复方法

本次测试的数据量很小，如果一张表有数十万行数据被误删除，我们通过上面的方式找位置点是很费时间的。这时可以通过下面的两个命令锁定起始和终止位置：
确定起始位置点：

mysqlbinlog -vv --start-datetime=‘2018-11-04 15:18:00‘ on.000004| head -1000 |more

　　确定终止位置点：

mysqlbinlog -vv --start-datetime=‘2018-11-04 15:18:00‘ --stop-datetime=‘2018-11-04 15:20:00‘ on.000004| tail -1000 |more

　　我们只看binlog输出的前N行和后N行，来找到起始和终止位置，这样可以大大节省时间。

三、将binlog里的delete语句转化为insert语句
Binlog是二进制文件，我们可以先把待恢复数据导出为可阅读文本：

mysqlbinlog -vv --start-position=192 --stop-position=435 on.000004 |grep ^"###" >/tmp/bin_data

接下来就是处理/tmp/bin_data文本，将里面的delete语句转化为insert语句，可以通过下面的语句实现转化：

cat /tmp/bin_data | sed -n ‘/###/p‘ | sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;‘ |sed -r ‘s/(@6.*),/\1;/g‘ | sed ‘s/@[1-9]=//g‘ | sed ‘s/@[1-9][0-9]=//g‘ >/tmp/person.sql

四、将insert语句导入数据库中
Delete语句误操作只会删除表数据，而表结构还在。所以我们可以直接将文本里的insert语句导入到数据库中，即可完成数据恢复：

mysql -h127.0.0.1 -P3306 -uroot -p123 < /tmp/person.sql

补充和总结：
1、以上操作只针对delete误操作有效，且binlog模式是行模式；如果是drop或者truncate语句造成的误操作，亦或者binlog不是row模式，在binlog文件里是找不到完整的被删除数据，这个时候可以考虑通过备份进行恢复；
2、如果在误操作很久之后才意识到数据被误删除，记不清误操作的大致时间，那么可以找到误操作所在的binlog文件，将binlog解析为可阅读文本形式，然后借助文本编辑命令找误操作位置，再恢复；如果这种方式很慢的话，可以考虑通过备份恢复，或者从别的环境中导出这张表的数据再导入到当前环境中；
3、Mysqlbinlog命令重要参数
-vv 将二进制转换为可阅读文本
--start-datetime 起始时间
--stop-datetime 终止时间
--start-position 起始位置
--stop-position 终止位置
--base64-output=decode-row 查看最底层DML语句数据模块，前提是数据库参数binlog_rows_query_log_events打开
4、熟悉linux的文本编辑命令，如grep、sed、awk等
5、虽然binlog2sql工具也可以快速解析binlog，生成回滚SQL，但只要清楚上面的命令和操作流程，恢复速度也不会比binlog2sql慢很多，更何况如果环境没有安装这个工具。

1. [root@cos7-jiang mysql]# mysqlbinlog -vv --start-datetime=‘2018-11-04 15:18:00‘ on.000004|more
2.   /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
3.   /*!40019 SET @@session.max_insert_delayed_threads=0*/;
4.   /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
5.   DELIMITER /*!*/;
6.   # at 4
7.   #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:
8.   00
9.   # Warning: this binlog is either in use or was not closed properly.
10.   BINLOG ‘
11.   AAAAAA8BAAAAdAAAAHgAAAABAAQANS42LjQyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
12.   AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaQt
13.   nxk=
14.   ‘/*!*/;
15.   # at 120
16.   #181104 15:18:37 server id 1 end_log_pos 192 CRC32 0x2224f8de Query thread_id=16 exec_time=0 error_code=0
17.   SET TIMESTAMP=1541315917/*!*/;
18.   SET @@session.pseudo_thread_id=16/*!*/;
19.   SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
20.   SET @@session.sql_mode=1075838976/*!*/;
21.   SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
22.   /*!\C latin1 *//*!*/;
23.   SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
24.   SET @@session.lc_time_names=0/*!*/;
25.   SET @@session.collation_database=DEFAULT/*!*/;
26.   BEGIN
27.   /*!*/;
28.   # at 192
29.   #181104 15:18:37 server id 1 end_log_pos 252 CRC32 0x65fbbe3b Table_map: `test`.`person` mapped to number 85
30.   # at 252
31.   #181104 15:18:37 server id 1 end_log_pos 435 CRC32 0x005d1b39 Delete_rows: table id 85 flags: STMT_END_F
32.    
33.   BINLOG ‘
34.   TZ3eWxMBAAAAPAAAAPwAAAAAAFUAAAAAAAEABHRlc3QABnBlcnNvbgAGAw/+DwMDBgoA/gEKAD47
35.   vvtl
36.   TZ3eWyABAAAAtwAAALMBAAAAAFUAAAAAAAEAAgAG/8ABAAAAAmppATEDd2VyqEA0AGpqyADAAgAA
37.   AANsdW8BMQN3ZXKcQjQAJ2zZAMADAAAAAnl1ATAEamVndfombwA3JyEBwAQAAAACZGEBMAdiZWlq
38.   aW5nOgZQAFfZQADABQAAAAJnagEwB2JlaWppbmcSBlAAh+jOAMAGAAAABHlhbmcBMAduYW5qaW5n
39.   cg5MAIdSqwo5G10A
40.   ‘/*!*/;
41.   ### DELETE FROM `test`.`person`
42.   ### WHERE
43.   ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
44.   ### @2=‘ji‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
45.   ### @3=‘1‘ /* STRING(1) meta=65025 nullable=1 is_null=0 */
46.   ### @4=‘wer‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
47.   ### @5=3424424 /* INT meta=0 nullable=1 is_null=0 */
48.   ### @6=13134442 /* INT meta=0 nullable=1 is_null=0 */
49.   ### DELETE FROM `test`.`person`
50.   ### WHERE
51.   ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
52.   ### @2=‘luo‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
53.   ### @3=‘1‘ /* STRING(1) meta=65025 nullable=1 is_null=0 */
54.   ### @4=‘wer‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
55.   ### @5=3424924 /* INT meta=0 nullable=1 is_null=0 */
56.   ### @6=14248999 /* INT meta=0 nullable=1 is_null=0 */
57.   ### DELETE FROM `test`.`person`
58.   ### WHERE
59.   ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
60.   ### @2=‘yu‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
61.   ### @3=‘0‘ /* STRING(1) meta=65025 nullable=1 is_null=0 */
62.   ### @4=‘jegu‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
63.   ### @5=7284474 /* INT meta=0 nullable=1 is_null=0 */
64.   ### @6=18949943 /* INT meta=0 nullable=1 is_null=0 */
65.   ### DELETE FROM `test`.`person`
66.   ### WHERE
67.   ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
68.   ### @2=‘da‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
69.   ### @3=‘0‘ /* STRING(1) meta=65025 nullable=1 is_null=0 */
70.   ### @4=‘beijing‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
71.   ### @5=5244474 /* INT meta=0 nullable=1 is_null=0 */
72.   ### @6=4249943 /* INT meta=0 nullable=1 is_null=0 */
73.   ### DELETE FROM `test`.`person`
74.   ### WHERE
75.   ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
76.   ### @2=‘gj‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
77.   ### @3=‘0‘ /* STRING(1) meta=65025 nullable=1 is_null=0 */
78.   ### @4=‘beijing‘ /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
79.   ### @5=5244434 /* INT meta=0 nullable=1 is_null=0 */
80.   ### @6=13559943 /* INT meta=0 nullable=1 is_null=0 */
81.   ### DELETE FROM `test`.`person`
82.   ### WHERE
83.   ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
84.   ### @2=‘yang‘

    • < 上一篇

      MySQL必知必会-官方数据库表及SQL脚本导入生成

    • 下一篇 >

      SQL语句--查找数据select