MySQL误删数据怎么恢复drop/truncate误删恢复

炒沙作縻终不饱，缕冰文章费工巧。这篇文章主要讲述MySQL误删数据怎么恢复drop/truncate误删恢复相关的知识，希望能为你提供帮助。
误删数据包含如下四种情况
（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 BEGINIGNORECASE=1 if($0~/drop/)count[$1" " $2" " $3" " $NF]++ENDfor(i in count)print i," \\t" ,count[i]|column -t|sort -k3nr

文章图片

在mysql-bin.000013中

# at 3160 #211124 14:32:38 server id 572533306end_log_pos 3225 CRC32 0xea347e46GTIDlast_committed=11sequence_number=12rbr_only=no SET @@SESSION.GTID_NEXT= 671c995a-fc15-11eb-946a-525400dc7f2a:908/*!*/; # at 3225 #211124 14:32:38 server id 572533306end_log_pos 3311 CRC32 0xc951897aQuerythread_id=375exec_time=0error_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 572533306end_log_pos 3225 CRC32 0xea347e46GTIDlast_committed=11sequence_number=12rbr_only=no SET @@SESSION.GTID_NEXT= 671c995a-fc15-11eb-946a-525400dc7f2a:908/*!*/; # at 3225 #211124 14:32:38 server id 572533306end_log_pos 3311 CRC32 0xc951897aQuerythread_id=375exec_time=0error_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、制定操作规范。这样做的目的，是避免写错要删除的表名。
【MySQL误删数据怎么恢复drop/truncate误删恢复】比如：在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。