mysql误删数据怎么恢复（2）drop/truncate误删恢复 -m6米乐安卓版下载

原创陈家睿 2021-11-24

12260

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

（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

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