【MySQL运维】truncate误删除数据后恢复办法

一、恢复说明

1、采用新增从库的方式来恢复数据，由于主库并没有停止，所以只有误操作的表相关业务会受到影响

2、执行了truncate语句后，由于表结构还存在，所以INSERT语句基本是能正常执行的，但是其它操作则会报错

3、出现误操作的业务立即停服，避免脏数据持续写入，增加恢复难度

二、恢复步骤

1、刷新日志，确保新产生的INSERT操作都在最新的binlog中

flush logs;  #假设刷新前的日志是mysql-bin.000065，那么新产生的脏数据都会在mysql-bin.000066中

2、将原表进行重命名，这样TRUNCATE后因为INSERT产生的脏数据都在该表里，后续整合数据需要用到

rename table mysqldba to mysqldba_bak

3、解析刷新日志前的最后一份binlog，目的是找到truncate语句前的position

mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000065 > /tmp/000065.sql

4、使用全量备份新建一个从库，后续会将从库的回放停在truncate语句前的position点

# 从库搭建过程略
start slave io_thread;  #只开启IO线程，观察Retrieved_Gtid_Set信息是否在正常更新

5、将从库的回放停在truncate语句前的position点。这里不能直接跳过truncate语句来继续执行后面的SQL，因为经过truncate后数据已经被清空，后续INSERT操作的主键ID都是从头开始的，再接着执行的话会出现主键冲突

start slave sql_thread until sql_before_gtids='xxxxxxxxxxxxxxxxxxx:222345'  #停在了truncate操作前
# start slave sql_thread until master_log_file='mysql-bin.000065',master_log_pos=181890214  #也可以指定position信息来进行停止

6、经过上面的操作后新的从库已经拥有了误操作前的完整数据，而误操作后产生的脏数据还在原来的库中，先将新从库的历史数据备份出来并导入，此时原库就拥有mysqldba_bak（脏数据）和mysqldba_old（原数据）两张表，剩下的就是和开发协商合并数据了

#新从库dump数据
rename table mysqldba to mysqldba_old
mysqldump --set-gtid-purged=off -uroot -p linuxe.mysqldba_old > /tmp/mysqldba_old.sql

#旧从库导入数据
mysql -uroot -p  < /tmp/mysqldba_old.sql