【MySQL核心】误删除表？帮你时间倒流！！！

背景

开发同事在操作测试库的时候，误删除了一张业务表，她问我能不能帮她找回来，我说滚！！！经不住她的软磨硬泡，我还是软了下来。

简述：

在真实的业务场景中，有时候误删除表可能不止是将这个表找回来那么简单，还需要经过业务方确认是否要补数据等处理方式。

我这里是通过备份+截取 binlog 的方式将数据恢复到删除前一个事务。

恢复思路：

• 1、从物理备份中获取表空间 / 或者从逻辑备份中过滤出单表

• 2、将表空间 import 到数据库 / 或者导入逻辑备份的单表

• 3、通过 my2sql 过滤出待恢复表的 SQL语句

• 4、将语句恢复至数据库完成恢复

1、删除一个表

drop table t_user;

2、停止业务（防止对数据二次伤害）

主库防止写入开启只读模式

set global read_only=1;

3、从物理全备恢复

解压备份文件

tar  xf  /data/backup/full.tar.gz

将备份文件重做备份路径

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

创建一张表结构相同的表，并释放表空间

alter  table  t_user discard  tablespace;

将备份中 t_user.ibd 文件cp到生产库的相对应的库下面

cp -a  innobackupex.exp  t_user.ibd   /mydata/3306/data/test/

将文件赋权

chown -R mysql:mysql /mydata/3306/data/test/t_user.ibd

将表空间导入到生产库中

alter table t_user import tablespace;

查看数据

select * from t_user limit 10;

4、从逻辑全备恢复

如果你的是逻辑备份可以这样操作

#从全备中提取出该表的建表语句
sed -e  '/./{H;$!d;}' -e 'x;/CREATE TABLE `t_user`/!d;q' full.sql >  /tmp/t_user.sql

#提取该表的insert into语句
grep  'INSERT INTO `t_user`' full.sql >> /tmp/t_user.sql

将备份文件恢复至数据库

# 登陆数据库 执行 source
use test  # 进入库
source /tmp/t_user.sql  # 执行导入

注意：这里备份文件要手动打开看下是不是你想要的表和数据

恢复至这里已经把备份的数据恢复好了，但是备份完这段时间数据库依然在运行中，这段时间的数据都在 binlog 中，但是怎么从 binlog 中提取单个表的数据呢？

5、截取 binlog 恢复

这里选择使用 go 语言编写的 my2sql 工具进行恢复，相比于其他工具，my2sql 速度更快，功能也比较丰富。在恢复前需要先找出备份的 position 信息和 drop table 前一个position 信息用来指定恢复的位置。

项目地址：https://github.com/liuhr/my2sql

获取位置信息

获取备份结束的 position 信息

# 查看备份结束位置（--start-position位置）
cat /data/backup/full/xtrabackup_binlog_info
mysql-bin.000007    13524   # start-position位置

获取删除表前一个 position 信息

# 通过mysqlbinlog 查找删除前一个position 位置
mysqlbinlog  /mydata/3306/mysql-bin.000007 |grep  -i -B20 -C10 'drop table'

COMMIT/*!*/;
# at 43459
#240815 13:46:59 server id 330651  end_log_pos 43524 CRC32 0xd7e4f2a5   Ignorable
# Ignorable event type 33 (MySQL Gtid)
# at 43524    # 使用前一个位置点  --stop-position 位置
#240815 13:46:59 server id 330651  end_log_pos 43645 CRC32 0x0455269b   Query   thread_id=7     exec_time=0     error_code=0    xid=0
SET TIMESTAMP=1723700819/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
DROP TABLE `users` /* generated by server */

生成 sql 语句

通过my2sql 生成 users 表的 sql 语句。

安装工具

https://github.com/liuhr/my2sql

# 安装 git 和 go
yum install -y git go

git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .

# 还可以直接下载打包好的 linux 文件 
https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

获取单表数据

my2sql  \
-user root -password 123456  -port 3306 \
-host localhost -databases test_db  -tables orders \
-work-type 2sql   -start-file  mysql-bin.000007 \
-start-pos=44863 -stop-pos=54850 \
-output-dir /tmp

-databases 指定库

-tables 指定表

-work-type 转换类型 为 sql

-start-file 指定 binlog 文件

-start-pos 指定开始位置点

-stop-pos 指定结束位置点

-output-dir 输出文件

将 sql 导入到数据库

# 登陆 mysql
mysql -uroot -p

# 导入sql
source /tmp/forward.1.sql

欢迎、点赞、收藏、转发，下期可以详细写下 my2sql 这个工具。