7

mysql误删数据怎么恢复(2)drop/truncate误删恢复 -利来国际app

陈家睿 2021-11-24
6498

误删数据包含如下四种情况

(1)使用 delete 语句误删数据行;

(2)使用 drop table 或者 truncate table 语句误删数据表;

(3)使用 drop database 语句误删数据库;

(4)使用 rm 命令误删整个 mysql 实例;


前面已经说了第一种情况使用 delete 命令删除的数据,你还可以用 flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据,就没办法通过 flashback 来恢复了。

因为,即使我们配置了 binlog_format=row,执行这三个命令时,记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句,这些信息是恢复不出数据的。


误删库 / 表这种情况下,要想恢复数据,就需要使用全量备份,加增量日志的方式了。这个方案要求线上有定期的全量备份,并且实时备份 binlog。

恢复数据的流程如下:

1.取最近一次全量备份恢复出一个临时库;

2.从binlog备份里面取出这次备份时间点之后的日志;

3.把日志除了误删数据的语句外,全部应用到临时库;


模拟恢复,假设每周日全备一次,之后每天进行增量备份一次


全备(周日)

innobackupex -ucjr -pcjr -h1.15.57.253 -p3306 --no-timestamp /backup/full &>/backup/xbk_full.log


模拟周一数据变化

create database cs charset utf8;

use cs ;

create table t1 (id int);

insert into t1 values(1),(2),(3); 


第一次增量备份(周一)

innobackupex -ucjr -pcjr -h1.15.57.253 -p3306 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/backup/inc1.log


模拟周二数据

create table t2 (id int);insert into t2 values(1),(2),(3);


第二次增量备份(周二)

innobackupex -ucjr -pcjr -h1.15.57.253 -p3306 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/backup/inc2.log


模拟周三数据变化

create table t3 (id int);

insert into t3 values(1),(2),(3);

drop database cs; 误删数据库



恢复思路:

1. 检查备份:周日full 周一inc1 周二inc2,周三的完整二进制日志

2. 进行备份整理,截取关键的二进制日志(从备份——误删除之前,将误删除的gitd排除出去)

3. 备份恢复到一个临时库,再用binlog日志恢复


(1) 全备的整理
innobackupex --apply-log --redo-only /backup/full

(2) 合并inc1到full中

innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

(3) 合并inc2到full中

innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full

(4) 最后一次整理全备

innobackupex --apply-log /data/backup/full

--redo-only参数, 所有增量合并时(除了最后一次增量),防止lsn号对不上,因为--apply-log包含了前滚和回滚操作


定位到binlog的gtid位置

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000013 | awk 'begin{ignorecase=1} {if($0~/drop/)count[$1" " $2" " $3" "$nf] }end{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr

在mysql-bin.000013中

# at 3160
#211124 14:32:38 server id 572533306  end_log_pos 3225 crc32 0xea347e46         gtid    last_committed=11       sequence_number=12      rbr_only=no
set @@session.gtid_next= '671c995a-fc15-11eb-946a-525400dc7f2a:908'/*!*/;
# at 3225
#211124 14:32:38 server id 572533306  end_log_pos 3311 crc32 0xc951897a         query   thread_id=375   exec_time=0     error_code=0
set timestamp=1637735558/*!*/;
set @@session.pseudo_thread_id=375/*!*/;
drop database cs
/*!*/;
# at 3311


恢复到临时库


1.创建数据目录,拷贝数据文件

mkdir -p /data/mysql

cd /backup/full

cp -a * /data/mysql


2.编辑配置文件

cp /etc/my.cnf /etc/mytemp.cnf

vim /etc/mytemp.cnf

datadir=/data/mysql


3.赋权

chown mysql:mysql -r /data


4.启动

mysqld_safe --defaults-file=/etc/mytemp.cnf --user=mysql &


已经恢复到周二为止数据


将误操作的binlog的gitd排除掉,前面已经查询得知该误删除的gtid

# at 3160
#211124 14:32:38 server id 572533306  end_log_pos 3225 crc32 0xea347e46         gtid    last_committed=11       sequence_number=12      rbr_only=no
set @@session.gtid_next= '671c995a-fc15-11eb-946a-525400dc7f2a:908'/*!*/;
# at 3225
#211124 14:32:38 server id 572533306  end_log_pos 3311 crc32 0xc951897a         query   thread_id=375   exec_time=0     error_code=0
set timestamp=1637735558/*!*/;
set @@session.pseudo_thread_id=375/*!*/;
drop database cs
/*!*/;
# at 3311


mysqlbinlog --skip-gtids --exclude-gtids='671c995a-fc15-11eb-946a-525400dc7f2a:908' /data/3306/binlog/mysql-bin.000013 >/backup/binlog.sql

登录临时库,应用

source /backup/binlog.sql


查看已经恢复误删的库,周三的更新也恢复了。


减少误操作的建议

1、账号分离,只给业务开发同学 dml 权限,而不给 truncate/drop 权限。而如果业务开发人员有 ddl 需求的话,也可以通过开发管理系统得到支持。

即使是 dba 团队成员,日常也都规定只使用只读账号,必要的时候才使用有更新权限的账号。

2、制定操作规范。这样做的目的,是避免写错要删除的表名。

比如:在删除数据表之前,必须先对表做改名操作。然后,观察一段时间,确保对业务无影响以后再删除这张表。改表名的时候,要求给表名加固定的后缀(比如加 _to_be_deleted),然后删除表的动作必须通过管理系统执行。并且,管理系删除表的时候,只能删除固定后缀的表。


最后修改时间:2021-11-24 16:01:44
「喜欢文章,快来给作者赞赏墨值吧」
2人已赞赏
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论