【mysql回滚操作】delete操作误删数据库记录后根据binlog恢复数据

MySQL【Delete误操作】使用binlog快速回滚（无须python脚本）

根据误操作时间定位binlog位置

在具体的使用场景中，binlog可能存了几十万甚至更多条数据，数据量达到上G的大小，因此确定binlog的位置需要根据误操作时间来进行筛选，因此最好能记住误操作的时间，否则查找定位binlog是一件费时费力的事。使用mysqlbinlog命令，通过–start-datetime及–stop-datetime来筛选在此时间区间内的所有操作及操作对应binlog的position。
日志文件所在路径不同版本mysql可能不同，本人使用mysql8，binlog文件在/var/lib/mysql下。
进入文件所在目录下，执行如以下命令即可查询得到相关数据操作。

mysqlbinlog --no-defaults --database=xxx -vv binlog.000007 --start-datetime="2022-02-19 8:06:34" --stop-datetime="2022-02-19 8:06:35"  | more

但如果在此过程中日志较多，通过上述命令确定position是很困难的，可以结合 “| head -1000” 以及 “| tail -1000” 进行过滤，只看binlog输出的前N行或后N行，来找到起始和终止位置，这样可以大大节省时间。若关于误操作的时间等信息实在无记忆，定位误操作的过程可以使用多种方式来进行筛选过滤，如在命令中添加sed命令来选择指定日志打印出来， “| sed -n ‘/### DELETE FROM `test`.`tet3`/,/COMMIT/p’”，打印从“### DELETE FROM `test`.`tet3`”开始到“COMMIT”结束的内容，类似方法可自行查找进行尝试。

mysqlbinlog --no-defaults --database=xxx -vv binlog.000007 --start-datetime="2022-02-19 8:06:34" --stop-datetime="2022-02-19 8:06:35" |tail -300   | more

提取误操作日志

通过查询binlog里形如 “at 123” 的日志即可确认position，确认删除操作的BEGIN及COMMIT的position即可知道整个删除操作的起始和结束position，根据两个位置点提取其中的DELETE操作日志，格式如下所示：

### DELETE FROM `test`.`me_info`
### WHERE
###   @1=2165974 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1984:03:17' /* DATE meta=0 nullable=1 is_null=0 */
###   @3=NULL /* DATE meta=765 nullable=1 is_null=1 */
###   @4=2012-10-25 00:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
###   @5='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @6=0 /* TINYINT meta=0 nullable=1 is_null=0 */
###   @7='' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
###   @9=0 /* MEDIUMINT meta=0 nullable=1 is_null=0 */

提取所有delete操作的日志输出到文件：

mysqlbinlog --no-defaults -vv binlog.000007 --start-position=10147088 --stop-position=109132975 | grep ^"###" >data_reload

将binlog里的delete语句转化为insert语句

delete语句为delete from `dbname`.`tablename` where @1=xxxx，@1之类表明第1个字段。
insert语句为insert into `dbname`.`tablename` values(value1, value2, ……)。
将delete语句转换为insert语句：

cat data_reload | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;' |sed -r 's/(@9.*),/\1);/g' | sed 's/@[1-9]=//g' >data_reload.sql

命令参数说明：

• sed -n ‘/###/p’：打印’###'开头的行。
• sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;’：
  s/### //g;s/\/\*.*/,/g; 把’### ’ 和/*…*/去掉； s/DELETE FROM/INSERT INTO/g; 把delete from换成insert into； s/WHERE/VALUES(/g; 把where换成values(。
• sed -r ‘s/(@9.*),/\1);/g’：-r是正则表达式，在@9开头的一行末尾添加一个 ); ，（此处为最后一个字段后），即补全了 values(v1, ……, v9); 。
• sed ‘s/@[1-9]=//g’：将@1-@9去除。

注：具体情况具体分析，根据不同数据库表结构来执行本条命令。

执行insert语句脚本将数据导入数据库

mysql -uroot -p < data_reload.sql