先别跑路!MySQL执行delete误删除数据还有救

马听 2023-09-01 10:08:18
有时候我们可能因为手抖或者粗心,执行了一条delete语句,导致正常的业务数据被删除了。

 

别慌,也先别跑路。

 

有很多种方式可以恢复回来,这一篇文章就来聊聊使用my2sql工具解析出delete语句的回滚操作(也就是insert),来恢复delete误删除的数据。

 

my2sql工具Github地址:https://github.com/liuhr/my2sql

 

不过要想使用my2sql恢复数据,需要注意这些:

 

  • binlog格式必须为row,且binlog_row_image=full(表示记录的二进制日志将包含所有列的值,包括旧值和新值)

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

  • 8.0 用户密码认证必须是mysql_native_password才能解析

 

我们通过一个实验来模拟误操作,并进行恢复。

 

一、新建测试表和测试用户

 

创建测试表并写入数据:

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
create database d_recover;use d_recover;
CREATE TABLE del_t1 (id int NOT NULL AUTO_INCREMENT,a int NOT NULL,PRIMARY KEY (id)ENGINE=InnoDB CHARSET=utf8mb4;
insert into del_t1 values (1,1),(2,2);

 

创建恢复用户:

 

  •  
  •  
CREATE USER `u_rollback`@`127.0.0.1` IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'IgdI8G_aUU';GRANT SELECT, REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO `u_rollback`@`127.0.0.1`;

 

二、安装Binlog解析工具my2sql

 

下载my2sql:

 

  •  
  •  
  •  
mkdir /data/backup/rollback -pcd /data/backup/rollbackwget https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

 

三、模拟误操作

 

  •  
delete from d_recover.del_t1;

 

四、把误操作事务所在的Binlog复制到新目录

 

  •  
  •  
mkdir /data/backup/rollbackcp /data/mysql/binlog/mysql-bin.000012 /data/backup/rollback

 

五、查看误操作事务开始和结束的位点

 

解析误操作期间的Binlog,预估误操作大概的起止时间,范围可以稍微大一点。

 

  •  
  •  
cd /data/backup/rollbackmysqlbinlog mysql-bin.000012 --start-datetime='2023-06-21 01:20:00'  --stop-datetime='2023-06-21 01:40:00'  --base64-output=decode-rows -v  > operation.sql

 

找到误操作的Binlog,并确定开始和结束的位点:

 

图片

 

比如上面解析出的Binlog,误操作开始的位点可以认为是3556,结束的位点是3719

 

六、生成回滚SQL

 

  •  
  •  
/data/backup/rollback./my2sql -user u_rollback -password 'IgdI8G_aUU' -host 127.0.0.1 -databases d_recover -tables del_t1 -work-type rollback -start-file mysql-bin.000012 -start-pos 3556 -stop-pos 3719 -output-dir recover_01

 

查看并确认回滚SQL:

 

  •  
cat recover_01/rollback.12.sql

 

图片

 

七、导入回滚的SQL

 

如果确定回滚的SQL没问题,把回滚SQL导入到误操作的库里:

 

  •  
mysql -uroot -p 12.sql

 

再进行数据确认:

 

  •  
select *  from d_recover.del_t1;

 

如果一切顺利,那误删除的数据(1,1),(2,2)就已经恢复回来了。

 

当然,数据恢复还有其他的一些方式,比如:全备+Binlog、延迟从库(主库有误操作,从库同步到误操作前一个事务)、新建从库同步到误操作前一个事务等等,后面会找时间分享这些恢复方式。

 

作者丨马听
来源丨公众号:MySQL数据库联盟(ID:gh_fbfc36d26181)
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节,那么长度值可能…

访客 2024年03月04日

只能说作者太用心了,优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年(即30岁左右),能做到年入40w-50w;有…

访客 2023年08月20日

230721

活动预告