MySQL误操作数据恢复之误删行

一、前言

对于MySQL日常操作中难免会有对行误操作的时候，通常误操作后，又想尽快的恢复，首先我们想到就是闪回。对于闪回工具有比较多的工具可以实现，如binlog2sql、MyFlash、my2fback、my2sql等。

本次介绍的主要为my2sql的闪回功能，以做到恢复数据的操作。

my2sql是go版MySQL binlog解析工具，通过解析MySQL binlog ，可以生成原始SQL、回滚SQL、去除主键的INSERT SQL等，也可以生成DML统计信息。类似工具有binlog2sql、MyFlash、my2fback等，本工具基于my2fback、binlog_rollback工具二次开发而来。

二、数据恢复演示

set1:安装软件

下载二进制文件：https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

set2:模拟数据

--表结构test@3306 16:49:  [db1]> show create table sbtest1\G*************************** 1. row ***************************       Table: sbtest1Create Table: CREATE TABLE `sbtest1` (  `id` int NOT NULL AUTO_INCREMENT,  `k` int NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  KEY `k_1` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.85 sec)
--数据量大小test@3306 16:48:  [db1]> select count(*) from sbtest1;+----------+| count(*) |+----------+|   100000 |+----------+1 row in set (9.47 sec)
--表的校验值test@3306 16:51:  [db1]> checksum table db1.sbtest1;+-------------+-----------+| Table       | Checksum  |+-------------+-----------+| db1.sbtest1 | 703476980 |+-------------+-----------+1 row in set (1.85 sec)

set3:模拟数据误删

--删除5000行数据test@3306 16:54:  [db1]> delete from sbtest1 where id<=5000;Query OK, 5000 rows affected (1.76 sec)
test@3306 16:55:  [db1]> select count(*) from sbtest1;+----------+| count(*) |+----------+|    95000 |+----------+1 row in set (1.85 sec)

set4:找误操作的时间点进行恢复

set5:使用my2sql回滚数据SQL

./my2sql -user test -password test -host 127.0.0.1   -port 3306  -mode file -local-binlog-file /root/mysql-sandboxes/5003/sandboxdata/testdb02-bin.000013 -work-type rollback  -start-file /root/mysql-sandboxes/5003/sandboxdata/testdb02-bin.000013  -start-datetime "2022-08-15 16:55:00"  -output-dir ./tmpdir

set6:将回滚SQL导回原库验证

source ./tmpdir/rollback.13.sql

到此数据已恢复完毕。

三、总结

1、my2sql使用上仍有不少限制

• 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响

• 只能回滚DML， 不能回滚DDL

• 支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp

• 此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限

• MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析

2、my2sql仍有其它功能值得探索，如统计DML以及大事务

3、对于生产环境还需谨慎操作，避免误操作带来不必要的麻烦。

参考链接：https://github.com/liuhr/my2sql