Mysql数据库误删除数据恢复方案

Mysql数据库误删除数据恢复方案

一、查看binlog是否开启并锁表

• #进入mysql
  mysql -uroot -p
  
  #查看binlog是否开启
  show variables like '%log_bin%'; 
  #如果log_bin显示为ON则开启成功
  
  #锁表,防止数据被污染
  lock tables test read;
  

二、查询目前在哪个binlog日志

• #列出binlog列表
  show binary logs;
  
  #查询最新的binlog
  show master status;
  

三、导出删除日期范围内的binlog日志

• 在mysql的bin目录下使用mysqlbinlog，如果是docker容器则使用find命令查找到mysqlbinlog命令所在文件，进入执行即可

• mysqlbinlog -uroot -proot --base64-output=decode-rows --start-datetime="2022-01-13 08:00:00" --stop-datetime="2022-01-14 10:00:00" --read-from-remote-server -vv mysql-bin.000002 | sed -n '/### DELETE FROM `test`.`test`/, /COMMIT/p' > /var/lib/mysql/row.sql;
  
  #--base64-output=decode-rows 表示导出格式为row
  #--start-datetime="2022-01-13 08:00:00" 表示查询的开始时间
  #--stop-datetime="2022-01-14 10:00:00" 查询的结束时间
  #--read-from-remote-server 表示用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
  -vv 表示显示sql 语句加 字段类型
  
  #sed -n '/### DELETE FROM `test`.`test`/, /COMMIT/p' 表示过滤规则
  # > /var/lib/mysql/row.sql 表示输出路径
  

四、过滤刚刚导出的binlog日志

• #先进入导出binlog的路径, docker可以去到容器外挂载路径
  cd /var/lib/mysql
  
  cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@3.*),/\1;/g' | sed 's/@[1-9]=//g' > rollback.sql;
  
  
  #sed -r 's/(@3.*),/\1;/g'
  #这里的@3代码过滤出的binlog的最后一行,替换掉最后的逗号变为;
  

• 在mysql中使用unlock tables;解锁表

• 再执行sql文件即可。