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

tanglu 240 2021-10-25

一、恢复说明

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

企业微信截图_20211025170028.png


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


版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:【MySQL运维】使用binlog2sql工具实现数据回滚
下一篇:【Redis运维】Redis各版本特性对比
相关文章

 发表评论

暂时没有评论,来抢沙发吧~

微信二维码