mysql恢复数据的一些方法

最新推荐文章于 2024-08-19 14:36:26 发布

西北角稳

最新推荐文章于 2024-08-19 14:36:26 发布

阅读量1.3k

点赞数

分类专栏：笔记

本文链接：https://blog.csdn.net/weixin_41321728/article/details/89226306

版权

笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文介绍了三种MySQL数据恢复方法：误删除InnoDB ibdata数据文件后的恢复，update语句忘加where条件的恢复，以及delete忘加where条件的恢复。详细步骤包括检查与备份文件，设置数据库只读，利用binlog日志进行数据恢复，并提供了模拟Oracle闪回功能的操作指南。

摘要由CSDN通过智能技术生成

1.人工误删除InnoDB ibdata数据文件恢复

使用rm -f ib* 删除数据文件和重做日志文件，此时mysql数据库还是可以继续工作，数据可以写入，此时千万不能把mysqld进程杀死，否则无法挽救.

先找到mysqld的进程pid netstat -ntlp | grep mysqld
查看结果 ll /proc/进程id/fd | egrep 'ib_|ibdata' ,查看10，11，4，9为要恢复的文件
关闭业务， flush tables with read lock; 让数据库只读，以便完成恢复工作
1. 检验只读是否成功，让脏页尽快刷入到磁盘中 set global innodb_max_dirty_pages_pct=0;
2. 查看binlog日志写入情况确保file和position的值没有在变化 show master status;
3. 在查看innodb状态信息，确保脏页已经刷入磁盘 show engine innodb status\G
4. 查看状态确保一些值是正确的
  1. 查看transactions 确保后台purge进程把undo log全部清理掉，事务id 要一致
  2. 查看insert buffer and adaptive hash index 合并插入缓存等于1
  3. 查看log 确保log sequence…，log flushed…，log checkpoint… 这三个值不在变化
  4. 查看buffer pool and memory 确保脏页数据为0
  5. 查看row operations 确保插入更新删除为0
恢复数据，把之前看到的几个文件cp到原来的目录下
修改用户属性 chown mysql:mysql ib*
重启mysql测试（千万不要在生产环境测试）

2.update忘加where条件误操作恢复

全量备份+增量binlog备份（弊端数据量大时，恢复麻烦）

模拟oracle闪回功能

将binlog日志设置为binlog_format=ROW，如果是statement则无效
执行语句但忘记加where条件导致数据都被修改

恢复，创建一个新的用户分配权限为只读，将业务连接账号设置为这个

1. `grant all privleges onyouDB.* TO 'admin_read_only'@'%'identified by '密码';
2. `flush privileges;`
3. set global read_only=1;

通过binlog找到那条执行的update语句

/usr/local/mysql-master/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-master-bin.000023 | grep -B 15 '修改后的值' | more

把此binlog语句导出

/usr/local/mysql-master/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-master-bin.000024 | sed -n '/# at 194/,/COMMIT/p' >/usr/local/1.txt

查看文件 `more /opt/1.txt`

把binlog转换成sql语句

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' test.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > ./recover.sql

sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 把where和set换位置
sed -r '/WHERE/{:a;N;/@4/!ba;s/###   @2.*//g}'   把###   @2.* 后边的替换为空
sed 's/### //g;s/\/\*.*/,/g'  把 /* */ 换成 , 
sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' 把#那行去掉，把commit去掉
sed '/^$/d'     去除空行
sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql  替换为字段
sed -i -r 's/(score=.*),/\1/g' recover.sql     把score=后边的逗号去掉

把@1，@2。。。转换为相应的表字段

sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql
sed -i -r 's/(score=.*),/\1/g' recover.sql
cat recover.sql

恢复
```
source /opt/recover.sql
```

3.delete忘加where条件误操作恢复（模拟oracle闪回功能）

分析binlog将误操作delete语句保存到文本中

/usr/local/mysql-master/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-master-bin.000024 | grep -B 15 'DELETE' |more

/usr/local/mysql-master/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-master-bin.000024 | sed -n '/### DELETE FROM `school`.`student`/,/COMMIT/p'> /usr/local/delete.txt

转换为标准的sql语句

cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > insert.sql