mysql执⾏delete操作后如何恢复_MySQL的delete误操作的快 速恢复⽅法
到数据库的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,@@llation_connection=8,@@llation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@llation_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) meta=10 nullable=1 is_null=0 */
### @5=3424424 /* INT meta=0 nullable=1 is_null=0 */
光学玻璃加工设备
### @6=13134442 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='luo' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='1' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='wer' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=3424924 /* INT meta=0 nullable=1 is_null=0 */
### @6=14248999 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='yu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='jegu' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
排火### @5=7284474 /* INT meta=0 nullable=1 is_null=0 */
### @6=18949943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='da' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=5244474 /* INT meta=0 nullable=1 is_null=0 */
### @6=4249943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='gj' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='beijing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=5244434 /* INT meta=0 nullable=1 is_null=0 */
### @6=13559943 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`person`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='yang' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @3='0' /* STRING(1) meta=65025 nullable=1 is_null=0 */
### @4='nanjing' /* VARSTRING(10) meta=10 nullable=1 is_null=0 */
### @5=4984434 /* INT meta=0 nullable=1 is_null=0 */
### @6=178999943 /* INT meta=0 nullable=1 is_null=0 */
# at 435
#181104 15:18:37 server id 1 end_log_pos 466 CRC32 0xab85d971 Xid = 197
COMMIT/*!*/;
# at 466
#181104 15:19:38 server id 1 end_log_pos 538 CRC32 0x8b9e1093 Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1541315978/*!*/;
BEGIN
/*!*/;
#181104 15:19:38 server id 1 end_log_pos 598 CRC32 0xb1679f78 Table_map: `test`.`person` mapped to number 85
# at 598
#181104 15:19:38 server id 1 end_log_pos 656 CRC32 0xa5d7a2d6 Write_rows: table id 85 flags: STMT_END_F
通过上⾯的命令可以⽐较⽅便地逐页寻被删除的数据，我们应该要到被删除的数据在binlog中的起始和终⽌位置点，例如上⾯的被删除数据的位置点在192和435之间，这样我们可以保证在这两个位置之间只有我们需要的待恢复的数据，⽽没有其他数据。
本次测试的数据量很⼩，如果⼀张表有数⼗万⾏数据被误删除，我们通过上⾯的⽅式位置点是很费时间的。这时可以通过下⾯的两个命令锁定起始和终⽌位置：确定起始位置点：
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慢很多，更何况如果环境没有安装这个⼯具。
[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:
pcb清洗剂
00
# Warning: this binlog is either in use or was not closed properly.
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,@@llation_connection=8,@@llation_server=8/*!*/;直播app推广
SET @@session.lc_time_names=0/*!*/;
SET @@llation_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 */
豫公网安备41160202000603
站长QQ:729038198
关于我们
投诉建议