实际工作中总会发生数据误删除的场景，在没有备份情况下，如何快速恢复误删数据就显得非常重要。
本文基于MySQL的binlog日志机制，当日志格式设置为“binlog_format=ROW”时，记录一步一步手动解析binlog、恢复误删数据的全过程，供大家参考使用。
大致的思路是：通过命令找到删除操作对应的 binlog 详细信息，可通过 postion 或者时间的方式来检索查询，查到相对应的 DELETE 语句，通过 sed 将 DELETE 命令转换成 INSERT 的命令，然后提取出来执行完成恢复。当然这个需要数据库设置参数 binlog_format 支持，只有这样才会记录 delete 时每一条删除记录的详细内容。
一、确定删除时间段
查看数据库日志名字，查看操作事件，确认删除的具体时间，以定位删除的日志内容。
show master logs;
show binlog events in 'binlog.000067';
show binlog events in 'binlog.000067' FROM 1293303 LIMIT 0, 20;
通过类似如上的语句可确定具体的日志名字，具体的操作时间点或position
mysql> show binlog events in 'binlog.000002' FROM 2853160 LIMIT 0, 20;
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+
| binlog.000002 | 2853160 | Query | 1 | 2853246 | BEGIN |
| binlog.000002 | 2853246 | Table_map | 1 | 2853315 | table_id: 94 (liking.wp_options) |
| binlog.000002 | 2853315 | Update_rows | 1 | 2859755 | table_id: 94 flags: STMT_END_F |
| binlog.000002 | 2859755 | Query | 1 | 2859842 | COMMIT |
| binlog.000002 | 2859842 | Anonymous_Gtid | 1 | 2859921 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2859921 | Query | 1 | 2860007 | BEGIN |
| binlog.000002 | 2860007 | Table_map | 1 | 2860076 | table_id: 94 (liking.wp_options) |
| binlog.000002 | 2860076 | Update_rows | 1 | 2866516 | table_id: 94 flags: STMT_END_F |
| binlog.000002 | 2866516 | Query | 1 | 2866603 | COMMIT |
| binlog.000002 | 2866603 | Anonymous_Gtid | 1 | 2866682 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2866682 | Query | 1 | 2866759 | BEGIN |
| binlog.000002 | 2866759 | Table_map | 1 | 2866828 | table_id: 94 (liking.wp_options) |
| binlog.000002 | 2866828 | Delete_rows | 1 | 2866936 | table_id: 94 flags: STMT_END_F |
| binlog.000002 | 2866936 | Query | 1 | 2867014 | COMMIT |
| binlog.000002 | 2867014 | Anonymous_Gtid | 1 | 2867093 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 2867093 | Query | 1 | 2867179 | BEGIN |
| binlog.000002 | 2867179 | Table_map | 1 | 2867247 | table_id: 95 (liking.wp_postmeta) |
| binlog.000002 | 2867247 | Update_rows | 1 | 2867347 | table_id: 95 flags: STMT_END_F |
| binlog.000002 | 2867347 | Query | 1 | 2867434 | COMMIT |
| binlog.000002 | 2867434 | Anonymous_Gtid | 1 | 2867513 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+---------------+---------+----------------+-----------+-------------+--------------------------------------+二、取到指定时间段的所有sql语句
mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-datetime="2022-10-27 16:54:00" --stop-datetime="2022-10-27 18:27:00" binlog.000288 > liking.sql
三、取delete语句
sed -n '/### DELETE FROM 数据脱敏.数据脱敏数据脱敏/,/COMMIT/p' liking.sql > liking-1.sql
得到类似如下结果：
head -50 liking-1.sql
### DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
### WHERE
### @1=222248 /* INT meta=0 nullable=0 is_null=0 */
### @2='数据脱敏数据脱敏' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[此处省略第3~45个记录行]
### @46='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @47='订单' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @48='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */四、只保留以###开始的行数据
sed -n '/^###/'p liking-1.sql > liking-2.sql
五、去掉开头的###字符
sed 's/### //g' liking-2.sql > liking-3.sql
得到类似如下结果：
head -50 liking-3.sql
DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
WHERE
@1=222248 /* INT meta=0 nullable=0 is_null=0 */
@2='数据脱敏数据脱敏' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
[此处省略第3个~第45个字段]
@46='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@47='订单' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
@48='数据脱敏' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */六、以#作分隔符将以/*开始的内容用,替换
sed 's#/*.*#,#g' liking-3.sql > liking-4.sql
得到如下结果：
head -50 liking-4.sql
DELETE FROM `数据脱敏`.`数据脱敏数据脱敏`
WHERE
@1=222248 ,
@2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
@46='数据脱敏' ,
@47='订单' ,
@48='数据脱敏' ,七、浏览结果做对应处理
鉴于还有一些insert、update语句，只取出前面的delete语句：
sed -n '1,493200p' liking-4.sql > liking-5.sql
八、全文替换关键字，将delete改成insert
将'DELETE FROM'改成'INSERT INTO'
将'WHERE'改成'SELECT'
sed 's#DELETE FROM#INSERT INTO#g' liking-5.sql > liking-6.sql
sed 's#WHERE#SELECT#g' liking-6.sql > liking-7.sql
得到如下结果：
head -50 liking-7.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏数据脱敏`
SELECT
@1=222248 ,
@2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
@46='数据脱敏' ,
@47='订单' ,
@48='数据脱敏' ,九、匹配'@48='开头以','结尾的行，把','替换成';'
sed -r 's#(@48=.)(,)#\1;#g' liking-7.sql > liking-8.sql
-r: 是指将正则表达式中的需要转义的字符设置成不需转义
括号用来匹配连续出现的内容
.标识匹配除换行符\n外的任何单元符
为了把','去掉，单独(,)
\1是用来取()表示符的第一个，并在后面拼接上';'
本次恢复的记录都是48个字段，最后加分号，标识SQL结束，比较容易理解。
得到类似如下结果：
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏数据脱敏`
SELECT
@1=222248 ,
@2='数据脱敏数据脱敏' ,
[此处省略第3个~第45个字段]
@46='数据脱敏' ,
@47='订单' ,
@48='数据脱敏' ;十、把'@??='替换成''，也就是去掉类似'@11='字样部分
sed -r 's#(@.=)(.)#\2#g' liking-8.sql > liking-9.sql
得到类似如下结果：
head -50 liking-9.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
222248 ,
'CMHE-202200393' ,
'数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
4.1772e+07 ,
'\'8000062202216124' ,
[此处省略第6个~第45个字段]
'数据脱敏' ,
'订单' ,
'马蕾' ;十一、最后拼接 commit 语句
sed -i '$a commit;' liking-9.sql
查看最后几行确认：
tail -51 liking-9.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
251837 ,
'CMHE-202101003' ,
'数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
5.04e+08 ,
'\'8000004202200006' ,
[此处省略第6个~第45个字段]
'数据脱敏' ,
'订单' ,
'陈鹏3' ;
commit;十二、浏览、调整个别异常格式字段
此时需大概浏览得到的文本结果，尤其关注转义符，如有必要，需及时调整。
发现尚存在类似如下字段格式
'\'8000004202200006'
如需去掉\'
则如下操作：
sed "s#\\'##g" liking-9.sql > liking-10.sql
最后确认如下：
tail -51 liking-10.sql
INSERT INTO `数据脱敏`.`数据脱敏数据脱敏`
SELECT
251837 ,
'CMHE-202101003' ,
'数据脱敏数据脱敏数据脱敏数据脱敏数据脱敏' ,
5.04e+08 ,
'8000004202200006' ,
[此处省略第6个~第45个字段]
'数据脱敏' ,
'订单' ,
'陈鹏3' ;
commit;十三、执行恢复数据
经过审视、调整后，此时得到的结果，已经可以正常执行INSERT操作恢复数据了！
写在最后，再次强调，备份是运维第一要务，备份的重要性再怎么强调也不为过，除非你可以承受数据丢失带来的后果。
数据千万行，备份最重要；数据不备份，丢失泪两行！
参考文档：
https://blog.csdn.net/qq_37701372/article/details/115188461
文章评论